W3cubDocs

/Ansible

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

Synopsis

Adds or removes a user from a MySQL database.

Requirements (on host that executes module)

  • MySQLdb

Options

parameter required default choices comments
append_privs
(added in 1.4)
 no no
  • yes
  • no
Append the privileges defined by priv to the existing ones for this user instead of overwriting existing ones.
check_implicit_admin
(added in 1.3)
 no no
  • yes
  • no
Check if mysql allows login as root/nopassword before trying supplied credentials.
config_file
(added in 2.0)
 no ~/.my.cnf
Specify a config file from which user and password are to be read.
connect_timeout
(added in 2.1)
 no 30
The connection timeout when connecting to the MySQL server.
encrypted
(added in 2.0)
 no no
  • yes
  • no
Indicate that the 'password' field is a `mysql_native_password` hash
host
 no localhost
the 'host' part of the MySQL username
host_all
(added in 2.1)
 no no
  • yes
  • no
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
 no localhost
Host running the database.
login_password
 no
The password used to authenticate with.
login_port
 no 3306
Port of the MySQL server. Requires login_host be defined as other then localhost if login_port is used.
login_unix_socket
 no
The path to a Unix domain socket for local connections.
login_user
 no
The username used to authenticate with.
name
 yes
name of the user (role) to add or remove
password
 no
set the user's password.
priv
 no
MySQL privileges string in the format: db.table:priv1,priv2
sql_log_bin
(added in 2.1)
 no yes
  • yes
  • no
Whether binary logging should be enabled or disabled for the connection.
ssl_ca
(added in 2.0)
 no
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)
 no
The path to a client public key certificate.
ssl_key
(added in 2.0)
 no
The path to the client private key.
state
 no present
  • present
  • absent
Whether the user should exist. When absent, removes the user.
update_password
(added in 2.0)
 no always
  • always
  • on_create
always will update passwords if they differ. on_create will only set the password for newly created users.

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

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.

Note

Currently, there is only support for the mysql_native_password encrypted password hash module.

Note

Requires the MySQLdb Python package on the remote host. For Ubuntu, this is as easy as apt-get install python-mysqldb. (See apt.) For CentOS/Fedora, this is as easy as yum install MySQL-python. (See yum.)

Note

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.

This is a Core Module

For more information on what this means please read Core Modules

For help in developing on modules, should you be so inclined, please read Community Information & Contributing, developing_test_pr and Developing Modules.

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