Detailed explanation of examples of mysql database roles

This article brings you relevant knowledge aboutmysql, which mainly introduces the relevant content about database roles. You can grant permissions to users through roles to facilitate the management of users with the same permissions. Users, let’s take a look below, I hope it will be helpful to everyone.

Recommended learning:mysql video tutorial

Mysql database permissions management, simply give it to a certain user directly Add permissions. One troublesome thing about doing this is that when we have a lot of users, if these users all have the same permissions, the operation will be a bit redundant. Then we can grant permissions to users through roles.

Understanding of roles

The purpose of introducing roles is to facilitate the management of users with the same permissions.Appropriate permission settings can ensure data security, which is crucial

Create a role

Create a role using the CREATE ROLE statement, the syntax is as follows:

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

Naming rules for role names Similar to username. If host_name is omitted, it defaults to %. role_name cannot be omitted and cannot be empty.

Exercise: Now we need to create a manager role, we can use the following code:

CREATE ROLE 'manager'@'localhost';

The effect is as shown below

Give permissions to the role

After creating a role, the default role is If there is no permission, we need to authorize the role. The syntax structure for granting role authorization is:

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

Example: Grant the manager role permission to view all tables under the dbtest1 database

View role permissions

As long as you create a role, the system will automatically give you a "USAGE" permission, which means the permission to connect to the database

Reclaim the role's permissions

#After the role is authorized, you can maintain the role's permissions and add or revoke the permissions. Add permissions using the GRANT statement, which is the same as role authorization. To revoke a role or role permissions use the REVOKE statement.

Modifying the permissions of a role will affect the permissions of the account that owns the role.

The SQL syntax for revoking role permissions is as follows

REVOKE privileges ON tablename FROM 'rolename';

Now we add a delete permission to the manager, and then take back this permission

##Delete role

When we need to reintegrate the business Sometimes, you may need to clean up previously created roles and delete some roles that will no longer be used. The operation of deleting a character is very simple, you only need to master the grammatical structure.

DROP ROLE role [,role2]...

Note that if you delete a role, the user will lose all permissions obtained through this role.

给用户赋予角色

角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。给用户添加角色可使用GRANT语句，语 法形式如下：

GRANT role [,role2,...] TO user [,user2,...];

在上述语句中，role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。

例子：创建一个用户叫wang5 然后赋予角色manager，操作如下

用wang5登录并操作

赋予wang5角色manager 注意这个是通过root用户实现的

然后通过wang5登录查看数据库

此时还是看不到dbtest1，这是怎么回事呢？原来我们需要激活角色

激活角色

方式1：使用set default role 命令激活角色

SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

现在激活manager角色

然后再通过wang5用户登录查看

激活方式2：将activate_all_roles_on_login设置为ON

默认情况：

设置 SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有 权限。

那么现在wang5已经被赋予manager角色，我们知道manager角色只是有select的权限。我们做如下操作

撤销用户的角色

REVOKE role FROM user;

比如撤销 wang5的manager角色，通过root用户

再通过wang5登录看效果

推荐学习：mysql视频教程

The above is the detailed content of Detailed explanation of examples of mysql database roles. For more information, please follow other related articles on the PHP Chinese website!