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 |
| 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 |
| 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 |
| 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 |
| 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 .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 (added in 2.1)
| no | yes |
| 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 |
| Whether the user should exist. When absent , removes the user. |
update_password (added in 2.0)
| no | always |
| 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.
- Currently, there is only support for the
mysql_native_password
encrypted password hash module. - 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.)
- 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.
Status
This module is flagged as preview which means that it is not guaranteed to have a backwards compatible interface.
For help in developing on modules, should you be so inclined, please read Community Information & Contributing, Testing Ansible and Developing Modules.
© 2012–2018 Michael DeHaan
© 2018–2019 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/ansible/2.4/mysql_user_module.html