Shulou(Shulou.com)06/03 Report--

Role function is nothing special for Oracle database, but for MySQL, the role function added in MySQL 8.0 is quite novel, and it is also what many users of MySQL service have been looking forward to.

The role function added in MySQL 8.0 allows DBAs to create roles, grant them permissions, 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 a detailed description of how roles are used in MySQL:

1. Create and delete roles:

Creating Roles Using the CREATE ROLE statement, suppose we create read-only roles, read-write roles, and developer roles for our 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 MySQL account name, consisting of username + hostname, which defaults to '%' if the hostname is omitted.

2. Delete role usage: root@localhost:mysql.sock 20:39: [(none)]>drop role db_dev;Query OK, 0 rows affected (0.08 sec) 3. Grant/revoke permissions:

After a role is created, it needs to be given appropriate permissions. Give roles full, read-only, and read-write permissions, respectively, using the following statements:

All authorities:

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 and write permissions:

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 granting role permissions, grant the corresponding role 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. Revoke user roles and permissions to revoke roles: 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)]> VI. View role permissions:

When 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)

Notice that executing the show grants statement simply sees that the user has been assigned a role, and what permissions does that role have? The following statement confirms the relevant 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` |+--------------------------------------------------------------------------------

MySQL can configure mandatory roles through the mandatory_roles variable. With mandatory roles, the server assigns this role by default to all accounts,

without the need to display the execution assigned role. It can be configured using the my.cnf file or using SET PERIST, for example:

[mysqld]mandatory_roles='role1,role2@localhost'SET PERSIST mandatory_roles = 'role1,role2@localhost;

Note that roles configured in mandatory_roles cannot be revoked or deleted.

Eight, the role automatically activated:

Roles assigned to user accounts can be active or inactive in account sessions. If the assigned role is active in the session,

They have the right to do so, and vice versa. 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, when an account is assigned a role or named in the mandatory_roles system variable value, the role does not become active in the account session.

To specify that a role is 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)

View current login users:

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 have all explicitly assigned and mandatory roles automatically activate when a user connects to the server, enable the activate_all_roles_on_login system variable. Automatic role activation is disabled by default.

About MySQL role is introduced here, more detailed content please visit the official website manual

https://dev.mysql.com/doc/refman/8.0/en/roles.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.