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

From Get docs
Ansible/docs/2.7/modules/mysql user module


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

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

added in 1.4

  • no

  • yes

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

check_implicit_admin

boolean

added in 1.3

  • no

  • yes

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

config_file

-

added in 2.0

Default:

"~/.my.cnf"

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

connect_timeout

-

added in 2.1

Default:

30

The connection timeout when connecting to the MySQL server.

encrypted

boolean

added in 2.0

  • no

  • yes

Indicate that the 'password' field is a `mysql_native_password` hash

host

-

Default:

"localhost"

the 'host' part of the MySQL username

host_all

boolean

added in 2.1

  • 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

-

Default:

"localhost"

Host running the database.

login_password

-

The password used to authenticate with.

login_port

-

Default:

3306

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

login_unix_socket

-

The path to a Unix domain socket for local connections.

login_user

-

The username used to authenticate with.

name

- / required

name of the user (role) to add or remove

password

-

set the user's password.

priv

-

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))).

sql_log_bin

boolean

added in 2.1

  • no
  • yes

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

ssl_ca

-

added in 2.0

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

ssl_cert

-

added in 2.0

The path to a client public key certificate.

ssl_key

-

added in 2.0

The path to the client private key.

state

-

  • present

  • absent

Whether the user should exist. When absent, removes the user.

update_password

-

added in 2.0

  • 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) Python package on the remote host. For Ubuntu, this is as easy as apt-get install python-pymysql. (See apt.) For CentOS/Fedora, this is as easy as yum install python2-PyMySQL. (See yum.)
  • 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.


Examples

# Removes anonymous user account for localhost
- mysql_user:
    name: ''
    host: localhost
    state: absent

# Removes all anonymous user accounts
- mysql_user:
    name: ''
    host_all: yes
    state: absent

# Create database user with name 'bob' and password '12345' with all database privileges
- mysql_user:
    name: bob
    password: 12345
    priv: '*.*:ALL'
    state: present

# Create database user with name 'bob' and previously hashed mysql native password '*EE0D72C1085C46C5278932678FBE2C6A782821B4' with all database privileges
- mysql_user:
    name: bob
    password: '*EE0D72C1085C46C5278932678FBE2C6A782821B4'
    encrypted: yes
    priv: '*.*:ALL'
    state: present

# Creates database user 'bob' and password '12345' with all database privileges and 'WITH GRANT OPTION'
- mysql_user:
    name: bob
    password: 12345
    priv: '*.*:ALL,GRANT'
    state: present

# Modify user Bob to require SSL connections. Note that REQUIRESSL is a special privilege that should only apply to *.* by itself.
- mysql_user:
    name: bob
    append_privs: true
    priv: '*.*:REQUIRESSL'
    state: present

# Ensure no user named 'sally'@'localhost' exists, also passing in the auth credentials.
- mysql_user:
    login_user: root
    login_password: 123456
    name: sally
    state: absent

# Ensure no user named 'sally' exists at all
- mysql_user:
    name: sally
    host_all: yes
    state: absent

# Specify grants composed of more than one word
- mysql_user:
    name: replication
    password: 12345
    priv: "*.*:REPLICATION CLIENT"
    state: present

# Revoke all privileges for user 'bob' and password '12345'
- mysql_user:
    name: bob
    password: 12345
    priv: "*.*:USAGE"
    state: present

# Example privileges string format
# mydb.*:INSERT,UPDATE/anotherdb.*:SELECT/yetanotherdb.*:ALL

# Example using login_unix_socket to connect to server
- mysql_user:
    name: root
    password: abc123
    login_unix_socket: /var/run/mysqld/mysqld.sock

# Example of skipping binary logging while adding user 'bob'
- mysql_user:
    name: bob
    password: 12345
    priv: "*.*:USAGE"
    state: present
    sql_log_bin: no

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

Status

Authors

  • Jonathan Mainguy (@Jmainguy)

Hint

If you notice any issues in this documentation you can edit this document to improve it.


© 2012–2018 Michael DeHaan
© 2018–2019 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/ansible/2.7/modules/mysql_user_module.html