轻松上手,快乐学习!

MySQL 教程

MySQL 首页MySQL 介绍MySQL 安装MySQL 实例库下载MySQL 实例库导入MySQL SELECTMySQL DISTINCTMySQL ORDER BYMySQL WHEREMySQL ANDMySQL ORMySQL INMySQL BETWEENMySQL LIKEMySQL LIMITMySQL IS NULLMySQL 别名MySQL JOINSMySQL INNER JOINMySQL LEFT JOINMySQL RIGHT JOINMySQL CROSS JOINMySQL SELF JOINMySQL GROUP BYMySQL HAVINGMySQL ROLLUPMySQL 子查询MySQL 派生表MySQL EXISTSMySQL CTEMySQL 递归 CTEMySQL UNIONMySQL INTERSECTMySQL MINUSMySQL INSERTMySQL INSERT INTO SELECTMySQL INSERT IGNOREMySQL UPDATEMySQL UPDATE JOINMySQL DELETEMySQL ON DELETE CASCADEMySQL DELETE JOINMySQL REPLACEMySQL PREPAREMySQL 事务MySQL 表锁定MySQL USEMySQL 数据库管理MySQL CREATE DATABASEMySQL DROP DATABASEMySQL 存储引擎MySQL CREATE TABLEMySQL 序列MySQL ALTER TABLEMySQL RENAME TABLEMySQL DROP COLUMNMySQL ADD COLUMNMySQL DROP TABLEMySQL 临时表MySQL TRUNCATE TABLEMySQL 数据类型MySQL NOT NULLMySQL Primary KeyMySQL Foreign KeyMySQL UNIQUEMySQL CHECKMySQL 字符集MySQL 排序规则MySQL 导入 CSVMySQL 导出 CSVMySQL 自然排序MySQL 基础

MySQL 存储过程

MySQL 存储过程介绍MySQL 存储过程实例MySQL 存储过程变量MySQL 存储过程参数MySQL 存储过程返回多值MySQL IF 语句MySQL CASE 语句MySQL IF CASE 选择MySQL 存储过程循环MySQL 存储过程游标MySQL 存储过程列表MySQL 存储过程异常处理MySQL SIGNAL 和 RESIGNALMySQL 存储函数

MySQL 视图

MySQL 视图教程SQL 视图介绍MySQL 视图介绍MySQL 创建视图MySQL 可更新视图WITH CHECK OPTIONLOCAL&CASCADEDMySQL 视图管理

MySQL 触发器

MySQL 触发器SQL 触发器MySQL 触发器介绍MySQL 触发器的创建MySQL 创建多个触发器MySQL 触发器管理MySQL 计划事件MySQL事件修改

MySQL 索引

MySQL 索引MySQL 索引创建MySQL 索引删除MySQL 显示索引MySQL 唯一索引MySQL 前缀索引MySQL 隐形索引MySQL 降序索引MySQL 复合索引MySQL 聚集索引MySQL 索引基数MySQL USE INDEXMySQL 强制索引

MySQL 管理

MySQL 管理MySQL访问控制系统MySQL 用户创建MySQL 用户密码MySQL 权限授予MySQL 权限撤销MySQL 角色MySQL 删除用户MySQL 表维护mysqldump 备份工具MySQL 数据库列表MySQL 表列表MySQL 表字段列表MySQL 用户列表MySQL 进程列表MySQL 列生成比较MySQL中同一表中的连续行

MySQL 全文搜索

MySQL 全文搜索MySQL 全文搜索介绍FULLTEXT索引MySQL 自然语言全文搜索MySQL 布尔全文搜索MySQL查询扩展MySQL ngram

MySQL 高级

MySQL 函数MySQL 窗口函数

MySQL 技巧

MySQL 查找重复数据MySQL 删除重复数据MySQL UUIDMySQL 表的复制MySQL 复制库MySQL 变量MySQL SELECT INTO 变量MySQL 表的存储引擎MySQL 使用正则查询MySQL 添加序号MySQL 随机查询MySQL 查询第 N 高记录MySQL 重置自增值MySQL VS MariaDBMySQL 间隔值MySQL 获取当天日期MySQL NULL 映射MySQL 注释理解MySQL EXPLAINMySQL 技巧介绍MySQL COUNTMySQL 数据分层MySQL 两表比较Mysql Like 优化

MySQL 窗口函数

