community.mysql.mysql_role – Adds, removes, or updates a MySQL role

Note

This plugin is part of the community.mysql collection (version 2.3.1).

You might already have this collection installed if you are using the ansible package. It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install community.mysql.

To use it in a playbook, specify: community.mysql.mysql_role.

New in version 2.2.0: of community.mysql

Synopsis

  • Adds, removes, or updates a MySQL role.

  • Roles are supported since MySQL 8.0.0 and MariaDB 10.0.5.

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

Parameter Choices/Defaults Comments
admin
string
Supported by MariaDB.
Name of the admin user of the role (the login_user, by default).
append_members
boolean
    Choices:
  • no ←
  • yes
Add members defined by the members option to the existing ones for this role instead of overwriting them.
Mutually exclusive with the detach_members and admin option.
append_privs
boolean
    Choices:
  • no ←
  • yes
Append the privileges defined by the priv option to the existing ones for this role instead of overwriting them.
ca_cert
path
The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server.

aliases: ssl_ca
check_hostname
boolean
added in 1.1.0 of community.mysql
    Choices:
  • no
  • yes
Whether to validate the server host name when an SSL connection is required. Corresponds to MySQL CLIs --ssl switch.
Setting this to false disables hostname verification. Use with caution.
Requires pymysql >= 0.7.11.
This option has no effect on MySQLdb.
check_implicit_admin
boolean
    Choices:
  • no ←
  • yes
Check if mysql allows login as root/nopassword before trying supplied credentials.
If success, passed login_user/login_password will be ignored.
client_cert
path
The path to a client public key certificate.

aliases: ssl_cert
client_key
path
The path to the client private key.

aliases: ssl_key
config_file
path
Default:
"~/.my.cnf"
Specify a config file from which user and password are to be read.
connect_timeout
integer
Default:
30
The connection timeout when connecting to the MySQL server.
detach_members
boolean
    Choices:
  • no ←
  • yes
Detaches members defined by the members option from the role instead of overwriting all the current members.
Mutually exclusive with the append_members and admin option.
login_host
string
Default:
"localhost"
Host running the database.
In some cases for local connections the login_unix_socket=/path/to/mysqld/socket, that is usually /var/run/mysqld/mysqld.sock, needs to be used instead of login_host=localhost.
login_password
string
The password used to authenticate with.
login_port
integer
Default:
3306
Port of the MySQL server. Requires login_host be defined as other than localhost if login_port is used.
login_unix_socket
string
The path to a Unix domain socket for local connections.
login_user
string
The username used to authenticate with.
members
list / elements=string
List of members of the role.
For users, use the format [email protected]. Always specify the hostname part explicitly.
For roles, use the format rolename.
Mutually exclusive with admin.
name
string / required
Name of the role to add or remove.
priv
raw
MySQL privileges string in the format: db.table:priv1,priv2.
You can specify multiple privileges 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))).
Can be passed as a dictionary (see the examples).
Supports GRANTs for procedures and functions (see the examples for the community.mysql.mysql_user module).
set_default_role_all
boolean
    Choices:
  • no
  • yes ←
Is not supported by MariaDB and is silently ignored when working with MariaDB.
If yes, runs SET DEFAULT ROLE ALL TO each of the members when changed.
If you want to avoid this behavior, set this option to no explicitly.
state
string
    Choices:
  • absent
  • present ←
If present and the role does not exist, creates the role.
If present and the role exists, does nothing or updates its attributes.
If absent, removes the role.

Notes

Note

  • Pay attention that the module runs SET DEFAULT ROLE ALL TO all the members passed by default when the state has changed. If you want to avoid this behavior, set set_default_role_all to no.

  • 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_password and login_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.

  • 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_user

The official documentation on the community.mysql.mysql_user module.

MySQL role reference

Complete reference of the MySQL role documentation.

Examples

# Example of a .my.cnf file content for setting a root password
# [client]
# user=root
# password=n<_665{vS43y
#
# Example of a privileges dictionary passed through the priv option
# priv:
#   'mydb.*': 'INSERT,UPDATE'
#   'anotherdb.*': 'SELECT'
#   'yetanotherdb.*': 'ALL'
#
# You can also use the string format like in the community.mysql.mysql_user module, for example
# mydb.*:INSERT,UPDATE/anotherdb.*:SELECT/yetanotherdb.*:ALL
#
# For more examples on how to specify privileges, refer to the community.mysql.mysql_user module

# Create a role developers with all database privileges
# and add alice and bob as members.
# The statement 'SET DEFAULT ROLE ALL' to them will be run.
- name: Create role developers, add members
  community.mysql.mysql_role:
    name: developers
    state: present
    priv: '*.*:ALL'
    members:
    - '[email protected]%'
    - '[email protected]%'

- name: Same as above but do not run SET DEFAULT ROLE ALL TO each member
  community.mysql.mysql_role:
    name: developers
    state: present
    priv: '*.*:ALL'
    members:
    - '[email protected]%'
    - '[email protected]%'
    set_default_role_all: no

# Assuming that the role developers exists,
# add john to the current members
- name: Add members to an existing role
  community.mysql.mysql_role:
    name: developers
    state: present
    append_members: yes
    members:
    - '[email protected]'

# Create role readers with the SELECT privilege
# on all tables in the fiction database
- name: Create role developers, add members
  community.mysql.mysql_role:
    name: readers
    state: present
    priv: 'fiction.*:SELECT'

# Assuming that the role readers exists,
# add the UPDATE privilege to the role on all tables in the fiction database
- name: Create role developers, add members
  community.mysql.mysql_role:
    name: readers
    state: present
    priv: 'fiction.*:UPDATE'
    append_privs: yes

- name: Create role with the 'SELECT' and 'UPDATE' privileges in db1 and db2
  community.mysql.mysql_role:
    state: present
    name: foo
    priv:
      'db1.*': 'SELECT,UPDATE'
      'db2.*': 'SELECT,UPDATE'

- name: Remove joe from readers
  community.mysql.mysql_role:
    state: present
    name: readers
    members:
    - '[email protected]'
    detach_members: yes

- name: Remove the role readers if exists
  community.mysql.mysql_role:
    state: absent
    name: readers

- name: Example of using login_unix_socket to connect to the server
  community.mysql.mysql_role:
    name: readers
    state: present
    login_unix_socket: /var/run/mysqld/mysqld.sock

# Pay attention that the admin cannot be changed later
# and will be ignored if a role currently exists.
# To change members, you need to run a separate task using the admin
# of the role as the login_user.
- name: On MariaDB, create the role readers with alice as its admin
  community.mysql.mysql_role:
    state: present
    name: readers
    admin: '[email protected]%'

- name: Create the role business, add the role marketing to members
  community.mysql.mysql_role:
    state: present
    name: business
    members:
    - marketing

Authors

  • Andrew Klychkov (@Andersson007)