One of the most interesting features introduced in MySQL 8.0 is roles or the ability of defining a set of privileges as a named role and then granting that set to one or more users. The main benefits are more clarity of privileges and ease of administration. Using roles we can assign the same set of privileges to several users, and eventually modify or revoke all privileges at once.
Roles in a nutshell
Looking at the manual, we see that using roles is a matter of several steps.
(1) Create a role. The statement is similar to CREATE USER though the effects are slightly different (we will see it in more detail later on.)
mysql [localhost] {root} ((none)) > CREATE ROLE r_lotr_dev;
Query OK, 0 rows affected (0.02 sec)
(2) Grant privileges to the role. Again, this looks like granting privileges to a user.
mysql [localhost] {root} ((none)) > GRANT ALL on lotr.* TO r_lotr_dev;
Query OK, 0 rows affected (0.01 sec)
(3) Create a user. This is the same that we've been doing until version 5.7.
mysql [localhost] {root} (mysql) > create user aragorn identified by 'lotrpwd';
Query OK, 0 rows affected (0.01 sec)
Notice that the role is in the mysql.user table, and looks a lot like a user.
mysql [localhost] {root} ((none)) > select host, user, authentication_string from mysql.user where user not like '%sandbox%;
+-----------+-------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+-------------+-------------------------------------------+
| % | r_lotr_dev | |
| % | aragorn | *3A376D0203958F6EB9E6166DC048EC04F84C00B9 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | root | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
+-----------+-------------+-------------------------------------------+
(4) Grant the role to the user. Instead of granting single privileges, we grant the role. Note that when we use this syntax we can't specify the ON xxx clause, because it is already implicit in the role definition.
mysql [localhost] {root} (mysql) > grant r_lotr_dev to aragorn;
Query OK, 0 rows affected (0.03 sec)
The relationship between user and role is recorded in a new table in the mysql database:
mysql [localhost] {root} (mysql) > select * from mysql.role_edges;
+-----------+------------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+------------+---------+---------+-------------------+
| % | r_lotr_dev | % | aragorn | N |
+-----------+------------+---------+---------+-------------------+
(5) Finally we set the default role. Until this point, the role is assigned to the user, but not active. We either set the default role permanently (as done below) or we let the user activate the role.
mysql [localhost] {root} (mysql) > alter user aragorn default role r_lotr_dba;
Query OK, 0 rows affected (0.01 sec)
If a default role has been set, it is recorded in another new table, mysql.default_roles.
mysql [localhost] {root} (mysql) > select * from mysql.default_roles;
+------+---------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+---------+-------------------+-------------------+
| % | aragorn | % | r_lotr_dba |
+------+---------+-------------------+-------------------+
Common gotchas
If we follow all the steps described above, using roles would not feel any different than using old style grants. But it is easy to go astray if we skip something. Let's see an example. First, we create an user and grant the same role as the one given to user aragorn:
mysql [localhost] {root} (mysql) > create user legolas identified by 'lotrpwd';
Query OK, 0 rows affected (0.03 sec)
mysql [localhost] {root} (mysql) > grant r_lotr_dev to legolas;
Query OK, 0 rows affected (0.01 sec)
Then we connect using user legolas:
mysql [localhost] {legolas} ((none)) > use lotr;
ERROR 1044 (42000): Access denied for user 'legolas'@'%' to database 'lotr'
mysql [localhost] {legolas} ((none)) > show grants;
+-----------------------------------------+
| Grants for legolas@% |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `legolas`@`%` |
| GRANT `r_lotr_dev`@`%` TO `legolas`@`%` |
+-----------------------------------------+
2 rows in set (0.00 sec)
mysql [localhost] {legolas} ((none)) > show grants for legolas using r_lotr_dev;
+---------------------------------------------------+
| Grants for legolas@% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `legolas`@`%` |
| GRANT ALL PRIVILEGES ON `lotr`.* TO `legolas`@`%` |
| GRANT `r_lotr_dev`@`%` TO `legolas`@`%` |
+---------------------------------------------------+
3 rows in set (0.00 sec)
The role is assigned to the user, but it is not active, as no default role was defined. To use a role, the user must activate one:
mysql [localhost] {legolas} ((none)) > select current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.01 sec)
There is no default role for legolas. We need to assign one.
mysql [localhost] {legolas} ((none)) > set role r_lotr_dev;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {legolas} ((none)) > select current_role();
+------------------+
| current_role() |
+------------------+
| `r_lotr_dev`@`%` |
+------------------+
1 row in set (0.00 sec)
Now the role is active, and all its privileges kick in:
mysql [localhost] {legolas} ((none)) > use lotr
Database changed
mysql [localhost] {legolas} (lotr) > show tables;
Empty set (0.00 sec)
mysql [localhost] {legolas} (lotr) > create table t1 (i int not null primary key);
Query OK, 0 rows affected (0.15 sec)
Note that the role activation is volatile. If the user reconnects, the role activation goes away:
mysql [localhost] {legolas} ((none)) > connect;
Connection id: 33
Current database: *** NONE ***
mysql [localhost] {legolas} ((none)) > select current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.01 sec)
For a permanent assignment, the user can use the SET DEFAULT ROLE statement:
mysql [localhost] {legolas} ((none)) > set default role r_lotr_dev to legolas;
Query OK, 0 rows affected (0.01 sec)
The above statement corresponds to ALTER USER ... DEFAULT ROLE .... Every user can set its own role with this statement, without needing additional privileges.
mysql [localhost] {legolas} ((none)) > alter user legolas default role r_lotr_dev;
Query OK, 0 rows affected (0.01 sec)
Now if the user reconnects, the role persists:
mysql [localhost] {legolas} ((none)) > connect
Connection id: 34
Current database: *** NONE ***
mysql [localhost] {legolas} ((none)) > select current_role();
+------------------+
| current_role() |
+------------------+
| `r_lotr_dev`@`%` |
+------------------+
1 row in set (0.01 sec)
However, if an user sets its own default role using ALTER USER, the change will be available only in the next session, or after calling SET ROLE.
Let's try:
mysql [localhost] {legolas} ((none)) > set default role none to legolas;
Query OK, 0 rows affected (0.02 sec)
mysql [localhost] {legolas} ((none)) > select current_role();
+------------------+
| current_role() |
+------------------+
| `r_lotr_dev`@`%` |
+------------------+
1 row in set (0.00 sec)
The role stays what it was before. This is similar to what happens when using SET GLOBAL var_name vs SET SESSION var_name. In the first case the effect persists, but it is not activated immediately, while a session set will be immediately usable, but will not persist after a new connection.
mysql [localhost] {legolas} ((none)) > connect
Connection id: 35
Current database: *** NONE ***
select current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
It's worth mentioning that SET DEFAULT ROLE implies an hidden update of a mysql table (default_roles), similar to what happens with SET PASSWORD. In both cases, a user without explicit access to the mysql database will be unable to check the effects of the operation.
Advanced role management.
Dealing with one or two roles is no big deal. Using the statements seen above, we can easily see what privileges were granted to a role or an user. When we have many roles and many users, the overview become more difficult to achieve.
Before we see the complex scenario, let's have a deeper look at what constitutes a role.
mysql [localhost] {root} (mysql) > create role role1;
Query OK, 0 rows affected (0.05 sec)
mysql [localhost] {root} (mysql) > create user user1 identified by 'somepass';
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {root} (mysql) > select host, user, authentication_string , password_expired , account_locked from user where user in ('role1', 'user1');
+------+-------+-------------------------------------------+------------------+----------------+
| host | user | authentication_string | password_expired | account_locked |
+------+-------+-------------------------------------------+------------------+----------------+
| % | role1 | | Y | Y |
| % | user1 | *13883BDDBE566ECECC0501CDE9B293303116521A | N | N |
+------+-------+-------------------------------------------+------------------+----------------+
2 rows in set (0.00 sec)
The main difference between user and role is that a role is created with password_expired and account_locked. Apart from that, an user could be used as a role and vice versa.
mysql [localhost] {root} (mysql) > alter user role1 identified by 'msandbox';
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {root} ((none)) > alter user role1 account unlock;
Query OK, 0 rows affected (0.02 sec)
Now role1 can access the database as any other user.
mysql [localhost] {root} ((none)) > grant root@'localhost' to user1;
Query OK, 0 rows affected (0.03 sec)
And user1 inherits all privileges from root, but it can access the server from any host.
Now let's see some complex usage of roles. In a typical organisation, we would define several roles to use the lotr database:
CREATE ROLE r_lotr_observer;
CREATE ROLE r_lotr_tester;
CREATE ROLE r_lotr_dev;
CREATE ROLE r_lotr_dba;
GRANT SELECT on lotr.* TO r_lotr_observer;
GRANT SELECT, INSERT, UPDATE, DELETE on lotr.* TO r_lotr_tester;
GRANT ALL on lotr.* TO r_lotr_dev;
GRANT ALL on *.* TO r_lotr_dba;
And then assign those roles to several users:
CREATE USER bilbo IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER frodo IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER sam IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER pippin IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER merry IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER boromir IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER gimli IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER aragorn IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER legolas IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER gollum IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER galadriel IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER gandalf IDENTIFIED BY 'msandbox';
GRANT r_lotr_observer TO pippin, merry, boromir, gollum;
SET DEFAULT ROLE r_lotr_observer to pippin, merry, boromir, gollum;
GRANT r_lotr_tester TO sam, bilbo, gimli;
SET DEFAULT ROLE r_lotr_tester to sam, bilbo, gimli;
GRANT r_lotr_dev to frodo, aragorn, legolas;
SET DEFAULT ROLE r_lotr_dev to frodo, aragorn, legolas;
GRANT r_lotr_dba TO gandalf, galadriel;
SET DEFAULT ROLE r_lotr_dba to gandalf, galadriel;
Now we have 12 users with 4 different roles. Looking at the user table, we don't get a good overview:
mysql [localhost] {root} (mysql) > select host, user, authentication_string from mysql.user where user not like '%sandbox%';
+-----------+-----------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+-----------------+-------------------------------------------+
| % | aragorn | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | bilbo | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | boromir | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | frodo | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | galadriel | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | gandalf | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | gimli | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | gollum | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | legolas | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | merry | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | pippin | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| % | r_lotr_dba | |
| % | r_lotr_dev | |
| % | r_lotr_observer | |
| % | r_lotr_tester | |
| % | sam | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | root | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
+-----------+-----------------+-------------------------------------------+
And even the roles_edges table does show a clear picture:
mysql [localhost] {root} (mysql) > select * from role_edges;
+-----------+-----------------+---------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------------+---------+-----------+-------------------+
| % | r_lotr_dba | % | galadriel | N |
| % | r_lotr_dba | % | gandalf | N |
| % | r_lotr_dev | % | aragorn | N |
| % | r_lotr_dev | % | frodo | N |
| % | r_lotr_dev | % | legolas | N |
| % | r_lotr_observer | % | boromir | N |
| % | r_lotr_observer | % | gollum | N |
| % | r_lotr_observer | % | merry | N |
| % | r_lotr_observer | % | pippin | N |
| % | r_lotr_tester | % | bilbo | N |
| % | r_lotr_tester | % | gimli | N |
| % | r_lotr_tester | % | sam | N |
+-----------+-----------------+---------+-----------+-------------------+
Here's a better use of that table's data. Which users are using the dev role?
select to_user as users from role_edges where from_user = 'r_lotr_dev';
+---------+
| users |
+---------+
| aragorn |
| frodo |
| legolas |
+---------+
3 rows in set (0.00 sec)
And the testers?
select to_user as users from role_edges where from_user = 'r_lotr_tester';
+-------+
| users |
+-------+
| bilbo |
| gimli |
| sam |
+-------+
3 rows in set (0.00 sec)
Or, even better, we could see all the roles at once:
select from_user as role, count(*) as how_many_users, group_concat(to_user) as users from role_edges group by role;
+-----------------+----------------+-----------------------------+
| role | how_many_users | users |
+-----------------+----------------+-----------------------------+
| r_lotr_dba | 2 | galadriel,gandalf |
| r_lotr_dev | 3 | aragorn,frodo,legolas |
| r_lotr_observer | 4 | boromir,gollum,merry,pippin |
| r_lotr_tester | 3 | bilbo,gimli,sam |
+-----------------+----------------+-----------------------------+
4 rows in set (0.01 sec)
Similarly, we could list the default role for several users:
select default_role_user as default_role, group_concat(user) as users from default_roles group by default_role;
+-----------------+-----------------------------+
| default_role | users |
+-----------------+-----------------------------+
| r_lotr_dba | galadriel,gandalf |
| r_lotr_dev | aragorn,frodo,legolas |
| r_lotr_observer | boromir,gollum,merry,pippin |
| r_lotr_tester | bilbo,gimli,sam |
+-----------------+-----------------------------+
The latest two queries should be good candidates for information_schema views.
Another candidate for an information_schema view is the list of roles, for which there is no satisfactory workaround now. The best we could think of is a list of users with password_expired and account_locked:
select host, user from mysql.user where password_expired='y' and account_locked='y';
+------+-----------------+
| host | user |
+------+-----------------+
| % | r_lotr_dba |
| % | r_lotr_dev |
| % | r_lotr_observer |
| % | r_lotr_tester |
+------+-----------------+
4 rows in set (0.00 sec)
Until a feature to differentiate users and roles is developed, it is advisable to use a name format that helps identify roles without help from the server. In this article I am using r_ as a prefix, which makes role listing easier.
mysql [localhost] {root} (mysql) > select host, user from mysql.user where user like 'r_%';
+------+-----------------+
| host | user |
+------+-----------------+
| % | r_lotr_dba |
| % | r_lotr_dev |
| % | r_lotr_observer |
| % | r_lotr_tester |
+------+-----------------+
4 rows in set (0.00 sec)
Known bugs
- bug#85562 Dropping a role does not remove privileges from active users.
- bug#85561 Users can be assigned non-existing roles as default.
- bug#85559 Dropping a role does not remove the associated default roles.
- bug#84244 Distinguish roles and plain users in mysql.user.
- bug#82987 SHOW CREATE USER doesn't show default role.
Summing up
Roles are a great addition to the MySQL feature set. The usability could be improved with some views in information_schema (usersforrole, defaultroleforuser) and functions in sys schema (defaultroleforuser, is_role.)
Perhaps some commands to activate or deactivate roles could make administration easier. The current bugs don't affect the basic functionality but decrease usability. I think that another run of bug fixing and user feedback would significantly improve this feature.
More about roles in my talk at PerconaLive 2017.
2 comments:
Nice to have a similar concept like PostgreSQL.
Statements like "SHOW GRANTS FOR user1 USING role1" will show privileges granted to user1 through role1. If multiple roles are granted to an user, currently there is no way to see consolidated list of privileges that an user can enjoy. We have to fist see all the granted roles for an user, and then execute another statement like : "SHOW GRANTS FOR user1 USING role1, role2, ....". At times admin may need this type of information. Something like following can be implemented : "SHOW GRANTS FOR user1 USING ALL", which will show all privileges an user have.
Post a Comment