root@6d623b7b5bcd:/# mysql -u root -ppassword

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create role s;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select, insert, update, delete, lock tables, execute on t.* to s;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create role a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant s to a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on t.* to a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create user u;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant a to u;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant s to u;

Query OK, 0 rows affected (0.00 sec)

root@6d623b7b5bcd:/# mysql -u u

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 9

Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;

+-------------------------------+

| Grants for u@%                |

+-------------------------------+

| GRANT a TO 'u'@'%'            |

| GRANT s TO 'u'@'%'            |

| GRANT USAGE ON *.* TO 'u'@'%' |

+-------------------------------+

3 rows in set (0.00 sec)

MariaDB [(none)]> create database t;

ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'

MariaDB [(none)]> set role a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database t;

ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'

MariaDB [(none)]> show grants;

+----------------------------------------------------------------------------+

| Grants for u@%                                                             |

+----------------------------------------------------------------------------+

| GRANT a TO 'u'@'%'                                                         |

| GRANT s TO 'u'@'%'                                                         |

| GRANT USAGE ON *.* TO 'u'@'%'                                              |

| GRANT s TO 'a'                                                             |

| GRANT USAGE ON *.* TO 'a'                                                  |

| GRANT ALL PRIVILEGES ON `t`.* TO 'a'                                       |

| GRANT USAGE ON *.* TO 's'                                                  |

| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 's' |

+----------------------------------------------------------------------------+

8 rows in set (0.00 sec)

MariaDB [(none)]> select current_role;

+--------------+

| current_role |

+--------------+

| a            |

+--------------+

1 row in set (0.00 sec)