community.mysql.mysql_user module – Adds or removes a user from a MySQL or MariaDB database
Note
This module is part of the community.mysql collection (version 3.11.0).
You might already have this collection installed if you are using the ansible package.
It is not included in ansible-core.
To check whether it is installed, run ansible-galaxy collection list.
To install it, use: ansible-galaxy collection install community.mysql.
You need further requirements to be able to use this module,
see Requirements for details.
To use it in a playbook, specify: community.mysql.mysql_user.
Synopsis
Adds or removes a user from a MySQL or MariaDB database.
Requirements
The below requirements are needed on the host that executes this module.
PyMySQL (Python 2.7 and Python 3.x)
Parameters
Parameter |
Comments |
|---|---|
Append the privileges defined by priv to the existing ones for this user instead of overwriting existing ones. Mutually exclusive with subtract_privs. Choices:
|
|
Create, update, or delete user attributes (arbitrary ‘key: value’ comments) for the user. MySQL server must support the INFORMATION_SCHEMA.USER_ATTRIBUTES table. Provided since MySQL 8.0. To delete an existing attribute, set its value to null. |
|
The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server. |
|
Whether to validate the server host name when an SSL connection is required. Corresponds to MySQL CLIs Setting this to Requires pymysql >= 0.7.11. Choices:
|
|
Check if mysql allows login as root/nopassword before trying supplied credentials. If success, passed login_user/login_password will be ignored. Choices:
|
|
The path to a client public key certificate. |
|
The path to the client private key. |
|
The default is When When This feature was introduced because MySQL 8 and above uses case sensitive fields names in privileges. Choices:
|
|
Specify a config file from which user and password are to be read. The default config file, The default config file, To prevent the default config file from being read, set config_file to be an empty string. Default: |
|
The connection timeout when connecting to the MySQL server. Default: |
|
Indicate that the ‘password’ field is a `mysql_native_password` hash. Choices:
|
|
Sets the С(mysql) system database as context for the executed statements (it will be used as a database to connect to). Useful if you use binlog / replication filters in MySQL as per default the statements can not be caught by a binlog / replication filter, they require a database to be set to work, otherwise the replication can break down. See https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#option_mysqld_binlog-ignore-db for a description on how binlog filters work (filtering on the primary). See https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_replicate-ignore-db for a description on how replication filters work (filtering on the replica). Choices:
|
|
The ‘host’ part of the MySQL username. Default: |
|
Override the host option, making ansible apply changes to all hostnames for a given user. This option cannot be used when creating users. Choices:
|
|
Host running the database. In some cases for local connections the login_unix_socket=/path/to/mysqld/socket, that is usually Default: |
|
The password used to authenticate with. |
|
Port of the MySQL server. Requires login_host be defined as other than localhost if login_port is used. Default: |
|
The path to a Unix domain socket for local connections. Use this parameter to avoid the |
|
The username used to authenticate with. |
|
Name of the user (role) to add or remove. |
|
Set the user’s password. Only for |
|
Choices:
|
|
Number of days password will expire. Requires password_expire=interval. |
|
User’s plugin to authenticate (``CREATE USER user IDENTIFIED WITH plugin``). |
|
User’s plugin auth_string (``CREATE USER user IDENTIFIED WITH plugin BY plugin_auth_string``). If plugin is ``pam`` (MariaDB) or ``auth_pam`` (MySQL) an optional plugin_auth_string can be used to choose a specific PAM service. You need to define a salt to have idempotence on password change with ``caching_sha2_password`` and ``sha256_password`` plugins. |
|
User’s plugin hash string (``CREATE USER user IDENTIFIED WITH plugin AS plugin_hash_string``). |
|
MySQL privileges string in the format: Additionally, there must be no spaces between the table and the privilege as this will yield a non-idempotent check mode. Multiple privileges can be specified by separating each one using a forward slash: The format is based on MySQL Database and table names can be quoted, MySQL-style. If column privileges are used, the Can be passed as a dictionary (see the examples). Supports GRANTs for procedures and functions (see the examples). Note: If you pass the same |
|
Limit the user for certain server resources. Provided since MySQL 5.6 / MariaDB 10.2. Available options are Used when state=present, ignored otherwise. |
|
Salt used to generate password hash from plugin_auth_string. Salt length must be 20 characters. Salt only support ``caching_sha2_password`` or ``sha256_password`` authentication plugin. |
|
Dictionary of session variables in form of Cannot be used to set global variables, use the community.mysql.mysql_variables module instead. |
|
Whether binary logging should be enabled or disabled for the connection. Choices:
|
|
Whether the user should exist. When Choices:
|
|
Revoke the privileges defined by the priv option and keep other existing privileges. If set, invalid privileges in priv are ignored. Mutually exclusive with append_privs. Choices:
|
|
Set requirement for secure transport as a dictionary of requirements (see the examples). Valid requirements are SSL, X509, SUBJECT, ISSUER, CIPHER. SUBJECT, ISSUER and CIPHER are complementary, and mutually exclusive with SSL and X509. https://mariadb.com/kb/en/securing-connections-for-client-and-server/#requiring-tls. |
|
Choices:
|
Attributes
Attribute |
Support |
Description |
|---|---|---|
Support: full |
Can run in check_mode and return changed status prediction without modifying target. |
Notes
Note
Compatible with MySQL or MariaDB.
MySQL server installs with default login_user of
rootand no password. To secure this user as part of an idempotent playbook, you must create at least two tasks: 1) change the root user’s password, without providing any login_user/login_password details, 2) drop a~/.my.cnffile containing the new root credentials. Subsequent runs of the playbook will then succeed by reading the new credentials from the file.Currently, there is only support for the
mysql_native_passwordencrypted password hash module.Requires the PyMySQL (Python 2.7 and Python 3.X) package installed on the remote host. The Python package may be installed with apt-get install python-pymysql (Ubuntu; see ansible.builtin.apt) or yum install python2-PyMySQL (RHEL/CentOS/Fedora; see ansible.builtin.yum). You can also use dnf install python2-PyMySQL for newer versions of Fedora; see ansible.builtin.dnf.
Be sure you have PyMySQL library installed on the target machine for the Python interpreter Ansible discovers. For example if ansible discovers and uses Python 3, you need to install the Python 3 version of PyMySQL. If ansible discovers and uses Python 2, you need to install the Python 2 version of PyMySQL.
If you have trouble, it may help to force Ansible to use the Python interpreter you need by specifying
ansible_python_interpreter. For more information, see https://docs.ansible.com/ansible/latest/reference_appendices/interpreter_discovery.html.Both
login_passwordandlogin_userare required when you are passing credentials. If none are present, the module will attempt to read the credentials from~/.my.cnf, and finally fall back to using the MySQL default login of ‘root’ with no password.If there are problems with local connections, using login_unix_socket=/path/to/mysqld/socket instead of login_host=localhost might help. As an example, the default MariaDB installation of version 10.4 and later uses the unix_socket authentication plugin by default that without using login_unix_socket=/var/run/mysqld/mysqld.sock (the default path) causes the error ``Host ‘127.0.0.1’ is not allowed to connect to this MariaDB server``.
If credentials from the config file (for example,
/root/.my.cnf) are not needed to connect to a database server, but the file exists and does not contain a[client]section, before any other valid directives, it will be read and this will cause the connection to fail, to prevent this set it to an empty string, (for exampleconfig_file: '').To avoid the
Please explicitly state intended protocolerror, use the login_unix_socket argument, for example,login_unix_socket: /run/mysqld/mysqld.sock.Alternatively, to avoid using login_unix_socket argument on each invocation you can specify the socket path using the `socket` option in your MySQL config file (usually
~/.my.cnf) on the destination host, for examplesocket=/var/lib/mysql/mysql.sock.
See Also
See also
- community.mysql.mysql_info
Gather information about MySQL or MariaDB servers.
- MySQL access control and account management reference
Complete reference of the MySQL access control and account management documentation.
- MySQL provided privileges reference
Complete reference of the MySQL provided privileges documentation.
Examples
# If you encounter the "Please explicitly state intended protocol" error,
# use the login_unix_socket argument
- name: Removes anonymous user account for localhost
community.mysql.mysql_user:
name: ''
host: localhost
state: absent
login_unix_socket: /run/mysqld/mysqld.sock
- name: Removes all anonymous user accounts
community.mysql.mysql_user:
name: ''
host_all: true
state: absent
- name: Create database user with name 'bob' and password '12345' with all database privileges
community.mysql.mysql_user:
name: bob
password: 12345
priv: '*.*:ALL'
state: present
- name: Create database user using hashed password with all database privileges
community.mysql.mysql_user:
name: bob
password: '*EE0D72C1085C46C5278932678FBE2C6A782821B4'
encrypted: true
priv: '*.*:ALL'
state: present
# Set session var wsrep_on=off before creating the user
- name: Create database user with password and all database privileges and 'WITH GRANT OPTION'
community.mysql.mysql_user:
name: bob
password: 12345
priv: '*.*:ALL,GRANT'
state: present
session_vars:
wsrep_on: off
- name: Create user with password, all database privileges and 'WITH GRANT OPTION' in db1 and db2
community.mysql.mysql_user:
state: present
name: bob
password: 12345dd
priv:
'db1.*': 'ALL,GRANT'
'db2.*': 'ALL,GRANT'
# Use 'PROCEDURE' instead of 'FUNCTION' to apply GRANTs for a MySQL procedure instead.
- name: Grant a user the right to execute a function
community.mysql.mysql_user:
name: readonly
password: 12345
priv:
FUNCTION my_db.my_function: EXECUTE
state: present
- name: Modify user attributes, creating the attribute 'foo' and removing the attribute 'bar'
community.mysql.mysql_user:
name: bob
attributes:
foo: "foo"
bar: null
- name: Modify user to require TLS connection with a valid client certificate
community.mysql.mysql_user:
name: bob
tls_requires:
x509:
state: present
- name: Modify user to require TLS connection with a specific client certificate and cipher
community.mysql.mysql_user:
name: bob
tls_requires:
subject: '/CN=alice/O=MyDom, Inc./C=US/ST=Oregon/L=Portland'
cipher: 'ECDHE-ECDSA-AES256-SHA384'
- name: Modify user to no longer require SSL
community.mysql.mysql_user:
name: bob
tls_requires:
- name: Ensure no user named 'sally'@'localhost' exists, also passing in the auth credentials
community.mysql.mysql_user:
login_user: root
login_password: 123456
name: sally
state: absent
# check_implicit_admin example
- name: >
Ensure no user named 'sally'@'localhost' exists, also passing in the auth credentials.
If mysql allows root/nopassword login, try it without the credentials first.
If it's not allowed, pass the credentials
community.mysql.mysql_user:
check_implicit_admin: true
login_user: root
login_password: 123456
name: sally
state: absent
- name: Ensure no user named 'sally' exists at all
community.mysql.mysql_user:
name: sally
host_all: true
state: absent
- name: Specify grants composed of more than one word
community.mysql.mysql_user:
name: replication
password: 12345
priv: "*.*:REPLICATION CLIENT"
state: present
- name: Revoke all privileges for user 'bob' and password '12345'
community.mysql.mysql_user:
name: bob
password: 12345
priv: "*.*:USAGE"
state: present
# Example privileges string format
# mydb.*:INSERT,UPDATE/anotherdb.*:SELECT/yetanotherdb.*:ALL
- name: Example using login_unix_socket to connect to server
community.mysql.mysql_user:
name: root
password: abc123
login_unix_socket: /var/run/mysqld/mysqld.sock
- name: Example of skipping binary logging while adding user 'bob'
community.mysql.mysql_user:
name: bob
password: 12345
priv: "*.*:USAGE"
state: present
sql_log_bin: false
- name: Create user 'bob' authenticated with plugin 'AWSAuthenticationPlugin'
community.mysql.mysql_user:
name: bob
plugin: AWSAuthenticationPlugin
plugin_hash_string: RDS
priv: '*.*:ALL'
state: present
- name: Create user 'bob' authenticated with plugin 'caching_sha2_password' and static salt
community.mysql.mysql_user:
name: bob
plugin: caching_sha2_password
plugin_auth_string: password
salt: 1234567890abcdefghij
- name: Limit bob's resources to 10 queries per hour and 5 connections per hour
community.mysql.mysql_user:
name: bob
resource_limits:
MAX_QUERIES_PER_HOUR: 10
MAX_CONNECTIONS_PER_HOUR: 5
- name: Ensure bob does not have the DELETE privilege
community.mysql.mysql_user:
name: bob
subtract_privs: true
priv:
'db1.*': DELETE
# Example .my.cnf file for setting the root password
# [client]
# user=root
# password=n<_665{vS43y