You are reading an unmaintained version of the Ansible documentation. Unmaintained Ansible versions can contain unfixed security vulnerabilities (CVE). Please upgrade to a maintained version. See the latest Ansible documentation.

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

Note

This module is part of the community.mysql collection (version 2.3.8).

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
Requirements
Parameters
Notes
See Also
Examples

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.

mysqlclient (Python 3.5+) or
PyMySQL (Python 2.7 and Python 3.x) or
MySQLdb (Python 2.x)

Parameters 

Parameter	Comments
admin string	Supported by MariaDB. Name of the admin user of the role (the login_user, by default).
append_members boolean	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. Choices: no ← (default) yes
append_privs boolean	Append the privileges defined by the priv option to the existing ones for this role instead of overwriting them. Choices: no ← (default) yes
ca_cert aliases: ssl_ca path	The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server.
check_hostname boolean added in 1.1.0 of community.mysql	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. Choices: no yes
check_implicit_admin boolean	Check if mysql allows login as root/nopassword before trying supplied credentials. If success, passed login_user/login_password will be ignored. Choices: no ← (default) yes
client_cert aliases: ssl_cert path	The path to a client public key certificate.
client_key aliases: ssl_key path	The path to the client private key.
config_file path	Specify a config file from which user and password are to be read. Default: “~/.my.cnf”
connect_timeout integer	The connection timeout when connecting to the MySQL server. Default: 30
detach_members boolean	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. Choices: no ← (default) yes
login_host string	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. Default: “localhost”
login_password string	The password used to authenticate with.
login_port integer	Port of the MySQL server. Requires login_host be defined as other than localhost if login_port is used. Default: 3306
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 `username@hostname`. 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	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. Choices: no yes ← (default)
state string	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. Choices: absent present ← (default)

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 mysqlclient, PyMySQL, or MySQLdb library installed on the target machine for the Python interpreter Ansible discovers. For example if ansible discovers and uses Python 3, you need to install the Python 3 version of PyMySQL or mysqlclient. If ansible discovers and uses Python 2, you need to install the Python 2 version of either PyMySQL or MySQL-python.
If you have trouble, it may help to force Ansible to use the Python interpreter you need by specifying ansible_python_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.

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:
    - 'alice@%'
    - 'bob@%'

- 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:
    - 'alice@%'
    - 'bob@%'
    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:
    - 'joe@localhost'

# 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:
    - 'joe@localhost'
    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: 'alice@%'

- 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)
Felix Hamme (@betanummeric)

Collection links

Issue Tracker Repository (Sources)

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

Synopsis

Requirements

Parameters

Notes

See Also

Examples