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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
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
-
|
|
Whether the user should exist. When
absent , removes the user.
|
update_password
-
added in 2.0
|
|
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
andlogin_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
- This module is not guaranteed to have a backwards compatible interface. [preview]
- This module is maintained by the Ansible Community. [community]
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