\u5f3a\u5236\u89d2\u8272\uff1aMandatory roles
\n\u53ef\u4ee5\u6307\u5b9a\u5f3a\u5236\u6027\u7684\u89d2\u8272\u4f5c\u4e3amandatory_roles\u7cfb\u7edf\u53d8\u91cf\u7684\u503c\u3002\u670d\u52a1\u5668\u5c06\u4e00\u4e2a\u5f3a\u5236\u6027\u7684\u89d2\u8272\u6388\u4e88\u6240\u6709\u7528\u6237,\u6240\u4ee5\u5b83\u4e0d\u9700\u8981\u660e\u786e\u6388\u4e88\u4efb\u4f55\u5e10\u6237\u3002
\n[mysqld]
\nmandatory_roles=\u2019role1,role2@localhost,r3@%.example.com\u2019
\n\u6216\u8005
\nSET PERSIST mandatory_roles = \u2018role1,role2@localhost,r3@%.example.com\u2019;
\n\u4e0d\u8fc7\u9700\u8981\u6ce8\u610f\u7684\u662f\uff1a
\nAs of MySQL 8.0.4, setting mandatory_roles requires the ROLE_ADMIN privilege, in addition to the SYSTEM_VARIABLES_ADMIN or SUPER privilege normally required to set a global system variable.
\nRoles named in the value of mandatory_roles cannot be revoked with REVOKE or dropped with DROP ROLE or DROP USER.
\nSET PERSIST sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, \u201cSET Syntax for Variable Assignment\u201d. To change a value only for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.<\/p>\n
\u6211\u4eec\u505a\u4e00\u4e2a\u793a\u8303\uff1a<\/p>\n
\u9996\u5148\u5efa\u4e00\u4e2a\u89d2\u8272\u5e76\u6388\u6743\uff1a
\nCREATE ROLE \u2018app_developer\u2019;
\nGRANT ALL ON test.* TO \u2018app_developer\u2019;<\/p>\n
\u7136\u540e\u6211\u4eec\u628a\u8fd9\u4e2a\u53c2\u6570\u8bbe\u6210\u8fd9\u4e2a\u89d2\u8272\uff1a
\nSET PERSIST mandatory_roles = \u2018app_developer@%\u2019;<\/p>\n
\u7136\u540e\u6211\u4eec\u521b\u5efa\u4e00\u4e2a\u7528\u6237 \u5e76\u4e0d\u8d4b\u6743
\ncreate user \u2018test2\u2019@\u2019%\u2019 identified by \u2018test2\u2019;
\nFLUSH PRIVILEGES;<\/p>\n
\u6211\u4eec\u5728\u5176\u5b83\u7684\u7a97\u53e3\u4e0a\u7528\u65b0\u52a0\u7684\u8fd9\u4e2a\u5e10\u53f7\u8fdb\u53bb\u3002\u7136\u540e\u53ef\u4ee5\u76f4\u63a5set\u8fd9\u4e2arole
\ntest2@information_schema 01:15:07>set role app_developer;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n
\u4e5f\u53ef\u4ee5\u8fdb\u884c\u67e5\u8be2
\ntest2@information_schema 01:15:15>show databases;
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n| Database |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n| information_schema |
\n| test |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n2 rows in set (0.00 sec)<\/p>\n
test2@information_schema 01:15:19>use test;
\nDatabase changed
\ntest2@test 01:15:23>select * from test.a;
\n+\u2014\u2014+
\n| id |
\n+\u2014\u2014+
\n| 1 |
\n+\u2014\u2014+
\n1 row in set (0.01 sec)<\/p>\n
\u7136\u540e\u5982\u679c\u6211\u4eec\u628a\u8fd9\u4e2a\u53c2\u6570\u6539\u6210\u7a7a\u7684\uff0c\u76f8\u5e94\u7684\u6743\u9650\u4e5f\u5c31\u6ca1\u6709\u4e86\u3002 \u8fd9\u4e2a\u4e0d\u7528\u9000\u51fa\u4f1a\u8bdd\u5c31\u4f1a\u751f\u6548
\ntest2@(none) 01:17:03>set role app_developer;
\nERROR 3530 (HY000): `app_developer`@`%` is not granted to `test2`@`%`<\/p>\n
\u9700\u8981\u6ce8\u610f\u7684\u662f \u5f53\u4e00\u4e2a\u89d2\u8272\u88abset\u4e86\u4e4b\u540e\u662f\u4e0d\u53ef\u4ee5\u88ab\u5220\u9664\u7684
\ndbadmin@(none) 01:20:25>drop role app_developer;
\nERROR 4527 (HY000): The role `app_developer`@`%` is a mandatory role and can\u2019t be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.<\/p>\n","protected":false},"excerpt":{"rendered":"
\u5f3a\u5236\u89d2\u8272\uff1aMandatory roles \u53ef\u4ee5\u6307\u5b9a\u5f3a\u5236\u6027\u7684\u89d2\u8272\u4f5c\u4e3amandatory_roles\u7cfb\u7edf\u53d8\u91cf\u7684\u503c\u3002 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[],"class_list":["post-1365","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1365","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1365"}],"version-history":[{"count":1,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1365\/revisions"}],"predecessor-version":[{"id":1366,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1365\/revisions\/1366"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1365"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1365"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1365"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}