community.mysql.mysql_user – Adds or removes a user from a MySQL database

Note

This plugin is part of the community.mysql collection.

To install it use: ansible-galaxy collection install community.mysql.

To use it in a playbook, specify: community.mysql.mysql_user.

Synopsis

  • Adds or removes a user from a MySQL database.

Requirements

The below requirements are needed on the host that executes this module.

  • PyMySQL (Python 2.7 and Python 3.X), or

  • MySQLdb (Python 2.x)

Parameters

Parameter Choices/Defaults Comments
append_privs
boolean
    Choices:
  • no ←
  • yes
Append the privileges defined by priv to the existing ones for this user instead of overwriting existing ones.
ca_cert
path
The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server.

aliases: ssl_ca
check_hostname
boolean
added in 1.1.0 of community.mysql
    Choices:
  • no
  • yes
Whether to validate the server host name when an SSL connection is required.
Setting this to false disables hostname verification. Use with caution.
Requires pymysql >= 0.7.11.
This optoin has no effect on MySQLdb.
check_implicit_admin
boolean
    Choices:
  • no ←
  • yes
Check if mysql allows login as root/nopassword before trying supplied credentials.
If success, passed login_user/login_password will be ignored.
client_cert
path
The path to a client public key certificate.

aliases: ssl_cert
client_key
path
The path to the client private key.

aliases: ssl_key
config_file
path
Default:
"~/.my.cnf"
Specify a config file from which user and password are to be read.
connect_timeout
integer
Default:
30
The connection timeout when connecting to the MySQL server.
encrypted
boolean
    Choices:
  • no ←
  • yes
Indicate that the 'password' field is a `mysql_native_password` hash.
host
string
Default:
"localhost"
The 'host' part of the MySQL username.
host_all
boolean
    Choices:
  • no ←
  • yes
Override the host option, making ansible apply changes to all hostnames for a given user.
This option cannot be used when creating users.
login_host
string
Default:
"localhost"
Host running the database.
In some cases for local connections the login_unix_socket=/path/to/mysqld/socket, that is usually /var/run/mysqld/mysqld.sock, needs to be used instead of login_host=localhost.
login_password
string
The password used to authenticate with.
login_port
integer
Default:
3306
Port of the MySQL server. Requires login_host be defined as other than localhost if login_port is used.
login_unix_socket
string
The path to a Unix domain socket for local connections.
login_user
string
The username used to authenticate with.
name
string / required
Name of the user (role) to add or remove.
password
string
Set the user's password..
plugin
string
added in 0.1.0 of community.mysql
User's plugin to authenticate (``CREATE USER user IDENTIFIED WITH plugin``).
plugin_auth_string
string
added in 0.1.0 of community.mysql
User's plugin auth_string (``CREATE USER user IDENTIFIED WITH plugin BY plugin_auth_string``).
plugin_hash_string
string
added in 0.1.0 of community.mysql
User's plugin hash string (``CREATE USER user IDENTIFIED WITH plugin AS plugin_hash_string``).
priv
raw
MySQL privileges string in the format: db.table:priv1,priv2.
Multiple privileges can be specified by separating each one using a forward slash: db.table:priv/db.table:priv.
The format is based on MySQL GRANT statement.
Database and table names can be quoted, MySQL-style.
If column privileges are used, the priv1,priv2 part must be exactly as returned by a SHOW GRANT statement. If not followed, the module will always report changes. It includes grouping columns by permission (SELECT(col1,col2) instead of SELECT(col1,SELECT(col2))).
Can be passed as a dictionary (see the examples).
resource_limits
dictionary
added in 0.1.0 of community.mysql
Limit the user for certain server resources. Provided since MySQL 5.6 / MariaDB 10.2.
Available options are MAX_QUERIES_PER_HOUR: num, MAX_UPDATES_PER_HOUR: num, MAX_CONNECTIONS_PER_HOUR: num, MAX_USER_CONNECTIONS: num.
Used when state=present, ignored otherwise.
sql_log_bin
boolean
    Choices:
  • no
  • yes ←
Whether binary logging should be enabled or disabled for the connection.
state
string
    Choices:
  • absent
  • present ←
Whether the user should exist.
When absent, removes the user.
tls_requires
dictionary
added in 1.0.0 of community.mysql
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.
update_password
string
    Choices:
  • always ←
  • on_create
always will update passwords if they differ.
on_create will only set the password for newly created users.

Notes

Note

  • MySQL server installs with default login_user of ‘root’ and no password. To secure this user as part of an idempotent playbook, you must create at least two tasks: the first must change the root user’s password, without providing any login_user/login_password details. The second must drop a ~/.my.cnf file 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_password encrypted password hash module.

  • Requires the PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.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 or MySQLdb library installed on the target machine for the Python interpreter Ansible uses, for example, if it is Python 3, you must install the library for Python 3. You can also change the interpreter. For more information, see https://docs.ansible.com/ansible/latest/reference_appendices/interpreter_discovery.html.

  • Both login_password and login_user are 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.

See Also

See also

community.mysql.mysql_info

The official documentation on the community.mysql.mysql_info module.

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

- name: Removes anonymous user account for localhost
  community.mysql.mysql_user:
    name: ''
    host: localhost
    state: absent

- name: Removes all anonymous user accounts
  community.mysql.mysql_user:
    name: ''
    host_all: yes
    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: yes
    priv: '*.*:ALL'
    state: present

- 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

- 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'

# Note that REQUIRESSL is a special privilege that should only apply to *.* by itself.
# Setting this privilege in this manner is supported for backwards compatibility only. Use 'tls_requires' instead.
- name: Modify user to require SSL connections.
  community.mysql.mysql_user:
    name: bob
    append_privs: yes
    priv: '*.*:REQUIRESSL'
    state: present

- 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: yes
    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: yes
    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: no

- 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: 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

# Example .my.cnf file for setting the root password
# [client]
# user=root
# password=n<_665{vS43y

Authors

  • Jonathan Mainguy (@Jmainguy)

  • Benjamin Malynovytch (@bmalynovytch)

  • Lukasz Tomaszkiewicz (@tomaszkiewicz)