一起聊聊mysql中的賬戶和權限

288

本篇文章給大家帶來了mysql中賬戶的權限的相關問題，當用戶執行任何數據庫操作時，服務器將會驗證用戶是否具有相應的權限，例如查詢表需要 select 權限，刪除對象需要 drop 權限。希望對大家有幫助。

一起聊聊mysql中的賬戶和權限

當客戶端連接 mysql 服務器時，必須提供有效的身份認證，例如用戶名和密碼。當用戶執行任何數據庫操作時，服務器將會驗證用戶是否具有相應的權限，例如查詢表需要 SELECT 權限，刪除對象需要 DROP 權限。

為了方便用戶權限的管理，MySQL 8.0 提供了角色的功能。角色（Role）是一組權限的集合。

一起聊聊mysql中的賬戶和權限

本篇我們討論 MySQL 中的賬戶和權限的管理。

5.1 管理用戶

5.1.1 創建用戶

MySQL 使用 CREATE USER 語句創建用戶，基本語法如下：

CREATE?USER?[if?NOT?EXISTS]?account_name IDENTIFIED?BY?'password';

其中，account_name 是賬戶名稱；賬戶名稱分為兩個部分：用戶名（user_name）和主機名（host_name），使用 % 連接。IDENTIFIED BY 用于指定用戶的密碼。IF NOT EXISTS 用于避免創建重名賬戶時產生錯誤信息。

以下語句創建一個新的用戶 dev01，它可以從本機登錄（localhost）：

mysql>?CREATE?USER?dev01@localhost?IDENTIFIED?BY?'Dev01@mysql'; Query?OK,?0?rows?affected?(0.21?sec)

MySQL 中的賬戶由用戶名和主機名共同決定，主機 office.example.com 上的 dev01 和主機 home.example.com 上的 dev01 是兩個賬戶。如果不指定主機名，表示用戶可以從任何主機登錄：

user_name user_name@%

% 是通配符，表示任何字符串；另外，_ 表示任意單個字符。

如果用戶名或主機名中包含特殊字符，例如空格或者 – ，需要使用引號分別引用這兩部分內容：

'user-name'@'host-name'

