{"id":1367,"date":"2017-12-27T17:02:24","date_gmt":"2017-12-27T09:02:24","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=1367"},"modified":"2018-01-09T17:02:47","modified_gmt":"2018-01-09T09:02:47","slug":"mysql8%e6%96%b0%e7%89%b9%e6%80%a7-roles","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=1367","title":{"rendered":"MySQL8\u65b0\u7279\u6027-Roles"},"content":{"rendered":"

\u4e00\u4e2aMySQL\u7684\u89d2\u8272\u662f\u4e00\u4e9b\u6743\u9650\u7684\u96c6\u5408\u3002\u5c31\u50cf\u4e00\u4e2a\u5e10\u53f7\u4e00\u6837\uff0c\u53ef\u4ee5\u5bf9\u4ed6\u8fdb\u884c\u6743\u9650\u7684\u8d4b\u6743\u548c\u6536\u56de\u3002
\n\u6211\u4eec\u53ef\u4ee5\u628a\u89d2\u8272\u6388\u6743\u7ed9\u7528\u6237\uff0c\u8fd9\u6837\u5e10\u53f7\u5c31\u62e5\u7528\u4e86\u89d2\u8272\u7684\u6743\u9650\u3002\u6211\u4eec\u53ef\u4ee5\u540c\u4e00\u4e2a\u89d2\u8272\u8d4b\u6743\u7ed9\u4e0d\u540c\u7684\u5e10\u53f7\uff0c\u4e5f\u53ef\u4ee5\u628a\u4e0d\u540c\u7684\u89d2\u8272\u7ed9\u540c\u4e00\u4e2a\u5e10\u53f7\u3002<\/p>\n

CREATE ROLE and DROP ROLE enable roles to be created and removed.
\nGRANT and REVOKE enable privilege assignment and revocation for user accounts and roles.
\nSHOW GRANTS displays privilege and role assignments for user accounts and roles.
\nSET DEFAULT ROLE specifies which account roles are active by default.
\nSET ROLE changes the active roles within the current session.
\nThe CURRENT_ROLE() function displays the active roles within the current session.
\nThe mandatory_roles and activate_all_roles_on_login system variables enable defining mandatory roles and automatic activation of granted roles when users log in to the server.<\/p>\n

\u521b\u5efa\u4e00\u4e2a\u89d2\u8272\uff1a
\nCREATE ROLE \u2018app_developer\u2019;
\n\u7ed9\u89d2\u8272\u8d4b\u6743\uff1a
\nGRANT ALL ON test.* TO \u2018app_developer\u2019;
\n\u628a\u89d2\u8272\u7684\u6743\u9650\u8d4b\u7ed9\u7528\u6237\uff1a
\n\u5982\u679c\u4ee5\u524d\u6211\u4eec\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528GRANT ALL ON test.* TO \u2018dev1\u2019@\u2019localhost\u2019 IDENTIFIED BY \u2018dev1pass\u2019;\u6765\u540c\u65f6\u5efa\u7528\u6237\u5e76\u8d4b\u6743\uff0c\u4f46\u662f\u73b0\u5728\u6211\u4eec\u4f7f\u7528\u4e0b\u9762\u7684\u547d\u4ee4\u5c06\u4f1a\u62a5\u9519
\nGRANT app_developer TO \u2018dev1\u2019@\u2019localhost\u2019 IDENTIFIED BY \u2018dev1pass\u2019;
\n\u6211\u4eec\u53ea\u80fd\u5206\u5f00\u6765\u64cd\u4f5c\uff1a
\ncreate user \u2018dev1\u2019@\u2019localhost\u2019 IDENTIFIED BY \u2018dev1pass\u2019;
\nGRANT app_developer TO \u2018dev1\u2019@\u2019localhost\u2019;<\/p>\n

\u8fd9\u4e2a\u65f6\u5019mysql\u7684role_edges\u8868\u91cc\u5c31\u4f1a\u591a\u4e00\u6761\u8bb0\u5f55
\nroot@mysql 03:44:38>select * from role_edges;
\n+\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014\u2013+\u2014\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014-+
\n| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
\n+\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014\u2013+\u2014\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014-+
\n| % | app_developer | localhost | dev1 | N |
\n+\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014\u2013+\u2014\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014-+
\n1 row in set (0.00 sec)<\/p>\n

\u6211\u4eec\u548c\u4ee5\u524d\u7684\u7248\u672c\u4e00\u6837\u6765\u67e5\u770b\u4e00\u4e0b\u7528\u6237\u7684\u6743\u9650\uff1a
\nroot@mysql 03:45:42>show grants for \u2018dev1\u2019@\u2019localhost\u2019;
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+
\n| Grants for dev1@localhost |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+
\n| GRANT USAGE ON *.* TO `dev1`@`localhost` |
\n| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+
\n2 rows in set (0.00 sec)<\/p>\n

root@mysql 03:45:53>show grants for \u2018dev1\u2019@\u2019localhost\u2019 using \u2018app_developer\u2019;
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n| Grants for dev1@localhost |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n| GRANT USAGE ON *.* TO `dev1`@`localhost` |
\n| GRANT ALL PRIVILEGES ON `test`.* TO `dev1`@`localhost` |
\n| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n3 rows in set (0.00 sec)<\/p>\n

\u8fd9\u4e2a\u65f6\u5019\u6211\u4eec\u4f7f\u7528dev1\u521a\u521a\u5efa\u7684\u7528\u6237\u767b\u4e00\u4e0b
\n$mysql -udev1 -pdev1pass<\/p>\n

dev1@(none) 04:02:33>show databases;
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n| Database |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n| information_schema |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
\n1 row in set (0.01 sec)<\/p>\n

dev1@(none) 04:02:39>SELECT CURRENT_ROLE();
\n+\u2014\u2014\u2014\u2014\u2014-+
\n| CURRENT_ROLE() |
\n+\u2014\u2014\u2014\u2014\u2014-+
\n| NONE |
\n+\u2014\u2014\u2014\u2014\u2014-+
\n1 row in set (0.00 sec)<\/p>\n

dev1@(none) 04:02:54>select * from test.a;
\nERROR 1142 (42000): SELECT command denied to user \u2018dev1\u2019@\u2019localhost\u2019 for table \u2018a\u2019<\/p>\n

\u53ef\u4ee5\u53d1\u73b0\u5e76\u67e5\u4e0d\u4e86\u3002
\n\u8fd9\u4e2a\u65f6\u5019\u6211\u4eecset\u4e00\u4e0b\u5f53\u524d\u7684\u89d2\u8272
\ndev1@(none) 04:04:33>set role app_developer;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

dev1@(none) 04:04:38>SELECT CURRENT_ROLE();
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014+
\n| CURRENT_ROLE() |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014+
\n| `app_developer`@`%` |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014+
\n1 row in set (0.00 sec)<\/p>\n

dev1@(none) 04:04:42>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

\u5f53\u6211\u4eecset\u4e86\u4ee5\u540e\u53ef\u4ee5\u4e86\u3002
\n\u5f53\u7136\u6211\u4eec\u4e5f\u53ef\u4ee5SET ROLE ALL \u3010EXCEPT xxx\u3011 \u5f53\u6709\u591a\u4e2a\u7684\u65f6\u5019\u4e5f\u53ef\u4ee5\u8fd9\u4e2a\u6837\u5b50
\n\u6211\u4eec\u6bcf\u6b21\u90fd\u9700\u8981\u8fd9\u6837set\u592a\u7d2f\u4e86\uff0c\u6211\u4eec\u4e5f\u53ef\u4ee5\u76f4\u63a5set default
\nroot@mysql 04:30:18>SET DEFAULT ROLE app_developer TO \u2018dev1\u2019@\u2019localhost\u2019;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

root@mysql 04:30:19>select * from default_roles;
\n+\u2014\u2014\u2014\u2013+\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014-+
\n| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
\n+\u2014\u2014\u2014\u2013+\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014-+
\n| localhost | dev1 | % | app_developer |
\n+\u2014\u2014\u2014\u2013+\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014-+
\n1 row in set (0.00 sec)<\/p>\n

 <\/p>\n

\u6536\u56de\u6743\u9650\u7684\u65f6\u5019\u53ef\u4ee5\u4f7f\u7528
\nREVOKE role FROM user;
\nREVOKE privileges FROM role;<\/p>\n

\u548crole\u76f8\u5173\u7684\u7cfb\u7edf\u53c2\u6570
\ndba@(none) 04:50:44>show variables like \u2018%role%\u2019;
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+
\n| Variable_name | Value |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+
\n| activate_all_roles_on_login | OFF |
\n| mandatory_roles | |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+
\n2 rows in set (0.00 sec)<\/p>\n

dba@(none) 04:52:05>set global activate_all_roles_on_login=on;<\/p>\n

https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/roles.html<\/p>\n","protected":false},"excerpt":{"rendered":"

\u4e00\u4e2aMySQL\u7684\u89d2\u8272\u662f\u4e00\u4e9b\u6743\u9650\u7684\u96c6\u5408\u3002\u5c31\u50cf\u4e00\u4e2a\u5e10\u53f7\u4e00\u6837\uff0c\u53ef\u4ee5\u5bf9\u4ed6\u8fdb\u884c\u6743\u9650\u7684\u8d4b\u6743\u548c\u6536\u56de\u3002 \u6211\u4eec\u53ef\u4ee5\u628a\u89d2\u8272\u6388\u6743\u7ed9\u7528 […]<\/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-1367","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1367","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=1367"}],"version-history":[{"count":1,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1367\/revisions"}],"predecessor-version":[{"id":1368,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1367\/revisions\/1368"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1367"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}