Try SpotterAdds or removes a user from a MySQL database
Authors: Jonathan Mainguy (@Jmainguy), Benjamin Malynovytch (@bmalynovytch), Lukasz Tomaszkiewicz (@tomaszkiewicz), kmarse (@kmarse), Laurent Indermühle (@laurent-indermuehle)
Install with ansible-galaxy collection install community.mysql:==3.9.0
collections:
- name: community.mysql
version: 3.9.0Adds or removes a user from a MySQL database.
# If you encounter the "Please explicitly state intended protocol" error,
# use the login_unix_socket argument
- name: Removes anonymous user account for localhost
community.mysql.mysql_user:
name: ''
host: localhost
state: absent
login_unix_socket: /run/mysqld/mysqld.sock
- name: Removes all anonymous user accounts
community.mysql.mysql_user:
name: ''
host_all: true
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: true
priv: '*.*:ALL'
state: present
# Set session var wsrep_on=off before creating the user
- 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
session_vars:
wsrep_on: off
- 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'
# Use 'PROCEDURE' instead of 'FUNCTION' to apply GRANTs for a MySQL procedure instead.
- name: Grant a user the right to execute a function
community.mysql.mysql_user:
name: readonly
password: 12345
priv:
FUNCTION my_db.my_function: EXECUTE
state: present
- name: Modify user attributes, creating the attribute 'foo' and removing the attribute 'bar'
community.mysql.mysql_user:
name: bob
attributes:
foo: "foo"
bar: null
- 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: true
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: true
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: false
- 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
- name: Ensure bob does not have the DELETE privilege
community.mysql.mysql_user:
name: bob
subtract_privs: true
priv:
'db1.*': DELETE
host:
default: localhost
description:
- The 'host' part of the MySQL username.
type: str
name:
description:
- Name of the user (role) to add or remove.
required: true
type: str
priv:
description:
- 'MySQL privileges string in the format: C(db.table:priv1,priv2).'
- 'Multiple privileges can be specified by separating each one using a forward slash:
C(db.table1:priv/db.table2:priv).'
- The format is based on MySQL C(GRANT) statement.
- Database and table names can be quoted, MySQL-style.
- If column privileges are used, the C(priv1,priv2) part must be exactly as returned
by a C(SHOW GRANT) statement. If not followed, the module will always report changes.
It includes grouping columns by permission (C(SELECT(col1,col2)) instead of C(SELECT(col1),SELECT(col2))).
- Can be passed as a dictionary (see the examples).
- Supports GRANTs for procedures and functions (see the examples).
- 'Note: If you pass the same C(db.table) combination to this parameter two or more
times with different privileges, for example, C(''*.*:SELECT/*.*:SHOW VIEW''), only
the last one will be applied, in this example, it will be C(SHOW VIEW) respectively.
Use C(''*.*:SELECT,SHOW VIEW'') instead to apply both.'
type: raw
state:
choices:
- absent
- present
default: present
description:
- Whether the user should exist.
- When C(absent), removes the user.
type: str
plugin:
description:
- User's plugin to authenticate (``CREATE USER user IDENTIFIED WITH plugin``).
type: str
version_added: 0.1.0
version_added_collection: community.mysql
ca_cert:
aliases:
- ssl_ca
description:
- The path to a Certificate Authority (CA) certificate. This option, if used, must
specify the same certificate as used by the server.
type: path
host_all:
default: false
description:
- Override the host option, making ansible apply changes to all hostnames for a given
user.
- This option cannot be used when creating users.
type: bool
password:
description:
- Set the user's password. Only for C(mysql_native_password) authentication. For other
authentication plugins see the combination of I(plugin), I(plugin_hash_string),
I(plugin_auth_string).
type: str
encrypted:
default: false
description:
- Indicate that the 'password' field is a `mysql_native_password` hash.
type: bool
attributes:
description:
- 'Create, update, or delete user attributes (arbitrary ''key: value'' comments) for
the user.'
- MySQL server must support the INFORMATION_SCHEMA.USER_ATTRIBUTES table. Provided
since MySQL 8.0.
- To delete an existing attribute, set its value to null.
type: dict
version_added: 3.9.0
version_added_collection: community.mysql
client_key:
aliases:
- ssl_key
description:
- The path to the client private key.
type: path
login_host:
default: localhost
description:
- Host running the database.
- In some cases for local connections the I(login_unix_socket=/path/to/mysqld/socket),
that is usually C(/var/run/mysqld/mysqld.sock), needs to be used instead of I(login_host=localhost).
type: str
login_port:
default: 3306
description:
- Port of the MySQL server. Requires I(login_host) be defined as other than localhost
if login_port is used.
type: int
login_user:
description:
- The username used to authenticate with.
type: str
client_cert:
aliases:
- ssl_cert
description:
- The path to a client public key certificate.
type: path
config_file:
default: ~/.my.cnf
description:
- Specify a config file from which user and password are to be read.
- The default config file, C(~/.my.cnf), if it exists, will be read, even if I(config_file)
is not specified.
- The default config file, C(~/.my.cnf), if it exists, must contain a C([client])
section as a MySQL connector requirement.
- To prevent the default config file from being read, set I(config_file) to be an
empty string.
type: path
sql_log_bin:
default: true
description:
- Whether binary logging should be enabled or disabled for the connection.
type: bool
append_privs:
default: false
description:
- Append the privileges defined by priv to the existing ones for this user instead
of overwriting existing ones. Mutually exclusive with I(subtract_privs).
type: bool
session_vars:
description:
- 'Dictionary of session variables in form of C(variable: value) to set at the beginning
of module execution.'
- Cannot be used to set global variables, use the M(community.mysql.mysql_variables)
module instead.
type: dict
version_added: 3.6.0
version_added_collection: community.mysql
tls_requires:
description:
- Set requirement for secure transport as a dictionary of requirements (see the examples).
- Valid requirements are SSL, X509, SUBJECT, ISSUER, CIPHER.
- SUBJECT, ISSUER and CIPHER are complementary, and mutually exclusive with SSL and
X509.
- U(https://mariadb.com/kb/en/securing-connections-for-client-and-server/#requiring-tls).
type: dict
version_added: 1.0.0
version_added_collection: community.mysql
force_context:
default: false
description:
- "Sets the \u0421(mysql) system database as context for the executed statements (it\
\ will be used as a database to connect to). Useful if you use binlog / replication\
\ filters in MySQL as per default the statements can not be caught by a binlog /\
\ replication filter, they require a database to be set to work, otherwise the replication\
\ can break down."
- See U(https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#option_mysqld_binlog-ignore-db)
for a description on how binlog filters work (filtering on the primary).
- See U(https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_replicate-ignore-db)
for a description on how replication filters work (filtering on the replica).
type: bool
version_added: 3.1.0
version_added_collection: community.mysql
check_hostname:
description:
- Whether to validate the server host name when an SSL connection is required. Corresponds
to MySQL CLIs C(--ssl) switch.
- Setting this to C(false) disables hostname verification. Use with caution.
- Requires pymysql >= 0.7.11.
- This option has no effect on MySQLdb.
type: bool
version_added: 1.1.0
version_added_collection: community.mysql
login_password:
description:
- The password used to authenticate with.
type: str
subtract_privs:
default: false
description:
- Revoke the privileges defined by the I(priv) option and keep other existing privileges.
If set, invalid privileges in I(priv) are ignored. Mutually exclusive with I(append_privs).
type: bool
version_added: 3.2.0
version_added_collection: community.mysql
connect_timeout:
default: 30
description:
- The connection timeout when connecting to the MySQL server.
type: int
password_expire:
choices:
- now
- never
- default
- interval
description:
- C(never) - I(password) will never expire.
- C(default) - I(password) is defined using global system variable I(default_password_lifetime)
setting.
- C(interval) - I(password) will expire in days which is defined in I(password_expire_interval).
- C(now) - I(password) will expire immediately.
type: str
version_added: 3.9.0
version_added_collection: community.mysql
resource_limits:
description:
- Limit the user for certain server resources. Provided since MySQL 5.6 / MariaDB
10.2.
- 'Available options are C(MAX_QUERIES_PER_HOUR: num), C(MAX_UPDATES_PER_HOUR: num),
C(MAX_CONNECTIONS_PER_HOUR: num), C(MAX_USER_CONNECTIONS: num), C(MAX_STATEMENT_TIME:
num) (supported only for MariaDB since collection version 3.7.0).'
- Used when I(state=present), ignored otherwise.
type: dict
version_added: 0.1.0
version_added_collection: community.mysql
update_password:
choices:
- always
- on_create
- on_new_username
default: always
description:
- C(always) will update passwords if they differ. This affects I(password) and the
combination of I(plugin), I(plugin_hash_string), I(plugin_auth_string).
- C(on_create) will only set the password or the combination of I(plugin), I(plugin_hash_string),
I(plugin_auth_string) for newly created users.
- 'C(on_new_username) works like C(on_create), but it tries to reuse an existing password:
If one different user with the same username exists, or multiple different users
with the same username and equal C(plugin) and C(authentication_string) attribute,
the existing C(plugin) and C(authentication_string) are used for the new user instead
of the I(password), I(plugin), I(plugin_hash_string) or I(plugin_auth_string) argument.'
type: str
login_unix_socket:
description:
- The path to a Unix domain socket for local connections.
- Use this parameter to avoid the C(Please explicitly state intended protocol) error.
type: str
plugin_auth_string:
description:
- User's plugin auth_string (``CREATE USER user IDENTIFIED WITH plugin BY plugin_auth_string``).
- If I(plugin) is ``pam`` (MariaDB) or ``auth_pam`` (MySQL) an optional I(plugin_auth_string)
can be used to choose a specific PAM service.
type: str
version_added: 0.1.0
version_added_collection: community.mysql
plugin_hash_string:
description:
- User's plugin hash string (``CREATE USER user IDENTIFIED WITH plugin AS plugin_hash_string``).
type: str
version_added: 0.1.0
version_added_collection: community.mysql
check_implicit_admin:
default: false
description:
- Check if mysql allows login as root/nopassword before trying supplied credentials.
- If success, passed I(login_user)/I(login_password) will be ignored.
type: bool
column_case_sensitive:
description:
- The default is C(false).
- When C(true), the module will not uppercase the field names in the privileges.
- When C(false), the field names will be upper-cased. This is the default
- This feature was introduced because MySQL 8 and above uses case sensitive fields
names in privileges.
type: bool
version_added: 3.8.0
version_added_collection: community.mysql
password_expire_interval:
description:
- Number of days I(password) will expire. Requires I(password_expire=interval).
type: int
version_added: 3.9.0
version_added_collection: community.mysql