MySQL CUME_DIST() 函数MySQL DENSE_RANK() 函数MySQL FIRST_VALUE 函数MySQL LAG() 函数MySQL LAST_VALUE() 函数MySQL LEAD 函数MySQL NTH_VALUE 函数MySQL NTILE 函数MySQL PERCENT_RANK 函数MySQL RANK 函数MySQL ROW_NUMBER 函数

MySQL 应用

MySQL应用程序编程接口PHP MySQL教程PHP 数据库连接

MySQL 优化

mysqld 内存持续变高


MySQL 角色


简介:在本教程中,您将学习如何使用MySQL角色来简化权限管理。


MySQL角色简介

通常,您有许多具有相同权限集的用户。以前,授予撤销多个用户权限的唯一方法是单独更改每个用户的权限,这非常耗时。

为了简化,MySQL提供了一个名为role的新对象,它是一个命名的特权集合。

如果要为多个用户授予相同的权限集,则应按如下方式执行:

  • 首先,创建一个新角色。
  • 其次,授予角色特权。
  • 第三,将角色授予用户。

如果要更改用户的权限,则只需更改已授予角色的权限。更改将对角色授予的所有用户生效。


MySQL角色示例

首先,创建一个名为CRM 的新数据库,代表客户关系管理。

CREATE DATABASE crm;

接下来,切换到crm数据库:

USE crm;

然后,在CRM数据库中创建customer表。

CREATE TABLE customer(
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name varchar(255) NOT NULL, 
    last_name VARCHAR(255) NOT NULL, 
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(255)
);

之后,将数据插入customer表中。

INSERT INTO customer(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-987-7654','john.doe@begtut.com'),
      ('Lily','Bush','(408)-987-7985','lily.bush@begtut.com');

最后,使用以下SELECT语句验证插入:

mysql> SELECT * FROM customer;
+----+------------+-----------+----------------+----------------------+
| id | first_name | last_name | phone          | email                |
+----+------------+-----------+----------------+----------------------+
|  1 | John       | Doe       | (408)-987-7654 | john.doe@begtut.com  |
|  2 | Lily       | Bush      | (408)-987-7985 | lily.bush@begtut.com |
+----+------------+-----------+----------------+----------------------+
2 rows in set (0.07 sec)

创造角色

假设您开发了一个使用CRM数据库的应用程序。要与CRM数据库交互,您需要为需要完全访问数据库的开发人员创建帐户。此外,您需要为仅需要读取权限的用户和需要同时具有读/写访问权限的其他用户创建帐户。

为避免单独授予每个用户帐户的权限,您需要创建一组角色并为每个用户帐户授予相应的角色。

要创建新角色,请使用CREATE ROLE语句:

CREATE ROLE crm_dev, crm_read, crm_write;

角色名称类似于由用户和主机部分组成的用户帐户:role_name@host_name

如果省略主机部分,则默认为'%'表示任何主机。

授予角色权限

要为角色​​授予权限,请使用GRANT语句。以下语句授予crm_dev角色所有权限:

GRANT ALL ON crm.* TO crm_dev;

以下语句授予角色SELECT权限crm_read

GRANT SELECT ON crm.* TO crm_read;

下面的语句补助INSERTUPDATE以及DELETE权限的crm_write角色:

GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write;

将角色分配给用户帐户

假设您需要一个用户帐户作为开发人员,一个可以具有只读访问权限的用户帐户和两个可以具有读/写访问权限的用户帐户。

要创建新用户,请使用以下CREATE USER语句:

-- developer user 
CREATE USER crm_dev1@localhost IDENTIFIED BY 'Secure$1782';
-- read access user
CREATE USER crm_read1@localhost IDENTIFIED BY 'Secure$5432';    
-- read/write users
CREATE USER crm_write1@localhost IDENTIFIED BY 'Secure$9075';   
CREATE USER crm_write2@localhost IDENTIFIED BY 'Secure$3452';

要为用户分配角色,请使用以下GRANT语句:

GRANT crm_dev TO crm_dev1@localhost;
 
GRANT crm_read TO crm_read1@localhost;
 
GRANT crm_read, crm_write TO crm_write1@localhost, crm_write2@localhost;

请注意,crm_write1 @ localhostcrm_write2 @ localhost帐户的GRANT语句同时授予crm_readcrm_write角色。

要验证角色分配,请使用以下SHOW GRANTS语句作为以下示例:

SHOW GRANTS FOR crm_dev1@localhost;

语句返回以下结果集:

+-----------------------------------------------+
| Grants for crm_dev1@localhost                 |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `crm_dev1`@`localhost`  |
| GRANT `crm_dev`@`%` TO `crm_dev1`@`localhost` |
+-----------------------------------------------+
2 rows in set (0.24 sec)

如您所见,它只返回授予的角色。要显示角色所代表的权限,请使用USING带有已授予角色名称的子句,如下所示:

SHOW GRANTS FOR crm_write1@localhost USING crm_write;

语句返回以下输出:

+---------------------------------------------------------------------+
| Grants for crm_write1@localhost                                     |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `crm_write1`@`localhost`                      |
| GRANT INSERT, UPDATE, DELETE ON `crm`.* TO `crm_write1`@`localhost` |
| GRANT `crm_read`@`%`,`crm_write`@`%` TO `crm_write1`@`localhost`    |
+---------------------------------------------------------------------+
3 rows in set (0.10 sec)

设置默认角色

现在,如果您使用crm_read1用户帐户连接到MySQL 并尝试访问CRM数据库:

>mysql -u crm_read1 -p
Enter password: ***********
mysql>USE crm;

声明发出以下错误消息:

ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm'

这是因为当您向用户帐户授予角色时,当用户帐户连接到数据库服务器时,它不会自动使角色变为活动状态。

如果您调用CURRENT_ROLE()函数:

mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.02 sec)