除了單引號之外，也可以使用反引號（`）或者雙引號（”）。

MySQL 中的賬戶信息存儲在系統數據庫 mysql 的 user 表中：

mysql>?select?host,?user?from?mysql.user; +-----------+------------------+ |?host??????|?user?????????????| +-----------+------------------+ |?localhost?|?dev01????????????| |?localhost?|?mysql.infoschema?| |?localhost?|?mysql.session????| |?localhost?|?mysql.sys????????| |?localhost?|?root?????????????| +-----------+------------------+ 5?rows?in?set?(0.00?sec)

除了 dev01@localhost 之外，其他 4 個用戶都是初始化創建的系統用戶。

除了基本語法之外，創建用戶時還可以指定更多選項：

resource_option:?{ ????MAX_QUERIES_PER_HOUR?count ??|?MAX_UPDATES_PER_HOUR?count ??|?MAX_CONNECTIONS_PER_HOUR?count ??|?MAX_USER_CONNECTIONS?count }

resource_option 用于限制該用戶對系統資源的使用：

  • MAX_QUERIES_PER_HOUR，每小時允許執行的查詢次數。默認為 0 ，表示沒有限制；

  • MAX_UPDATES_PER_HOUR，每小時允許執行的更新次數。默認為 0 ，表示沒有限制；

  • MAX_CONNECTIONS_PER_HOUR，每小時允許執行的連接次數。默認為 0 ，表示沒有限制；

  • MAX_USER_CONNECTIONS，該用戶并發連接的數量。默認為 0 ，表示沒有限制；此時用戶的并發連接數由系統變量 max_user_connections 決定。

以下語句創建一個新的賬戶 dev02，允許從任何主機登錄。同時限制該用戶每小時最多執行 1000 次查詢和 100 次更新：

mysql>?CREATE?USER?'dev02'@'%' ????->?WITH?MAX_QUERIES_PER_HOUR?1000?MAX_UPDATES_PER_HOUR?100; Query?OK,?0?rows?affected?(0.01?sec)

注意第二行的->是客戶端的提示符，不是輸入的內容。查詢系統用戶表可以顯示以上設置：

mysql>?select?host,?user,?max_questions,?max_updates?from?mysql.user; +-----------+------------------+---------------+-------------+ |?host??????|?user?????????????|?max_questions?|?max_updates?| +-----------+------------------+---------------+-------------+ |?%?????????|?dev02????????????|??????????1000?|?????????100?| |?localhost?|?dev01????????????|?????????????0?|???????????0?| |?localhost?|?mysql.infoschema?|?????????????0?|???????????0?| |?localhost?|?mysql.session????|?????????????0?|???????????0?| |?localhost?|?mysql.sys????????|?????????????0?|???????????0?| |?localhost?|?root?????????????|?????????????0?|???????????0?| +-----------+------------------+---------------+-------------+ 6?rows?in?set?(0.00?sec)

以下是密碼管理選項：

password_option:?{ ????PASSWORD?EXPIRE?[default?|?NEVER?|?INTERVAL?N?DAY] ??|?PASSWORD?history?{DEFAULT?|?N} ??|?PASSWORD?REUSE?INTERVAL?{DEFAULT?|?N?DAY} ??|?PASSWORD?require?CURRENT?[DEFAULT?|?OPTIONAL] }

密碼管理選項可以用于設置密碼的過期策略、重用策略和修改密碼時的驗證：

  • PASSWORD EXPIRE，將密碼立即設置為過期；PASSWORD EXPIRE DEFAULT，使用全局的密碼過期策略，由系統變量 default_password_lifetime 決定；PASSWORD EXPIRE NEVER，密碼永不過期；PASSWORD EXPIRE INTERVAL N DAY 密碼每隔 N 天過期；

  • PASSWORD HISTORY DEFAULT，使用全局的密碼重用策略，由系統變量 password_history 決定；PASSWORD HISTORY N，新密碼與最近 N 次密碼不能重復；

  • PASSWORD REUSE INTERVAL DEFAULT，使用全局的密碼重用策略（按照時間間隔指定），由系統變量 password_reuse_interval 決定；PASSWORD REUSE INTERVAL N DAY，新密碼與最近 N 天內的密碼不能重復；

  • PASSWORD REQUIRE CURRENT，用戶修改密碼時需要輸入當前密碼；PASSWORD REQUIRE CURRENT OPTIONAL，用戶修改密碼時不需要輸入當前密碼；PASSWORD REQUIRE CURRENT DEFAULT，使用全局策略，由系統變量 password_require_current 決定。

賬戶的密碼選項同樣可以通過 mysql.user 表查看：

mysql>?select?host,user, ????->?password_expired,?password_last_changed, ????->?password_lifetime,?password_reuse_history, ????->?password_reuse_time,?password_require_current ????->?from?mysql.user; +-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+ |?host??????|?user?????????????|?password_expired?|?password_last_changed?|?password_lifetime?|?password_reuse_history?|?password_reuse_time?|?password_require_current?| +-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+ |?%?????????|?dev02????????????|?N????????????????|?2019-09-23?15:02:47???|??????????????NULL?|???????????????????NULL?|????????????????NULL?|?NULL?????????????????????| |?localhost?|?dev01????????????|?N????????????????|?2019-09-23?14:23:39???|??????????????NULL?|???????????????????NULL?|????????????????NULL?|?NULL?????????????????????| |?localhost?|?mysql.infoschema?|?N????????????????|?2019-08-28?10:07:39???|??????????????NULL?|???????????????????NULL?|????????????????NULL?|?NULL?????????????????????| |?localhost?|?mysql.session????|?N????????????????|?2019-08-28?10:07:39???|??????????????NULL?|???????????????????NULL?|????????????????NULL?|?NULL?????????????????????| |?localhost?|?mysql.sys????????|?N????????????????|?2019-08-28?10:07:39???|??????????????NULL?|???????????????????NULL?|????????????????NULL?|?NULL?????????????????????| |?localhost?|?root?????????????|?N????????????????|?2019-08-28?10:07:44???|??????????????NULL?|???????????????????NULL?|????????????????NULL?|?NULL?????????????????????| +-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+ 6?rows?in?set?(0.00?sec)

以下是賬戶鎖定選項：

lock_option:?{ ????ACCOUNT?LOCK ??|?ACCOUNT?UNLOCK }

該選項用于指定是否鎖定賬戶，鎖定的賬戶無法使用；默認為 ACCOUNT UNLOCK，不鎖定賬戶。

5.1.2 修改用戶

ALTER USER 語句可以修改用戶的屬性，修改用戶的選項和創建用戶相同。

首先是修改用戶的密碼。以下語句用于修改用戶 dev01 的密碼：

mysql>?ALTER?USER?dev01@localhost?IDENTIFIED?BY?'Dev01@2019'; Query?OK,?0?rows?affected?(0.25?sec)

MySQL 提供了 RENAME USER 語句，用于修改用戶名：

mysql>?RENAME?USER?dev02?TO?dev03; Query?OK,?0?rows?affected?(0.26?sec)

用戶 dev02 被重命名為 dev03。

RENAME USER 語句自動將舊用戶的權限授予新用戶，但是不會自動解決舊用戶上的對象依賴。例如，某個存儲過程的定義者為舊的用戶名，并且使用定義者權限運行時，將會產生錯誤。

另一個常見的用戶修改操作就是鎖定賬戶和解鎖賬戶：

mysql>?ALTER?USER?dev01@localhost?ACCOUNT?LOCK; Query?OK,?0?rows?affected?(0.13?sec)

用戶 dev01 被鎖定，此時無法使用該用戶進行連接：

"C:Program?FilesMySQLMySQL?Server?8.0binmysql.exe"?-u?dev01?-p Enter?password:?********** ERROR?3118?(HY000):?Access?denied?for?user?'dev01'@'localhost'.?Account?is?locked.

系統變量 Locked_connects 用于記錄鎖定賬戶嘗試登錄的次數：

mysql>?SHOW?GLOBAL?STATUS?LIKE?'Locked_connects'; +-----------------+-------+ |?Variable_name???|?Value?| +-----------------+-------+ |?Locked_connects?|?1?????| +-----------------+-------+ 1?row?in?set?(0.00?sec)

最后我們將 dev01 進行解鎖：

mysql>?ALTER?USER?dev01@localhost?ACCOUNT?UNLOCK; Query?OK,?0?rows?affected?(0.10?sec)

5.1.3 刪除用戶

DROP USER 語句用于刪除一個用戶。以下語句將會刪除用 dev03：

mysql>?DROP?USER?dev03; Query?OK,?0?rows?affected?(0.14?sec)

如果被刪除的用戶已經連接到 MySQL 服務器，用戶可以繼續執行操作；但是無法建立新的連接。

5.2 管理權限

新創建的用戶默認只有 USAGE 權限，只能連接數據庫，而沒有任何操作權限。使用 SHOW GRANTS 命令可以查看用戶的權限：

mysql>?SHOW?GRANTS?FOR?dev01@localhost; +-------------------------------------------+ |?Grants?for?dev01@localhost????????????????| +-------------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?`dev01`@`localhost`?| +-------------------------------------------+ 1?row?in?set?(0.00?sec)

使用 GRANT 語句可以為用戶授予權限。

5.2.1 授予權限

GRANT 語句基本語法如下：

GRANT?privilege,?...? ON?privilege_level? TO?account_name;

GRANT 語句支持一次授予多個權限，使用逗號進行分隔。

privilege_level 指定權限的作用級別，包括：

  • 全局權限，作用于 MySQL 服務器中的所有數據庫。全局權限使用*.*表示，例如，以下語句授予 dev01@localhost 用戶查詢所有數據庫中的所有表的權限：

mysql>?GRANT?SELECT ???->?ON?*.* ???->?TO?dev01@localhost; Query?OK,?0?rows?affected?(0.01?sec) mysql>?SHOW?GRANTS?FOR?dev01@localhost; +--------------------------------------------+ |?Grants?for?dev01@localhost?????????????????| +--------------------------------------------+ |?GRANT?SELECT?ON?*.*?TO?`dev01`@`localhost`?| +--------------------------------------------+ 1?row?in?set?(0.00?sec)

全局權限存儲在 mysql.user 表中。

  • 數據庫權限，作用于指定數據庫中的所有對象。數據庫權限使用db_name.*表示，例如，以下語句授予 dev01@localhost 用戶查詢數據庫 world 中的所有表的權限：

mysql>?GRANT?ALL ????->?ON?world.* ????->?TO?dev01@localhost; Query?OK,?0?rows?affected?(0.01?sec) mysql>?SHOW?GRANTS?FOR?dev01@localhost; +----------------------------------------------------------+ |?Grants?for?dev01@localhost???????????????????????????????| +----------------------------------------------------------+ |?GRANT?SELECT?ON?*.*?TO?`dev01`@`localhost`???????????????| |?GRANT?ALL?PRIVILEGES?ON?`world`.*?TO?`dev01`@`localhost`?| +----------------------------------------------------------+ 2?rows?in?set?(0.00?sec)

數據庫權限存儲在 mysql.db 表中。

  • 表權限，作用于指定表的所有列。數據庫權限使用db_name.table_name表示；如果不指定 db_name，使用默認數據庫；如果沒有默認數據庫，將會返回錯誤。例如，以下語句授予 dev01@localhost 用戶數據庫 world 中country 表的增刪改查權限：

mysql>?GRANT?SELECT,?INSERT,?UPDATE,?delete ????->?ON?world.country ????->?TO?dev01@localhost; Query?OK,?0?rows?affected?(0.01?sec) mysql>?SHOW?GRANTS?FOR?dev01@localhost; +----------------------------------------------------------------------------------+ |?Grants?for?dev01@localhost???????????????????????????????????????????????????????| +----------------------------------------------------------------------------------+ |?GRANT?SELECT?ON?*.*?TO?`dev01`@`localhost`???????????????????????????????????????| |?GRANT?ALL?PRIVILEGES?ON?`world`.*?TO?`dev01`@`localhost`?????????????????????????| |?GRANT?SELECT,?INSERT,?UPDATE,?DELETE?ON?`world`.`country`?TO?`dev01`@`localhost`?| +----------------------------------------------------------------------------------+ 3?rows?in?set?(0.00?sec)

表權限存儲在 mysql.tables_priv 表中。

  • 列權限，作用于指定表的指定列。每個列權限都需要指定具體的列名。例如，以下語句授予 dev01@localhost 用戶在 world.country 表中 code 和 name 字段的查詢權限，以及 population 字段的修改權限：

mysql>?GRANT?SELECT(code,?name),?UPDATE(population) ????->?ON?world.country ????->?TO?dev01@localhost; Query?OK,?0?rows?affected?(0.01?sec) mysql>?SHOW?GRANTS?FOR?dev01@localhost; +----------------------------------------------------------------------------------------------------------------------------------+ |?Grants?for?dev01@localhost?| +----------------------------------------------------------------------------------------------------------------------------------+ |?GRANT?SELECT?ON?*.*?TO?`dev01`@`localhost`?| |?GRANT?ALL?PRIVILEGES?ON?`world`.*?TO?`dev01`@`localhost`?| |?GRANT?SELECT,?SELECT?(`code`,?`name`),?INSERT,?UPDATE,?UPDATE?(`population`),?DELETE?ON?`world`.`country`?TO?`dev01`@`localhost`?| +----------------------------------------------------------------------------------------------------------------------------------+ 3?rows?in?set?(0.00?sec)

列權限存儲在 mysql.columns_priv 表中。

  • 存儲例程權限，作用于存儲例程（函數和過程）。存儲例程權限可以基于全局、數據庫或者單個例程進行指定。以下語句授予 dev01@localhost 用戶在數據庫 world.country 中創建存儲例程的權限：

mysql>?GRANT?CREATE?ROUTINE ????->?ON?world.* ????->?TO?dev01@localhost; Query?OK,?0?rows?affected?(0.02?sec) mysql>?SHOW?GRANTS?FOR?dev01@localhost; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |?Grants?for?dev01@localhost?| +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |?GRANT?SELECT?ON?*.*?TO?`dev01`@`localhost`| |?GRANT?SELECT,?INSERT,?UPDATE,?DELETE,?CREATE,?DROP,?REFERENCES,?INDEX,?ALTER,?CREATE?TEMPORARY?TABLES,?LOCK?TABLES,?EXECUTE,?CREATE?VIEW,?SHOW?VIEW,?ALTER?ROUTINE,?EVENT,?TRIGGER?ON?`world`.*?TO?`dev01`@`localhost`?| |?GRANT?SELECT,?SELECT?(`code`,?`name`),?INSERT,?UPDATE,?UPDATE?(`population`),?DELETE?ON?`world`.`country`?TO?`dev01`@`localhost`?| +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3?rows?in?set?(0.00?sec)

存儲例程權限存儲在 mysql.procs_priv 表中。

  • 代理用戶權限，允許用戶作為其他用戶的代理。代理用戶擁有被代理用戶的所有權限。以下語句將 dev01@localhost 用戶設置為 root 用的代理：

mysql>?GRANT?PROXY ????->?ON?root ????->?TO?dev01@localhost; Query?OK,?0?rows?affected?(0.01?sec) mysql>?SHOW?GRANTS?FOR?dev01@localhost; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |?Grants?for?dev01@localhost?| +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |?GRANT?SELECT?ON?*.*?TO?`dev01`@`localhost`?| |?GRANT?SELECT,?INSERT,?UPDATE,?DELETE,?CREATE,?DROP,?REFERENCES,?INDEX,?ALTER,?CREATE?TEMPORARY?TABLES,?LOCK?TABLES,?EXECUTE,?CREATE?VIEW,?SHOW?VIEW,?ALTER?ROUTINE,?EVENT,?TRIGGER?ON?`world`.*?TO?`dev01`@`localhost`?| |?GRANT?SELECT,?SELECT?(`code`,?`name`),?INSERT,?UPDATE,?UPDATE?(`population`),?DELETE?ON?`world`.`country`?TO?`dev01`@`localhost`?| |?GRANT?PROXY?ON?'root'@'%'?TO?'dev01'@'localhost'?| +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4?rows?in?set?(0.00?sec)

代理用戶權限存儲在 mysql.proxies_priv 表中。

5.2.2 撤銷權限

REVOKE 語句執行與 GRANT 語句相反的操作，撤銷授予用戶的權限。

REVOKE?privilegee,?.. ON?privilege_level FROM?account_name;

撤銷權限的參數與授予權限時類似，以下語句撤銷用戶 dev01@localhost 所有的權限：

mysql>?REVOKE?ALL,?GRANT?OPTION ????->?FROM?dev01@localhost; Query?OK,?0?rows?affected?(0.01?sec) mysql>?SHOW?GRANTS?FOR?dev01@localhost; +--------------------------------------------------+ |?Grants?for?dev01@localhost???????????????????????| +--------------------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?`dev01`@`localhost`????????| |?GRANT?PROXY?ON?'root'@'%'?TO?'dev01'@'localhost'?| +--------------------------------------------------+ 2?rows?in?set?(0.00?sec)

代理用戶權限需要單獨撤銷：

mysql>?REVOKE?PROXY ????->?ON?root ????->?FROM?dev01@localhost; Query?OK,?0?rows?affected?(0.01?sec) mysql>?SHOW?GRANTS?FOR?dev01@localhost; +-------------------------------------------+ |?Grants?for?dev01@localhost????????????????| +-------------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?`dev01`@`localhost`?| +-------------------------------------------+ 1?row?in?set?(0.00?sec)

用戶 dev01@localhost 又恢復了初始的權限。

對于全局級別的權限，REVOKE 的效果在用戶下次登錄時生效；對于數據庫級別的權限，REVOKE 的效果在執行 USE 命令后生效；對于表級或者字段級別的權限，REVOKE 的效果隨后的查詢立即生效。

5.3 管理角色

當用戶越來越多時，權限的管理也越來越復雜；而實際上，許多用戶需要相同或類似的權限。為此，MySQL 8.0 引入了一個新的特性：角色（Role）。角色是一組權限的集合。

與賬戶類似，角色也可以授予權限；但是角色不能用于登錄數據庫。通過角色為用戶授權的步驟如下：

  • 創建一個角色；

  • 為角色授權權限；

  • 為用戶指定角色。

5.3.1 創建角色

假設我們的應用需要使用 world 數據庫。開發人員需要該數據庫的完全訪問權限，測試人員需要表的讀寫權限，業務分析人員需要查詢數據的權限。

首先，使用CREATE ROLE語句創建 3 個角色：

mysql>?CREATE?ROLE?devp_role,?read_role,?write_role; Query?OK,?0?rows?affected?(0.02?sec)

角色名稱和賬戶名稱類似，也可以包含 role_name 和 host_name 兩部分，使用 @ 連接。

此時如果查詢用戶表：

mysql>?SELECT?host,user,authentication_string?FROM?mysql.user; +-----------+------------------+------------------------------------------------------------------------+ |?host??????|?user?????????????|?authentication_string??????????????????????????????????????????????????| +-----------+------------------+------------------------------------------------------------------------+ |?%?????????|?devp_role????????|????????????????????????????????????????????????????????????????????????| |?%?????????|?read_role????????|????????????????????????????????????????????????????????????????????????| |?%?????????|?write_role???????|????????????????????????????????????????????????????????????????????????| |?localhost?|?dev01????????????|?$A$005$lw58QcU;QI|L`ktULChFhIVFxy5dsYrYmEhJkJqko4mezqefUFyT0zgyE2?| |?localhost?|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?| |?localhost?|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?| |?localhost?|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?| |?localhost?|?root?????????????|?$A$005$kDqbW(q*0Uev;TyKgUe56D9KXiFzPtrSGVxKjvM23CYN5pgE9dLrO0eT8?| +-----------+------------------+------------------------------------------------------------------------+ 8?rows?in?set?(0.00?sec)

可以看出，角色實際上也是一個用戶，但是沒有密碼。

5.3.2 為角色授權

為角色授權和用戶授權類似，也是使用 GRANT 語句。我們分別為上面的 3 個角色分配權限：

mysql>?GRANT?ALL?ON?world.*?TO?devp_role; Query?OK,?0?rows?affected?(0.01?sec) mysql>?GRANT?SELECT?ON?world.*?TO?read_role; Query?OK,?0?rows?affected?(0.01?sec) mysql>?GRANT?INSERT,?UPDATE,?DELETE?ON?world.*?TO?write_role; Query?OK,?0?rows?affected?(0.01?sec)

查看角色的權限和查詢用戶的權限類似：

mysql>?SHOW?GRANTS?FOR?devp_role; +------------------------------------------------------+ |?Grants?for?devp_role@%???????????????????????????????| +------------------------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?`devp_role`@`%`????????????????| |?GRANT?ALL?PRIVILEGES?ON?`world`.*?TO?`devp_role`@`%`?| +------------------------------------------------------+ 2?rows?in?set?(0.00?sec)

5.3.2 為用戶指定角色

接下來我們創建幾個用戶，然后為他們分別指定角色。

mysql>?CREATE?USER?devp1?IDENTIFIED?BY?'Devp1@2019'; Query?OK,?0?rows?affected?(0.01?sec) mysql>?CREATE?USER?read1?IDENTIFIED?BY?'Read1@2019'; Query?OK,?0?rows?affected?(0.01?sec) mysql>?CREATE?USER?test1?IDENTIFIED?BY?'Test1@2019'; Query?OK,?0?rows?affected?(0.04?sec)

為用戶指定角色和授予權限類似，也是使用GRANT語句：

mysql>?GRANT?devp_role?TO?devp1; Query?OK,?0?rows?affected?(0.01?sec) mysql>?GRANT?read_role?TO?read1; Query?OK,?0?rows?affected?(0.01?sec) mysql>?GRANT?read_role,?write_role?TO?test1; Query?OK,?0?rows?affected?(0.01?sec)

再次查詢用戶的權限：

mysql>?SHOW?GRANTS?FOR?devp1; +--------------------------------------+ |?Grants?for?devp1@%???????????????????| +--------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?`devp1`@`%`????| |?GRANT?`devp_role`@`%`?TO?`devp1`@`%`?| +--------------------------------------+ 2?rows?in?set?(0.00?sec)

如果想要知道用戶通過角色獲得的具體權限，可以使用using選項：

mysql>?SHOW?GRANTS?FOR?devp1?USING?devp_role; +--------------------------------------------------+ |?Grants?for?devp1@%???????????????????????????????| +--------------------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?`devp1`@`%`????????????????| |?GRANT?ALL?PRIVILEGES?ON?`world`.*?TO?`devp1`@`%`?| |?GRANT?`devp_role`@`%`?TO?`devp1`@`%`?????????????| +--------------------------------------------------+ 3?rows?in?set?(0.00?sec)

另外，也可以通過將一個用戶授予另一個用戶，實現權限的復制：

mysql>?GRANT?read1?TO?test1; Query?OK,?0?rows?affected?(0.09?sec)

用戶是具有登錄權限的角色，角色是不能登錄的用戶。

5.3.4 設置默認角色

使用 devp1 連接數據庫：

"C:Program?FilesMySQLMySQL?Server?8.0binmysql.exe"?-u?devp1?-p Enter?password:?********** Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?g. Your?MySQL?connection?id?is?14 Server?version:?8.0.17?MySQL?Community?Server?-?GPL Copyright?(c)?2000,?2019,?Oracle?and/or?its?affiliates.?All?rights?reserved. Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners. Type?'help;'?or?'h'?for?help.?Type?'c'?to?clear?the?current?input?statement. mysql>?use?world; ERROR?1044?(42000):?Access?denied?for?user?'devp1'@'%'?to?database?'world'

我們已經為用戶 devp1 授予了 devp_role 角色，該角色擁有數據庫 world 上的所有權限；錯誤的原因在于該角色沒有自動激活。使用CURRENT_ROLE()函數查看當前啟動的角色：

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

結果顯示沒有任何角色。SET DEFAULT ROLE命令可以設置用戶默認的活動角色：

mysql>?SET?DEFAULT?ROLE?ALL ????->?TO?devp1; Query?OK,?0?rows?affected?(0.01?sec)

再次使用 devp1 連接數據庫后，將會激活該用戶所有的角色：

"C:Program?FilesMySQLMySQL?Server?8.0binmysql.exe"?-u?devp1?-p Enter?password:?********** Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?g. Your?MySQL?connection?id?is?15 Server?version:?8.0.17?MySQL?Community?Server?-?GPL Copyright?(c)?2000,?2019,?Oracle?and/or?its?affiliates.?All?rights?reserved. Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners. Type?'help;'?or?'h'?for?help.?Type?'c'?to?clear?the?current?input?statement. mysql>?SELECT?current_role(); +-----------------+ |?current_role()??| +-----------------+ |?`devp_role`@`%`?| +-----------------+ 1?row?in?set?(0.00?sec) mysql>?use?world; Database?changed mysql>?select?*?from?city?limit?1; +----+-------+-------------+----------+------------+ |?ID?|?Name??|?CountryCode?|?District?|?Population?| +----+-------+-------------+----------+------------+ |??1?|?Kabul?|?AFG?????????|?Kabol????|????1780000?| +----+-------+-------------+----------+------------+ 1?row?in?set?(0.00?sec)

另一種方式就是使用SET ROLE命令設置當前會話的活動角色：

SET?ROLE?NONE; SET?ROLE?ALL; SET?ROLE?DEFAULT;

以上語句分別表示不設置任何角色、設置所有角色以及設置默認的角色。

5.3.5 撤銷角色的權限

撤銷角色的權限與撤銷用戶的權限類似，撤銷角色的權限同時會影響到具有該角色的用戶。

以下語句撤銷角色 write_role 的 DELETE 權限：

mysql>?REVOKE?DELETE ????->?ON?world.* ????->?FROM?write_role; Query?OK,?0?rows?affected?(0.14?sec)

此時，用戶 test1 上的相應權限也被撤銷。

5.3.6 刪除角色

DROP ROLE語句可以刪除角色：

DROP?ROLE?role_name,?...;

刪除角色的同時會撤銷為用戶指定的角色。以下語句將會刪除角色 read_role 和 write_role：

mysql>?DROP?ROLE?read_role,?write_role; Query?OK,?0?rows?affected?(0.10?sec)

推薦學習：mysql視頻教程

以上就是一起聊聊

? 版權聲明
THE END
數據庫
# 數據庫# mysql# 對象# 字符串# 并發# select# delete# default# if# require# history# using
喜歡就支持一下吧
點贊8 分享