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
    Choices:
  • 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
    Choices:
  • 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
    Choices:
  • 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
    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
-
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
    Choices:
  • 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
-
    Choices:
  • present ←
  • absent
Whether the user should exist. When absent, removes the user.
update_password
-
added in 2.0
    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) 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.