community.mysql.mysql_user – Adds or removes a user from a MySQL database¶
Note
This plugin is part of the community.mysql collection (version 1.2.0).
To install it use: ansible-galaxy collection install community.mysql.
To use it in a playbook, specify: community.mysql.mysql_user.
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¶
Notes¶
Note
MySQL server installs with default login_user of
rootand no password. To secure this user as part of an idempotent playbook, you must create at least two tasks: 1) change the root user’s password, without providing any login_user/login_password details, 2) drop a~/.my.cnffile 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_passwordencrypted password hash module.Supports (check_mode).
Requires the PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.X) package installed on the remote host. The Python package may be installed with apt-get install python-pymysql (Ubuntu; see ansible.builtin.apt) or yum install python2-PyMySQL (RHEL/CentOS/Fedora; see ansible.builtin.yum). You can also use dnf install python2-PyMySQL for newer versions of Fedora; see ansible.builtin.dnf.
Be sure you have PyMySQL or MySQLdb library installed on the target machine for the Python interpreter Ansible uses, for example, if it is Python 3, you must install the library for Python 3. You can also change the interpreter. For more information, see https://docs.ansible.com/ansible/latest/reference_appendices/interpreter_discovery.html.
Both
login_passwordandlogin_userare 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.If there are problems with local connections, using login_unix_socket=/path/to/mysqld/socket instead of login_host=localhost might help. As an example, the default MariaDB installation of version 10.4 and later uses the unix_socket authentication plugin by default that without using login_unix_socket=/var/run/mysqld/mysqld.sock (the default path) causes the error
Host '127.0.0.1' is not allowed to connect to this MariaDB server.Alternatively, you can use the mysqlclient library instead of MySQL-python (MySQLdb) which supports both Python 2.X and Python >=3.5. See https://pypi.org/project/mysqlclient/ how to install it.
See Also¶
See also
- community.mysql.mysql_info
The official documentation on the community.mysql.mysql_info module.
- MySQL access control and account management reference
Complete reference of the MySQL access control and account management documentation.
- MySQL provided privileges reference
Complete reference of the MySQL provided privileges documentation.
Examples¶
- name: Removes anonymous user account for localhost
community.mysql.mysql_user:
name: ''
host: localhost
state: absent
- name: Removes all anonymous user accounts
community.mysql.mysql_user:
name: ''
host_all: yes
state: absent
- name: Create database user with name 'bob' and password '12345' with all database privileges
community.mysql.mysql_user:
name: bob
password: 12345
priv: '*.*:ALL'
state: present
- name: Create database user using hashed password with all database privileges
community.mysql.mysql_user:
name: bob
password: '*EE0D72C1085C46C5278932678FBE2C6A782821B4'
encrypted: yes
priv: '*.*:ALL'
state: present
- name: Create database user with password and all database privileges and 'WITH GRANT OPTION'
community.mysql.mysql_user:
name: bob
password: 12345
priv: '*.*:ALL,GRANT'
state: present
- name: Create user with password, all database privileges and 'WITH GRANT OPTION' in db1 and db2
community.mysql.mysql_user:
state: present
name: bob
password: 12345dd
priv:
'db1.*': 'ALL,GRANT'
'db2.*': 'ALL,GRANT'
# Note that REQUIRESSL is a special privilege that should only apply to *.* by itself.
# Setting this privilege in this manner is supported for backwards compatibility only.
# Use 'tls_requires' instead.
- name: Modify user to require SSL connections
community.mysql.mysql_user:
name: bob
append_privs: yes
priv: '*.*:REQUIRESSL'
state: present
- name: Modify user to require TLS connection with a valid client certificate
community.mysql.mysql_user:
name: bob
tls_requires:
x509:
state: present
- name: Modify user to require TLS connection with a specific client certificate and cipher
community.mysql.mysql_user:
name: bob
tls_requires:
subject: '/CN=alice/O=MyDom, Inc./C=US/ST=Oregon/L=Portland'
cipher: 'ECDHE-ECDSA-AES256-SHA384'
- name: Modify user to no longer require SSL
community.mysql.mysql_user:
name: bob
tls_requires:
- name: Ensure no user named 'sally'@'localhost' exists, also passing in the auth credentials
community.mysql.mysql_user:
login_user: root
login_password: 123456
name: sally
state: absent
# check_implicit_admin example
- name: >
Ensure no user named 'sally'@'localhost' exists, also passing in the auth credentials.
If mysql allows root/nopassword login, try it without the credentials first.
If it's not allowed, pass the credentials
community.mysql.mysql_user:
check_implicit_admin: yes
login_user: root
login_password: 123456
name: sally
state: absent
- name: Ensure no user named 'sally' exists at all
community.mysql.mysql_user:
name: sally
host_all: yes
state: absent
- name: Specify grants composed of more than one word
community.mysql.mysql_user:
name: replication
password: 12345
priv: "*.*:REPLICATION CLIENT"
state: present
- name: Revoke all privileges for user 'bob' and password '12345'
community.mysql.mysql_user:
name: bob
password: 12345
priv: "*.*:USAGE"
state: present
# Example privileges string format
# mydb.*:INSERT,UPDATE/anotherdb.*:SELECT/yetanotherdb.*:ALL
- name: Example using login_unix_socket to connect to server
community.mysql.mysql_user:
name: root
password: abc123
login_unix_socket: /var/run/mysqld/mysqld.sock
- name: Example of skipping binary logging while adding user 'bob'
community.mysql.mysql_user:
name: bob
password: 12345
priv: "*.*:USAGE"
state: present
sql_log_bin: no
- name: Create user 'bob' authenticated with plugin 'AWSAuthenticationPlugin'
community.mysql.mysql_user:
name: bob
plugin: AWSAuthenticationPlugin
plugin_hash_string: RDS
priv: '*.*:ALL'
state: present
- name: Limit bob's resources to 10 queries per hour and 5 connections per hour
community.mysql.mysql_user:
name: bob
resource_limits:
MAX_QUERIES_PER_HOUR: 10
MAX_CONNECTIONS_PER_HOUR: 5
# Example .my.cnf file for setting the root password
# [client]
# user=root
# password=n<_665{vS43y
Authors¶
Jonathan Mainguy (@Jmainguy)
Benjamin Malynovytch (@bmalynovytch)
Lukasz Tomaszkiewicz (@tomaszkiewicz)