The role function is not special for Oracle database. However, for MySQL, the role function added in MySQL 8.0 is quite novel, which is also what many users of MySQL have been expecting
The role function added in MySQL 8.0 allows DBAs to create roles, grant permissions to them, and authorize users. Using roles can greatly reduce the work of DBAs, and easily manage various complex permissions of each team and user.
Here's how to use roles in MySQL:
1, Create delete role:
CREATE ROLE uses the "CREATE ROLE" statement, assuming that we create read-only roles, read-write roles, and developer roles for the application.
root@localhost:mysql.sock 20:37: [(none)]>create role 'db_read', 'db_rw', 'db_dev'; Query OK, 0 rows affected (0.08 sec)
The name of the role is very similar to the account name of MySQL, which is composed of user name + host name. If the host name is omitted, it defaults to '%'.
2, Delete role use:
root@localhost:mysql.sock 20:39: [(none)]>drop role db_dev; Query OK, 0 rows affected (0.08 sec)
3, Grant / revoke authority:
After a role is created, it needs to be given corresponding permissions. Use the following statements to give full, read-only, and read-write permissions to roles:
All permissions:
root@localhost:mysql.sock 20:43: [(none)]>create role 'db_dev'; Query OK, 0 rows affected (0.03 sec) root@localhost:mysql.sock 20:44: [(none)]>grant all on app_db.* to 'db_dev'; Query OK, 0 rows affected (0.04 sec)
Read only permission:
root@localhost:mysql.sock 20:44: [(none)]>grant select on app_db.* to 'db_read'; Query OK, 0 rows affected (0.08 sec)
Read write permission:
root@localhost:mysql.sock 20:46: [(none)]>grant select,insert,update,delete on app_db.* to 'db_rw'; Query OK, 0 rows affected (0.07 sec)
4, After the role permission is granted, the corresponding role is granted to the user
For example:
Create user:
root@localhost:mysql.sock 20:50: [(none)]>create user 'db_dev_user'@'localhost' identified by 'passjw01'; Query OK, 0 rows affected (0.09 sec) root@localhost:mysql.sock 20:52: [(none)]>create user 'db_read_user'@'localhost' identified by 'passjw02'; Query OK, 0 rows affected (0.08 sec) root@localhost:mysql.sock 20:52: [(none)]>create user 'db_rw_user'@'localhost' identified by 'passjw03'; Query OK, 0 rows affected (0.05 sec)
Grant role permissions:
root@localhost:mysql.sock 20:53: [(none)]>grant 'db_dev' to 'db_dev_user'@'localhost'; Query OK, 0 rows affected (0.07 sec) root@localhost:mysql.sock 20:54: [(none)]>grant 'db_read' to 'db_read_user'@'localhost'; Query OK, 0 rows affected (0.02 sec) root@localhost:mysql.sock 20:54: [(none)]>grant 'db_rw' to 'db_rw_user'@'localhost'; Query OK, 0 rows affected (0.07 sec)
5, To revoke a user's role and revoke a role's permissions:
root@localhost:mysql.sock 20:54: [(none)]>revoke 'db_dev' from 'db_dev_user'@'localhost'; Query OK, 0 rows affected (0.03 sec) root@localhost:mysql.sock 20:55: [(none)]>revoke all on app_db.* from db_dev; Query OK, 0 rows affected (0.10 sec) root@localhost:mysql.sock 20:57: [(none)]>
6, To view permissions for a role:
After the role is granted to the user, we can view the permissions the user has and execute:
root@localhost:mysql.sock 20:59: [(none)]>show grants for 'db_rw_user'@'localhost'; +------------------------------------------------+ | Grants for db_rw_user@localhost | +------------------------------------------------+ | GRANT USAGE ON *.* TO `db_rw_user`@`localhost` | | GRANT `db_rw`@`%` TO `db_rw_user`@`localhost` | +------------------------------------------------+ 2 rows in set (0.00 sec)
Do you notice that executing the show grants statement only shows that the user has been given the role, and how to view the permissions of the role? Execute the following statement to confirm the content:
root@localhost:mysql.sock 21:01: [(none)]>show grants for 'db_rw_user'@'localhost' using 'db_rw'; +--------------------------------------------------------------------------------+ | Grants for db_rw_user@localhost | +--------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `db_rw_user`@`localhost` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `db_rw_user`@`localhost` | | GRANT `db_rw`@`%` TO `db_rw_user`@`localhost` | +--------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
7, Mandatory roles:
MySQL can configure mandatory roles through the mandatory roles variable. If you use a mandatory role, the server will assign it to all accounts by default,
There is no need to show the role assigned to the execution. You can use my.cnf file or SET PERSIST for configuration, for example:
[mysqld] mandatory_roles='role1,role2@localhost'
SET PERSIST mandatory_roles = 'role1,role2@localhost;
Note that the roles configured in the mandatory roles cannot be revoked or deleted.
8, Role auto activation:
The role assigned to a user account can be active or inactive in an account session. If the given role is active in the session,
Otherwise, it has no corresponding permission. To determine which roles are active in the current session, use the current? Role() function.
root@localhost:mysql.sock 21:01: [(none)]>select CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | NONE | +----------------+ 1 row in set (0.00 sec)
By default, a role does not become active in an account session after it is assigned to the account or named in the value of the mandatory? Roles system variable.
To specify that roles are activated each time a user connects to the server for authentication, use SET DEFAULT ROLE:
root@localhost:mysql.sock 21:04: [(none)]>set default role all to 'db_dev_user'@'localhost', 'db_rw_user'@'localhost', 'db_read_user'@'localhost'; Query OK, 0 rows affected (0.06 sec)
After that, log in to the server with app? Dev? User to view the current role permissions:
[root@localhost ~]# mysql -udb_rw_user -p'passjw03' db_rw_user@localhost:mysql.sock 21:11: [(none)]>show databases; +--------------------+ | Database | +--------------------+ | app_db | | information_schema | +--------------------+ db_rw_user@localhost:mysql.sock 21:11: [(none)]>select CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | `db_rw`@`%` | +----------------+ 1 row in set (0.00 sec)
To view the currently logged in user:
db_rw_user@localhost:mysql.sock 10:39: [(none)]>select current_user(); +----------------------+ | current_user() | +----------------------+ | db_rw_user@localhost | +----------------------+ 1 row in set (0.00 sec) db_rw_user@localhost:mysql.sock 10:39: [(none)]>select current_user; +----------------------+ | current_user | +----------------------+ | db_rw_user@localhost | +----------------------+ 1 row in set (0.00 sec)
To automatically activate all explicitly assigned and enforced roles when a user connects to the server, enable the activate all roles on login system variable. Automatic character activation is disabled by default.
Here's the introduction of roles in MySQL. For more details, please visit the official website manual
https://dev.mysql.com/doc/refman/8.0/en/roles.html