它返回了NONE,意味着没有活跃的角色。

要在每次用户帐户连接到数据库服务器时指定应激活哪些角色,请使用SET DEFAULT ROLE语句。

以下语句为crm_read1@localhost帐户的所有已分配角色设置默认值。

SET DEFAULT ROLE ALL TO crm_read1@localhost;

现在,如果使用crm_read1用户帐户连接到MySQL数据库服务器并调用CURRENT_ROLE()函数:

>mysql -u crm_read1 -p
Enter password: ***********
>SELECT CURRENT_ROLE();

您将看到crm_read1用户帐户的默认角色。

+----------------+
| current_role() |
+----------------+
| `crm_read`@`%` |
+----------------+
1 row in set (0.00 sec)

您可以crm_read通过将当前数据库切换为CRM,执行SELECT语句和DELETE语句来测试帐户的权限,如下所示:

mysql> use crm;
Database changed
mysql> SELECT COUNT(*) FROM customer;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
 
mysql> DELETE FROM customer;
ERROR 1142 (42000): DELETE command denied to user 'crm_read1'@'localhost' for table 'customer'

它按预期工作。当我们发布DELETE语句时,我们收到错误,因为crm_read1用户帐户只有读访问权限。

设置活动角色

用户帐户可以通过指定哪个已授予的角色处于活动状态来修改当前会话中当前用户的有效权限。

以下语句将活动角色设置为NONE,表示没有活动角色。

SET ROLE NONE;

要将活动角色设置为所有已授予角色,请使用:

SET ROLE ALL;

要将活动角色设置为由SET DEFAULT ROLE语句设置的默认角色,请使用:

SET ROLE DEFAULT;

要设置活动的命名角色,请使用:

SET ROLE granted_role_1, granted_role_2, ...

撤消角色的权限

要撤消特定角色的权限,请使用REVOKE语句。REVOKE声明不仅影响角色,还影响授予角色的任何帐户。

例如,要临时使所有读/写用户只读,您可以crm_write按如下方式更改角色:

REVOKE INSERT, UPDATE, DELETE ON crm.* FROM crm_write;

要恢复权限,您需要按如下方式重新授予权限:

GRANT INSERT, UPDATE, DELETE ON crm.* FOR crm_write;

删除角色

要删除一个或多个角色,请使用DROP ROLE语句,如下所示:

DROP ROLE role_name, role_name, ...;

与REVOKE语句一样,DROP ROLE语句撤销了授予它的每个用户帐户的角色。

例如,要删除crm_readcrm_write角色,可以使用如下语句:

DROP ROLE crm_read, crm_write;

将权限从用户帐户复制到另一个帐户

MySQL将用户帐户视为角色,因此,您可以将用户帐户授予其他用户帐户,例如向用户帐户授予角色。这允许您将用户的权限复制到另一个用户。

假设您需要另一个CRM数据库开发者帐户:

首先,创建新的用户帐户:

CREATE USER crm_dev2@localhost IDENTIFIED BY 'Secure$6275';

其次,将crm_dev1用户帐户的权限复制到crm_dev2用户帐户,如下所示:

GRANT crm_dev1@localhost TO crm_dev2@localhost;

在本教程中,您学习了如何使用MySQL角色来更轻松地管理用户帐户的权限。