Using roles to control user permissions

Roles are an alternative way of managing permissions. They are used to give users permissions as a group instead of individually. For example, all users from the finance department could be assigned to a finance role with permissions specific to the tasks they need to perform.

Roles were first introduced in MariaDB 10.0.

How to do it...

To create an example role and demonstrate how roles work, perform the following steps:

1. Launch the mysql command-line client and connect to our MariaDB database server.

2. Create a test database, if it doesn't exist, using the following statement:

   CREATE DATABASE IF NOT EXISTS test;
   

3. Run the following command to create a role:

   CREATE ROLE read_only; 
   

4. Grant the role some permissions using the following statement:

   GRANT SELECT ON test.* TO read_only; 
   GRANT USAGE  ON test.* TO read_only;
   

5. Display the permissions granted to the role using the following statement:

   SHOW GRANTS FOR read_only;
   

   The output of the preceding statement is...