Try SpotterAdds or removes a user from a MySQL database
Authors: Jonathan Mainguy (@Jmainguy), Benjamin Malynovytch (@bmalynovytch), Lukasz Tomaszkiewicz (@tomaszkiewicz)
Install with ansible-galaxy collection install community.general:==0.2.1
collections:
- name: community.general
version: 0.2.1Adds or removes a user from a MySQL database.
- name: Removes anonymous user account for localhost
mysql_user:
name: ''
host: localhost
state: absent
- name: Removes all anonymous user accounts
mysql_user:
name: ''
host_all: yes
state: absent
- name: Create database user with name 'bob' and password '12345' with all database privileges
mysql_user:
name: bob
password: 12345
priv: '*.*:ALL'
state: present
- name: Create database user using hashed password with all database privileges
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'
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
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.
- name: Modify user to require SSL connections.
mysql_user:
name: bob
append_privs: yes
priv: '*.*:REQUIRESSL'
state: present
- name: 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
# 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.
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
mysql_user:
name: sally
host_all: yes
state: absent
- name: Specify grants composed of more than one word
mysql_user:
name: replication
password: 12345
priv: "*.*:REPLICATION CLIENT"
state: present
- name: 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
- name: Example using login_unix_socket to connect to server
mysql_user:
name: root
password: abc123
login_unix_socket: /var/run/mysqld/mysqld.sock
- name: Example of skipping binary logging while adding user 'bob'
mysql_user:
name: bob
password: 12345
priv: "*.*:USAGE"
state: present
sql_log_bin: no
- name: Create user 'bob' authenticated with plugin 'AWSAuthenticationPlugin'
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
mysql_user:
name: bob
resource_limits:
MAX_QUERIES_PER_HOUR: 10
MAX_CONNECTIONS_PER_HOUR: 5
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.table:priv/db.table: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).
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.2.0
version_added_collection: community.general
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..
type: str
encrypted:
default: false
description:
- Indicate that the 'password' field is a `mysql_native_password` hash.
type: bool
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.
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.
type: bool
login_password:
description:
- The password used to authenticate with.
type: str
connect_timeout:
default: 30
description:
- The connection timeout when connecting to the MySQL server.
type: int
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).'
- Used when I(state=present), ignored otherwise.
type: dict
version_added: 0.2.0
version_added_collection: community.general
update_password:
choices:
- always
- on_create
default: always
description:
- C(always) will update passwords if they differ.
- C(on_create) will only set the password for newly created users.
type: str
login_unix_socket:
description:
- The path to a Unix domain socket for local connections.
type: str
plugin_auth_string:
description:
- User's plugin auth_string (``CREATE USER user IDENTIFIED WITH plugin BY plugin_auth_string``).
type: str
version_added: 0.2.0
version_added_collection: community.general
plugin_hash_string:
description:
- User's plugin hash string (``CREATE USER user IDENTIFIED WITH plugin AS plugin_hash_string``).
type: str
version_added: 0.2.0
version_added_collection: community.general
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