A quick tour of
MySQL 8.0 roles
Giuseppe Maxia
Software explorer
1
#fosdem
#mysqldevroom
Who's this guy?
About me
‣ Giuseppe Maxia, a.k.a. "The Data Charmer"
‣ QA Architect at VMware
‣ Several decades development and DB
experience
‣ Long timer MySQL community member.
‣ Blog: http://datacharmer.blogspot.com
‣ Twitter: @datacharmer
2
#fosdem
#mysqldevroom
A long coveted feature finally arrives
Roles overview
‣ Available since MySQL 8.0.0
‣ Created like an user
‣ Granted like privileges
‣ Need to be activated (with tricks)
3
Up until the current GA (MySQL 5.7) there were no roles
Before roles
‣ CREATE USER
‣ GRANT, GRANT, and more granular GRANT
‣ CREATE USER
‣ GRANT, GRANT again, and then GRANT
‣ CREATE USER
‣ GRANT, GRANT, GRANT, GRANT, oops!
4
In short: a lot of work,
with many chances to make mistakes
Why bother with this new feature?
Advantages of roles
‣ Faster user administration
•
define a role once
•
assign it many times
‣ Centralised grants handling
•
grant and revoke privileges to roles
•
No need to edit all users profiles
‣ Easy to understand grants statistics
5
A BAD example. (1)
6
So far, so good
A BAD example. (2)
7
WHAT DID JUST HAPPEN ? STAY TUNED TO FIND OUT
8
Roles
usage
1
CREATE ROLE
3
CREATE USER
2
GRANT
PRIVILEGES to
ROLE
4
GRANT ROLE
TO USER
5
SET (DEFAULT)
ROLE
Like creating a user
Create role
CREATE ROLE r_lotr_dev;
## NOTE: there is no "IDENTIFIED" clause
9
Same as we do it with users
grant privileges to role
GRANT ALL ON lotr.* TO r_lotr_dev;
10
This one is already known
Create user
CREATE USER aragorn IDENTIFIED BY
'lotrpwd';
11
We grant a role in a way similar to granting a privilege
Grant role to user
GRANT r_lotr_dev TO aragorn;
## NOTE: there is not an "ON" clause
## in the GRANT statement.
12
The role needs to be activate
Set [default] role
ALTER USER aragorn DEFAULT ROLE
r_lotr_dba;
## OR
SET DEFAULT ROLE r_lotr_dba
TO aragorn;
13
There is more than one way to do it
Unfortunately
Some important points
14
#fosdem
#mysqldevroom
Grants are the total of all roles privileges
A user can be granted more roles
‣ User can have many roles"
‣ The default role can be a list of roles
15
This may cause some confusion
Roles are saved in 'user' table
‣ Roles are users without login (= account locked
and expired password)
16
It is there, but you can't see it
Granting a role to a user is not
enough
‣ When we grant a privilege, the user can use it
immediately."
‣ When we grant a role, we first need to set the
default.
17
This may look tricky, but it is really simple
We can grant a user to a user
‣ Roles are users without login"
‣ But roles with login (i.e. users) can be granted."
‣ Privileges are assigned regardless of the host of
the user."
GRANT root@'localhost' to someuser;
‣ user someuser@'%' has all privileges of root from
any host"
18
You can lose track easily here
SET ROLE anyone?
‣ SET ROLE role_name is a session assignment of
a role"
‣ SET DEFAULT ROLE role_name is a permanent
assignment of a role for a user"
‣ SET ROLE DEFAULT means assign the default
role to this user for the session.
19
Sadly, it's up to the DBA's ingenuity
Telling roles from users
‣ Roles are users with expired password and locked
account."
‣ A good workaround is using a naming convention
to tell roles apart (e.g. "r_something")
20
There is a feature request about this matter,
but I haven’t seen any progress on it.
We have two new tables in 'mysql' DB dedicated to roles
Tables for roles
‣ role_edges reports which roles are assigned to
which users."
‣ default_roles takes track of the current default
roles assigned to users.
21
Roles in action
22
#fosdem
#mysqldevroom
23
Create roles
24
Create users and apply roles
25
Users and roles
26
Finding roles
empirically
27
role_edge table
(Which roles were assigned)
28
default_roles table
(Which roles are set as default)
29
Who are the DBAs?
30
Who are the developers?
31
Roles summary
32
Default roles summary
33
user with default role
34
User without default role
35
User without default role
36
SET ROLE is not permanent
Back to the BAD example. (2)
37
Back to the BAD example. (3)
38
It’s a all-or-nothing option
Roles can be active by default
‣ Starting in 8.0.2"
‣ You can use option activate_all_roles_on_login
•
When enabled, all roles become active by default
‣ And mandatory_roles"
•
When set, all users will get the role(s) defined
39
Example with mandatory roles (1)
mysql> create schema lotr;
Query OK, 1 row affected (0.00 sec)
mysql> grant select on lotr.* to r_lotr_reader;
Query OK, 0 rows affected (0.00 sec)
mysql> set global mandatory_roles='r_lotr_reader';
Query OK, 0 rows affected (0.00 sec)
mysql> create user dummy identified by 'msandbox';
Query OK, 0 rows affected (0.00 sec)
40
Example with mandatory roles (2)
$ mysql lotr -u dummy -p
ERROR 1044 (42000): Access denied for user 'dummy'@'%'
to database ‘lotr'
# ====== as root ========
mysql> set global activate_all_roles_on_login=1;
$ mysql lotr -u dummy -p
mysql> show grants;
+------------------------------------------+
| Grants for dummy@% |
+------------------------------------------+
| GRANT USAGE ON *.* TO `dummy`@`%` |
| GRANT SELECT ON `lotr`.* TO `dummy`@`%` |
| GRANT `r_lotr_reader`@`%` TO `dummy`@`%` |
+------------------------------------------+
3 rows in set (0.00 sec)
41
Example with mandatory roles (3)
$ mysql lotr -u root -p
mysql> show grants\G
*** 1. row ***
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE,
CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER,
SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE,
REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE
ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE,
CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*** 2. row ***
Grants for root@localhost: GRANT
BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPL
ICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_
GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_AD
MIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*** 3. row ***
Grants for root@localhost: GRANT SELECT ON `lotr`.* TO
`root`@`localhost`
42
Latest addition
‣ In 8.0.3+"
‣ You can set the default role within CREATE
USER."
‣ Sounds good"
‣ Until you notice that you are activating a role that
has not been assigned yet. "
😧
43
Q & A
44
@datacharmer
#fosdem
#mysqldevroom
