MySQL角色是一个命名的特权集合。与用户帐户一样,角色也可以拥有授予或撤销的特权。
可以为用户帐户授予角色,这将为帐户授予与每个角色相关联的特权。这样就可以将特权集分配给帐户,并提供了一种方便的方法来授予各个特权,既可以概念化所需的特权分配,也可以实现它们。
下面的列表总结了MySQL提供的角色管理功能:
秀奖助金
显示用户帐户和角色的权限和角色分配。设置默认角色
指定默认情况下激活的帐户角色。设置角色
更改当前会话中的活动角色。的
CURRENT_ROLE ()
命令功能显示当前会话中的活动角色。的
mandatory_roles
而且activate_all_roles_on_login
系统变量支持定义强制角色和在用户登录到服务器时自动激活授予的角色。
有关各个角色操作语句(包括使用它们所需的特权)的描述,请参见账户管理报表.下面的讨论提供了角色使用的示例。除非另有指定,此处显示的SQL语句应该使用具有足够管理权限的MySQL帐户执行,例如根
帐户。
考虑一下这个场景:
应用程序使用名为
app_db
.与应用程序相关联的是,可以为创建和维护应用程序的开发人员和与应用程序交互的用户创建帐户。
开发人员需要完全访问数据库。有些用户只需要读访问,有些用户需要读/写访问。
要避免单独向可能很多用户帐户授予特权,请创建角色作为所需特权集的名称。通过授予适当的角色,可以很容易地将所需的特权授予用户帐户。
要创建角色,请使用创建角色
声明:
创建角色:app_developer, app_read, app_write
角色名很像用户帐户名,由用户部分和主机部分组成'
格式。如果省略主机部分,默认为user_name
“@”host_name
'“%”
.用户和主机部分可以不加引号,除非它们包含特殊字符,例如-
或%
.与帐号名不同,角色名中的用户部分不能为空。有关更多信息,请参见第4.5节,“指定角色名”.
要为角色分配特权,请执行格兰特
语句使用与为用户帐户分配特权相同的语法:
在app_db上授予所有权限。*“app_developer”;在app_db上授予SELECT权限。*“app_read”;赋予app_db的插入、更新、删除权限。*“app_write”;
现在假设您最初需要一个开发人员帐户、两个需要只读访问权限的用户帐户和一个需要读写访问权限的用户帐户。使用创建用户
创建帐户:
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass'创建用户read_user1 @ localhost IDENTIFIED BY read_user1pass创建用户read_user2 @ localhost IDENTIFIED BY read_user2pass创建用户rw_user1 @ localhost IDENTIFIED BY rw_user1pass
要为每个用户帐户分配其所需的特权,您可以使用格兰特
与刚才显示的相同形式的语句,但这需要列举每个用户的个别特权。相反,使用替代方法格兰特
允许授予角色而不是特权的语法:
GRANT 'app_developer' TO 'dev1'@'localhost';GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';GRANT 'app_read', 'app_write'到'rw_user1'@'localhost';
的格兰特
声明的rw_user1
Account授予读和写角色,它们结合起来提供所需的读和写权限。
的格兰特
向帐户授予角色的语法与授予特权的语法不同:有一个在
子句分配特权,而没有在
子句来分配角色。因为语法是不同的,所以不能在同一个语句中混合分配特权和角色。(允许为一个帐户分配特权和角色,但您必须单独使用格兰特
语句,每个语句的语法都与被授予的内容相适应。)在MySQL 8.0.16版本中,角色不能被授予匿名用户。
角色创建时被锁定,没有密码,并被分配默认的身份验证插件。这些角色属性可以在以后用改变用户
语句,由具有全局的用户创建用户
特权。)
锁定后,不能使用角色对服务器进行身份验证。如果未锁定,则可以使用角色进行身份验证。这是因为角色和用户都是授权标识符,它们有很多共同点,但几乎没有区别。另请参阅用户和角色互换性.
的值中将角色命名为强制角色是可能的mandatory_roles
系统变量。服务器将强制角色视为授予所有用户,因此不需要显式地授予任何帐户。
要在服务器启动时指定强制角色,请定义mandatory_roles
在你的服务器my.cnf
文件:
(mysqld) mandatory_roles = ' role1的角色,role2@localhost r3@ % .example.com '
设置和坚持mandatory_roles
在运行时,使用这样的语句:
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';
设置保存
设置运行的MySQL实例的值。它还保存该值,使其保留到后续的服务器重启。要更改正在运行的MySQL实例的值,而不将其带入后续重启,请使用全球
关键字,而不是坚持
.看到SET变量赋值语法.
设置mandatory_roles
要求ROLE_ADMIN
特权,除了SYSTEM_VARIABLES_ADMIN
特权(或已弃用的超级
通常需要设置一个全局系统变量。
与显式授予的角色一样,强制角色直到激活才生效(请参见激活的角色).在登录时,所有被授予的角色都会被激活,如果activate_all_roles_on_login
启用系统变量,否则将角色设置为默认角色。在运行时,设置角色
激活的角色。
的值中命名的角色mandatory_roles
不能用撤销
或下降下降的作用
或减少用户
.
为了防止会话在默认情况下成为系统会话,角色必须具有SYSTEM_USER
的值中不能列出特权mandatory_roles
系统变量:
如果
mandatory_roles
是否在启动时分配了一个具有SYSTEM_USER
特权时,服务器将一条消息写入错误日志并退出。如果
mandatory_roles
在运行时分配的角色具有SYSTEM_USER
特权时,错误发生mandatory_roles
值保持不变。
如果角色名为mandatory_roles
不是存在于mysql.user
系统表中,该角色未被授予用户。当服务器尝试为用户激活角色时,它不会将不存在的角色视为强制的,并将警告写入错误日志。如果稍后创建角色并因此变得有效,冲洗的特权
可能是必要的,以便使服务器将其视为强制的。
要验证分配给帐户的特权,请使用秀奖助金
.例如:
mysql> SHOW GRANTS FOR 'dev1'@'localhost'+-------------------------------------------------+ | 资助dev1@localhost | +-------------------------------------------------+ | 格兰特使用*。*“dev1”@“localhost”| |授予“app_developer”@“%”“dev1”@ localhost ` | +-------------------------------------------------+
然而,这说明每个角色都没有被赋予”扩大”它指向角色所代表的特权。若要显示角色权限,请添加使用
子句命名被授予的角色显示特权:
mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';+----------------------------------------------------------+ | 资助dev1@localhost | +----------------------------------------------------------+ | 格兰特使用*。给' dev1 ' @ ' localhost ' | |授予' app_db '上的所有特权。*“dev1”@“localhost”| |授予“app_developer”@“%”“dev1”@ localhost ` | +----------------------------------------------------------+
以类似方式验证其他类型的用户:
mysql> SHOW grant FOR 'read_user1'@'localhost' USING 'app_read';+--------------------------------------------------------+ | 资助read_user1@localhost | +--------------------------------------------------------+ | 格兰特使用*。* TO ' read_user1 ' @ ' localhost ' | | GRANT SELECT ON ' app_db '。*“read_user1”@“localhost”| |授予“app_read”@“%”“read_user1”@ localhost ` | +--------------------------------------------------------+ mysql >显示授予“rw_user1”@“localhost”使用“app_read”、“app_write”;+------------------------------------------------------------------------------+ | 资助rw_user1@localhost | +------------------------------------------------------------------------------+ | 格兰特使用*。* TO ' rw_user1 ' @ ' localhost ' | | GRANT对' app_db '进行SELECT、INSERT、UPDATE、DELETE操作。*“rw_user1”@“localhost”| |授予“app_read”@“%”,“app_write”@“%”“rw_user1”@ localhost ` | +------------------------------------------------------------------------------+
授予用户帐户的角色在帐户会话中可以是活动的,也可以是不活动的。如果被授予的角色在会话中是活动的,则应用它的特权;否则,它们不会。要确定当前会话中哪些角色是活动的,请使用CURRENT_ROLE ()
函数。
默认情况下,将角色授予帐户或在mandatory_roles
系统变量值不会自动使角色在帐户会话内变为活动状态。例如,因为到目前为止在前面的讨论中没有rw_user1
如果将服务器连接为。角色已经激活rw_user1
并调用CURRENT_ROLE ()
函数,结果为没有一个
(没有发挥积极作用):
mysql >选择CURRENT_ROLE ();+----------------+ | CURRENT_ROLE () | +----------------+ | 没有一个 | +----------------+
要指定每当用户连接到服务器并进行身份验证时,哪些角色应该成为活动角色,请使用设置默认角色
.要为前面创建的每个帐户设置默认的所有分配角色,使用以下语句:
设置默认角色ALL为'dev1'@'localhost', 'read_user1'@'localhost', 'read_user2'@'localhost', 'rw_user1'@'localhost';
如果你连接成rw_user1
的初始值CURRENT_ROLE ()
反映新的默认角色分配:
mysql >选择CURRENT_ROLE ();+--------------------------------+ | CURRENT_ROLE () | +--------------------------------+ | ` app_read ' @ ' % ', ' app_write `@`%` | +--------------------------------+
要使所有显式授予和强制的角色在用户连接到服务器时自动激活,请启用activate_all_roles_on_login
系统变量。缺省情况下,角色自动激活功能处于关闭状态。
在会话中,用户可以执行设置角色
更改活动角色的集合。例如,对于rw_user1
:
mysql> SET ROLE NONE选择CURRENT_ROLE ();+----------------+ | CURRENT_ROLE () | +----------------+ | 没有一个 | +----------------+ mysql >设置角色除了“app_write”;选择CURRENT_ROLE ();+----------------+ | CURRENT_ROLE () | +----------------+ | ` app_read `@`%` | +----------------+ mysql >设置角色默认;选择CURRENT_ROLE ();+--------------------------------+ | CURRENT_ROLE () | +--------------------------------+ | ` app_read ' @ ' % ', ' app_write `@`%` | +--------------------------------+
第一个设置角色
语句禁用所有角色。第二个让rw_user1
有效的阅读。第三个选项恢复默认角色。
存储的程序和视图对象的有效用户受定义者
而且SQL安全
属性,该属性确定执行是在调用程序上下文中发生还是在定义程序上下文中发生(请参见存储对象访问控制):
在调用程序上下文中执行的存储程序和视图对象与当前会话中活动的角色一起执行。
在definer上下文中执行的存储程序和视图对象使用它们中命名的用户的默认角色执行
定义者
属性。如果activate_all_roles_on_login
时,这些对象在执行时将所有角色授予定义者
用户,包括必选角色。对于存储程序,如果执行的角色与默认的不同,则应该执行程序体设置角色
激活所需的角色。
就像可以将角色授予帐户一样,它们也可以从帐户中撤销:
撤销角色从用户;
中命名的角色mandatory_roles
不能撤销系统变量值。
撤销
也可以应用于角色来修改授予它的权限。这不仅会影响角色本身,还会影响授予该角色的任何帐户。假设您希望暂时将所有应用程序用户设置为只读。要做到这一点,使用撤销
的修改权限app_write
角色:
撤销对app_db的INSERT、UPDATE、DELETE操作。*“app_write”;
实际上,这使得角色没有任何特权,可以看到使用秀奖助金
(这表明该语句可以用于角色,而不仅仅是用户):
mysql> SHOW GRANTS FOR app_write+---------------------------------------+ | 资助app_write @% | +---------------------------------------+ | 格兰特使用*。*“app_write `@`%` | +---------------------------------------+
因为从角色撤销权限会影响分配给修改角色的任何用户的权限,rw_user1
现在没有表修改权限(插入
,更新
,删除
不再存在):
mysql> SHOW grant FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write'+----------------------------------------------------------------+ | 资助rw_user1@localhost | +----------------------------------------------------------------+ | 格兰特使用*。* TO ' rw_user1 ' @ ' localhost ' | | GRANT SELECT ON ' app_db '。*“rw_user1”@“localhost”| |授予“app_read”@“%”,“app_write”@“%”“rw_user1”@ localhost ` | +----------------------------------------------------------------+
实际上,rw_user1
读写用户变为只读用户。的任何其他帐户也会发生这种情况app_write
角色,说明了如何使用角色使得不需要修改个人帐户的特权。
要恢复角色的修改权限,只需重新授予它们:
赋予app_db的插入、更新、删除权限。*“app_write”;
现在rw_user1
具有修改权限,其他帐户也具有修改权限app_write
的角色。
要删除角色,请使用下降的作用
:
DROP ROLE 'app_read', 'app_write'
删除角色将从授予该角色的每个帐户中撤销该角色。
中命名的角色mandatory_roles
不能删除系统变量值。
正如之前所暗示的秀奖助金
,它显示用户帐户或角色的授予,帐户和角色可以互换使用。
角色和用户之间的一个区别是创建角色
创建默认锁定的授权标识符,而创建用户
创建默认情况下未锁定的授权标识符。但是,这种区别不是不可变的,因为具有适当特权的用户可以在创建角色或用户之后锁定或解锁它们。
如果数据库管理员的首选项是特定的授权标识符必须是角色,那么可以使用名称方案来传达这一意图。例如,你可以用r_
用于指定为角色的所有授权标识符的前缀。
角色和用户的另一个区别在于它们的管理权限:
因此,创建角色
而且下降的作用
特权并不像创建用户
并且可以授予应该只允许创建和删除角色的用户,而不应该执行更一般的帐户操作。
关于用户和角色的特权和互换性,可以将用户帐户视为角色,并将该帐户授予另一个用户或角色。其效果是将帐户的特权和角色授予其他用户或角色。
这组语句演示了您可以将用户授予用户,将角色授予用户,将用户授予角色,或将角色授予角色:
创建用户‘u1’;创建角色“r1”;授予db1选择权限。*“u1”;在db2上授予SELECT权限。*“r1”;创建用户“u2”;创建角色的r2;GRANT 'u1', 'r1' TO 'u2';GRANT 'u1', 'r1'到'r2';
每种情况下的结果都是向被授对象授予与被授对象关联的特权。在执行这些语句之后,每个u2
而且r2
已被用户授予特权(u1
)和角色(r1
):
mysql> SHOW GRANTS FOR 'u2' USING 'u1', 'r1';+-------------------------------------+ | 授予对u2 @% | +-------------------------------------+ | 格兰特使用*。* TO ' u2 ' @ ' % ' | | GRANT SELECT ON ' db1 '。* TO ' u2 ' @ ' % ' | | GRANT SELECT ON ' db2 '。*“u2 `@`%` | | 授予“u1”@“%”,“r1”@“%”“u2 `@`%` | +-------------------------------------+ mysql >显示赠款的r2使用‘u1’,‘r1’;+-------------------------------------+ | r2的资助 @% | +-------------------------------------+ | 格兰特使用*。* TO ' r2 ' @ ' % ' | | GRANT SELECT ON ' db1 '。* TO ' r2 ' @ ' % ' | | GRANT SELECT ON ' db2 '。*“r2 `@`%` | | 授予“u1”@“%”,“r1”@“%”“r2 `@`%` | +-------------------------------------+
前面的例子只是说明,但是用户帐户和角色的互换性有实际的应用,例如下面的情况:假设一个遗留应用程序开发项目在MySQL中出现角色之前开始,因此所有与该项目相关的用户帐户都被直接授予特权(而不是通过被授予角色授予特权)。其中一个帐户是一个开发帐户,最初被授予如下特权:
创建用户old_app_dev @ localhost IDENTIFIED BY 'old_app_devpass';GRANT ALL ON old_app。*“old_app_dev”@“localhost”;
如果该开发人员离开项目,就有必要将权限分配给另一个用户,或者如果开发活动已经扩展,可能会分配给多个用户。以下是一些处理这个问题的方法:
不使用角色:更改帐户密码,使原来的开发人员不能使用,并让一个新的开发人员使用帐户:
ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password”;
使用角色:锁定帐户,防止任何人使用该帐户连接服务器:
ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK
然后将帐户视为一个角色。对于每个新加入项目的开发人员,创建一个新帐户,并授予其原来的开发人员帐户:
创建用户new_app_dev1 @ localhost IDENTIFIED BYnew_password”;GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';
其效果是将原来的开发人员帐户特权分配给新帐户。