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

Note

This module is part of the community.mysql collection (version 2.3.8).

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.

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.

  • mysqlclient (Python 3.5+) or

  • PyMySQL (Python 2.7 and Python 3.x) or

  • MySQLdb (Python 2.x)

Parameters

Parameter

Comments

append_privs

boolean

Append the privileges defined by priv to the existing ones for this user instead of overwriting existing ones.

Choices:

  • no ← (default)

  • yes

ca_cert

aliases: ssl_ca

path

The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server.

check_hostname

boolean

added in 1.1.0 of community.mysql

Whether to validate the server host name when an SSL connection is required. Corresponds to MySQL CLIs --ssl switch.

Setting this to false disables hostname verification. Use with caution.

Requires pymysql >= 0.7.11.

This option has no effect on MySQLdb.

Choices:

  • no

  • yes

check_implicit_admin

boolean

Check if mysql allows login as root/nopassword before trying supplied credentials.

If success, passed login_user/login_password will be ignored.

Choices:

  • no ← (default)

  • yes

client_cert

aliases: ssl_cert

path

The path to a client public key certificate.

client_key

aliases: ssl_key

path

The path to the client private key.

config_file

path

Specify a config file from which user and password are to be read.

Default: “~/.my.cnf”

connect_timeout

integer

The connection timeout when connecting to the MySQL server.

Default: 30

encrypted

boolean

Indicate that the ‘password’ field is a mysql_native_password hash.

Choices:

  • no ← (default)

  • yes

host

string

The ‘host’ part of the MySQL username.

Default: “localhost”

host_all

boolean

Override the host option, making ansible apply changes to all hostnames for a given user.

This option cannot be used when creating users.

Choices:

  • no ← (default)

  • yes

login_host

string

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.

Default: “localhost”

login_password

string

The password used to authenticate with.

login_port

integer

Port of the MySQL server. Requires login_host be defined as other than localhost if login_port is used.

Default: 3306

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. Only for mysql_native_password authentication. For other authentication plugins see the combination of plugin, plugin_hash_string, plugin_auth_string.

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.table1:priv/db.table2: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).

Supports GRANTs for procedures and functions (see the examples).

Note: If you pass the same db.table combination to this parameter two or more times with different privileges, for example, '*.*:SELECT/*.*:SHOW VIEW', only the last one will be applied, in this example, it will be SHOW VIEW respectively. Use '*.*:SELECT,SHOW VIEW' instead to apply both.

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

Whether binary logging should be enabled or disabled for the connection.

Choices:

  • no

  • yes ← (default)

state

string

Whether the user should exist.

When absent, removes the user.

Choices:

  • absent

  • present ← (default)

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.

https://mariadb.com/kb/en/securing-connections-for-client-and-server/#requiring-tls.

update_password

string

always will update passwords if they differ. This affects password and the combination of plugin, plugin_hash_string, plugin_auth_string.

on_create will only set the password or the combination of plugin, plugin_hash_string, plugin_auth_string for newly created users.

Choices:

  • always ← (default)

  • on_create

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: 1) change the root user’s password, without providing any login_user/login_password details, 2) 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.

  • Supports (check_mode).

  • 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 mysqlclient, PyMySQL, or MySQLdb 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 or mysqlclient. If ansible discovers and uses Python 2, you need to install the Python 2 version of either PyMySQL or MySQL-python.

  • 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_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.

  • Alternatively, you can use the mysqlclient library instead of MySQL-python (MySQLdb) which supports both Python 2.X and Python >=3.5. See https://pypi.org/project/mysqlclient/ how to install it.

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'

# 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

# Note that REQUIRESSL is a special privilege that should only apply to *.* by itself.
# Setting this privilege in this manner is deprecated.
# 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)