IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
In this article, we are going to talk about the new feature in MySQL 8 related privilege system, which is Roles. So, let’s talk about roles, this article is completely devoted to roles.
Other users may have complex privileges, a common example for this is a user used to report, this user does not need to write any data, but there is also the possibility it doesn’t need to read all data. It is very likely that it can be used only for a few specific tables or columns, or it can only have selected access to a set of views that will also be a specific one.
These privileges can add up quickly and end up in very long user definitions, we will not use a definition, and more grants are making room for more mistakes. In MySQL 8 these privilege sets can be defined as Roles and Roles can be granted to users instead of basic MySQL privileges. Roles are a collection of privileges that we grant to users.
Just like a new user created with the create user command we use to create a role as mentioned below;
CREATE ROLE 'reportrole';
You can create multiple roles just as below query in one go.
CREATE ROLE 'app_ro', 'app_w', 'app_dev';
New role created with the create role command will have no privileges associated to it, permissions can be granted to this role as it was a normal user.
In the example below, we grant select privilege on all the tables in the COMPANY database to reportrole role we created above.
GRANT SELECT ON company.* to repotrole;
The asterisk (*) represents all tables inside the company database.
Roles can be granted to MySQL users, in the example below, we are creating the ‘company_ro’ at localhost user.
CREATE USER IF NOT EXISTS 'company_ro'@'localhost' IDENTIFIED WITH mysql_native_password BY 'company_pass' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
Remember that the user is not just a username but rather its user at the host in MySQL and then the repotrole role can be granted to it with grant repotrole to the user as showed below.
GRANT 'repotrole' to 'company_ro'@'localhost';
The current role function of MySQL helps to determine what role does the current user has.
mysql> select current_role(); +----------------+ | current_role() | +----------------+ | NONE | +----------------+
The privileges of role stack up meaning that the users will have the privileges described by the Union of the base privileges and its roles have. Very important that if the user is granted the role it’s not activated by default. we will see this latter at the end of this section.
Defining Mandatory Roles
Mandatory roles are roles that will be associated by all users by default is controlled by the mandatory variable.
SET PERSIST mandatory_roles = ‘role1,role2′;
mysql> SET PERSIST mandatory_roles=’dbt3_read23’;
Mandatory roles, like explicitly granted roles, do not take effect until activated, you will see how to active role latter in this post.
Practice Roles
Let’s do this practically, we create a role and assign it to multiple users, we will add privileges on all the tables on company database and we will add another select privilege on the sys table as shown below.
mysql> create role 'repotrole';
So grant the privileges to the reporting user.
mysql> grant select on company.* to 'repotrole'; mysql> grant select on sys.version to 'repotrole';
Let’s create to users, user1 at localhost and user2 at localhost with different passwords
mysql> create user 'user1'@'localhost' identified by 'foo'; mysql> create user 'user2'@'localhost' identified by 'bar'; mysql> grant 'repotrole' to 'user1'@'localhost'; mysql> grant 'repotrole' to 'user2'@'localhost';
They are created, assign the repotrole role to them, and then check with show grants if they have the repotrole role.
mysql> show grants for 'user1'@'localhost'; +----------------------------------------------+ | Grants for user1@localhost | +----------------------------------------------+ | GRANT USAGE ON *.* TO `user1`@`localhost` | | GRANT `repotrole`@`%` TO `user1`@`localhost` | +----------------------------------------------+ mysql> show grants for 'user2'@'localhost'; +----------------------------------------------+ | Grants for user2@localhost | +----------------------------------------------+ | GRANT USAGE ON *.* TO `user2`@`localhost` | | GRANT `repotrole`@`%` TO `user2`@`localhost` | +----------------------------------------------+
They do so instead of having to spell out both privileges for both users we just created the repotrole role and that role added both privileges to both new users we created.
Check the users with pt-show-grants as well.
User1
[root@localhost ~]# pt-show-grants | grep user1 -- Grants for 'dbt3_user1'@'%' CREATE USER IF NOT EXISTS 'dbt3_user1'@'%'; ALTER USER 'dbt3_user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; GRANT USAGE ON *.* TO `dbt3_user1`@`%`; GRANT `dbt3_reader`@`%` TO `dbt3_user1`@`%`; -- Grants for 'user1'@'localhost' CREATE USER IF NOT EXISTS 'user1'@'localhost'; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; GRANT USAGE ON *.* TO `user1`@`localhost`; GRANT `repotrole`@`%` TO `user1`@`localhost`;
User2
[root@localhost ~]# pt-show-grants | grep user2 -- Grants for 'dbt3_user2'@'%' CREATE USER IF NOT EXISTS 'dbt3_user2'@'%'; ALTER USER 'dbt3_user2'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' DEFAULT ROLE `dbt3_read23`@`%` REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; GRANT USAGE ON *.* TO `dbt3_user2`@`%`; -- Grants for 'user2'@'localhost' CREATE USER IF NOT EXISTS 'user2'@'localhost'; ALTER USER 'user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; GRANT USAGE ON *.* TO `user2`@`localhost`; GRANT `repotrole`@`%` TO `user2`@`localhost`;
So pt-show-grants show the repotrole role for both users.
If you want to learn how to use pt-show-grants use link below
Let’s start the MySQL client again and this time grant the repotrole role to comp1 user.
mysql> grant 'repotrole' to 'comp1'@'localhost';
Let’s exit from the MySQL client and Logan comp1 user, just specify the username and the password on the command line.
Specifying the password on the command line on production systems is not a good idea because it will be recorded in history. Let’s check for permissions with show grants.
mysql> show grants; +----------------------------------------------------------------+ | Grants for comp1@localhost | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO `comp1`@`localhost` | | GRANT `dbt3_read23`@`%`,`repotrole`@`%` TO `comp1`@`localhost` |
So the user has the repotrole role but its privileges are not activated yet. You can see ‘dbt3_read23’ role is already there because this role has been set as PERSIST mandatory_roles, when you create a new user this role will be assigned automatically.
Activating Roles
We can use set role repotrole to activate the role and then we check show grants.
mysql> set role 'repotrole'; Query OK, 0 rows affected (0.00 sec) mysql> show grants; +----------------------------------------------------------------+ | Grants for comp1@localhost | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO `comp1`@`localhost` | | GRANT SELECT ON `company`.* TO `comp1`@`localhost` | | GRANT SELECT ON `sys`.`version` TO `comp1`@`localhost` | | GRANT `dbt3_read23`@`%`,`repotrole`@`%` TO `comp1`@`localhost` | +----------------------------------------------------------------+
You can see that the comp1 user already had the privileges to all the company tables, this is why we granted the role to this user for this exercise but the repotrole role will add the new privilege to the system version table.
We can set back to the default role and check the privileges again.
mysql> set role none; Query OK, 0 rows affected (0.00 sec) mysql> show grants; +----------------------------------------------------------------+ | Grants for comp1@localhost | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO `comp1`@`localhost` | | GRANT `dbt3_read23`@`%`,`repotrole`@`%` TO `comp1`@`localhost` | +----------------------------------------------------------------+ This time the user kept the dbt3_read23 role because it had it as base privileges but it won't have the privilege to sys.version table that version anymore because that came from the role.
