community / community.mysql / 3.9.0 / module / mysql_user Adds 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)community.mysql.mysql_user (3.9.0) — module
Install with ansible-galaxy collection install community.mysql:==3.9.0
collections: - name: community.mysql version: 3.9.0
Adds 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