MySQL 的 GRANT和REVOKE 命令

GRANT - 授權

將指定 操作物件 的指定 操作權限 授予指定的 用戶; 發出該 GRANT陳述句的可以是資料庫管理員，也可以是該資料庫物件的創建者;

1. 查詢

   • 查看用戶自己權限

     SHOW GRANTS;

   • 查看其他用戶權限

     SHOW GRANTS FOR 'user'@'host';

   Tips: host 可以使用通配符 %；如 'user'@'%', 'user'@'192.168.0.%';

2. 授權

   • 語法

     GRANT 權限 ON 資料庫物件 TO 用戶 [WITH GRANT OPTION];

   • 栗子

     • 授予 super用戶所有權限

       GRANT ALL [PRIVILEGES] ON . TO 'user'@'%';

     • 授予用戶 INSERT 權限

       CREATE INSERT ON db_name.* TO 'user'@'%' IDENTIFIED BY 'newpasswd';

     • 授予用戶 SELECT, UPDATE, DELETE 權限

       GRANT SELECT, UPDATE, DELETE ON db_name.* TO 'user'@'localhost'

3. 重繪

   FLUSH PRIVILEGES;

Tips:

• WITH GRANT OPTION: 表示是否能傳播其權限；（授權命令是由資料庫管理員使用的）
  • 指定 WITH GRANT OPTION，則獲得該權限的用戶可以把這種權限授予其他用戶；但不允許回圈傳授，即被授權者不能把權限在授回給授權者或祖先；
  • 未指定，則獲得某種權限的用戶只能自己使用該權限，不能傳播該權限；
• 在 GRANT 關鍵字之后指定一個或多個特權，如果要授予用戶多個權限，則每個權限都將以逗號分隔(見下表中的特權串列);
• 指定確定特權應用級別的privilege_level;
  • MySQL支持全域(*.*)，資料庫(database.*)，表(database.table)和列級別;
  • 如果您使用列權限級別，則必須在每個權限之后使用逗號分隔列的串列;
• 如果授予權限的用戶已經存在，則GRANT陳述句修改其特權; 如不存在，則GRANT陳述句將創建一個新用戶; 可選的條件 IDENTIFIED BY 允許為用戶設定新密碼;

REVOKE - 回收授權

• 語法

  REVOKE 權限 ON 資料庫物件 FROM 用戶 [CASCADE | RESTRICT];

• 栗子

  • 回收全部權限

    REVOKE ALL [PRIVILEGES] ON . FROM 'user'@'%';

  • 回收 INSERT 權限

    REVOKE INSERT ON db_name.* FROM 'user'@'%';

Tips:

• CASCADE | RESTRICT 當檢測到關聯的特權時，RESTRICT(默認值) 導致REVOKE失敗；CASCADE 可以回收所有這些關聯的特權；（如U1授權U2, U2授權U3，此時使用級聯（CASCADE）識訓了U2和U3的權限，否則系統將拒絕執行該命令）

GRANT允許的特權

• 下表說明了可用于GRANT和REVOKE陳述句的所有可用權限：

  權限	含義	全域	資料庫	表	列	程序	代理
  ALL [PRIVILEGES]	授予除了grant option之外的指定訪問級別的所有權限
  ALTER	允許用戶使用alter table陳述句	X	X	X
  ALTER ROUTINE	允許用戶更改或洗掉存盤程式, 可以使用{alter | drop} {procedure | unction}	X	X			X
  CREATE	允許用戶創建資料庫和表	X	X	X
  CREATE ROUTINE	可以使用{create | alter | drop} {procedure | function}	X
  CREATE TABLESPACE	允許用戶創建，更改或洗掉表空間和日志檔案組	X
  CREATE TEMPORARY TABLES	允許用戶使用create temporary table創建臨時表	X	X
  CREATE USER	允許用戶使用create user，drop user，rename user和revoke all privileges陳述句	X
  CREATE VIEW	允許用戶創建或修改視圖	X	X	X
  DELETE	允許用戶使用delete	X	X	X
  DROP	允許用戶洗掉資料庫，表和視圖	X	X	X
  EVENT	能夠使用事件計劃的事件	X	X
  EXECUTE	允許用戶執行存盤程序/存盤函式	X	X
  FILE	允許用戶讀取資料庫目錄中的任何檔案	X
  GRANT OPTION	允許用戶有權授予或撤銷其他帳戶的權限	X	X	X		X	X
  INDEX	允許用戶創建或洗掉索引	X	X	X
  INSERT	允許用戶使用insert陳述句	X	X	X	X
  LOCK TABLES	允許用戶在具有select權限的表上使用lock tables	X	X
  PROCESS	允許用戶使用show processlist陳述句查看所有行程	X
  PROXY	啟用用戶代理
  REFERENCES	允許用戶創建外鍵	X	X	X	X
  RELOAD	允許用戶使用flush操作	X
  REPLICATION CLIENT	允許用戶查詢主服務器或從服務器的位置	X
  REPLICATION SLAVE	允許用戶使用復制從站從主機讀取二進制日志事件	X
  SELECT	允許用戶使用select陳述句	X	X	X	X
  SHOW DATABASES	允許用戶顯示所有資料庫	X
  SHOW VIEW	允許用戶使用show create view陳述句	X	X	X
  SHUTDOWN	允許用戶使用mysqladmin shutdown命令	X
  SUPER	允許用戶使用其他管理操作，如change master to，kill，purge binary logs，set global和mysqladmin命令	X
  TRIGGER	允許用戶使用trigger操作	X	X	X
  UPDATE	允許用戶使用update陳述句	X	X	X	X
  USAGE	連接（登陸）權限（默認授予且不能被回收）

實體

用戶操作

1. 創建用戶

   CREATE USER 'user'@'%' IDENTIFIED BY 'newpasswd';

2. 重命名用戶

   CREATE USER 'old_user'@'%' TO 'new_user'@'%';

3. 洗掉用戶

   1. 查詢用戶

      SELECT user,host FROM mysql.user;

   2. 洗掉用戶

      DROP USER 'user'@'host';

查看和重繪權限

• 查看權限

  SHOW GRANTS [FOR 'user'@'host'];

• 重繪權限

  FLUSH PRIVILEGES;

授權與取消授權

1. 用法

   GRANT 權限 ON 資料庫物件 TO 用戶 [WITH GRANT OPTION];   -- 授權
   REVOKE 權限 ON 資料庫物件 FROM 用戶 [CASCADE | RESTRICT];   -- 取消授權
   

   Tips: REVOKE 跟 GRANT 的語法差不多，只需要把關鍵字 TO 換成 FROM 即可

2. 栗子

   • 添加權限（和已有權限合并，不會覆寫已有權限）

     -- 添加普通資料用戶，查詢、插入、更新、洗掉 資料庫中所有表資料的權利
     grant select, insert, update, delete on testdb.* to common_user@'%'
     
     -- 添加資料庫開發人員，創建表、索引、視圖、存盤程序、函式，，，等權限
     grant create, alter, drop on testdb.* to developer@'192.168.0.%';
     
     -- 添加操作 MySQL 外鍵權限
     grant references on testdb.* to developer@'192.168.0.%';
     
     -- 添加操作 MySQL 臨時表權限
     grant create temporary tables on testdb.* to developer@'192.168.0.%';
     
     -- 添加操作 MySQL 索引權限
     grant index on testdb.* to developer@'192.168.0.%';
     
     -- 添加操作 MySQL 視圖、查看視圖源代碼 權限
     grant create, show view on testdb.* to developer@'192.168.0.%';
     

   • 洗掉授權

     -- 洗掉普通資料用戶，查詢、插入、更新、洗掉 資料庫中所有表資料的權利
     revoke select, insert, update, delete on testdb.* from common_user@'%'
     
     -- 洗掉資料庫開發人員，創建表、索引、視圖、存盤程序、函式，，，等權限
     revoke create, alter, drop on testdb.* from developer@'192.168.0.%';
     
     -- 洗掉操作 MySQL 外鍵權限
     revoke references on testdb.* from developer@'192.168.0.%';
     
     -- 洗掉操作 MySQL 臨時表權限
     revoke create temporary tables on testdb.* from developer@'192.168.0.%';
     
     -- 洗掉操作 MySQL 索引權限
     revoke index on testdb.* from developer@'192.168.0.%';
     
     -- 洗掉操作 MySQL 視圖、查看視圖源代碼 權限
     revoke create, show view on testdb.* from developer@'192.168.0.%';
     

   • DBA 權限管理

     -- 普通 DBA 管理某個 MySQL 資料庫的權限
     grant all [privileges] on testdb to dba@'localhost'
     
     -- 高級 DBA 管理 MySQL 中所有資料庫的權限，
     grant all on *.* to dba@'localhost'
     

   • 細密度授權

     -- 作用在整個 MySQL 服務器上
     grant select on *.* to dba@localhost;   -- dba 可以查詢 MySQL 中所有資料庫中的表
     grant all    on *.* to dba@localhost;   -- dba 可以管理 MySQL 中的所有資料庫
     
     -- 作用在單個資料庫上
     grant select on testdb.* to dba@localhost; -- dba 可以查詢 testdb 中的表，
     
     -- 作用在單個資料表上
     grant select, insert, update, delete on testdb.t_orders to dba@localhost;
     
     -- 作用在表中的列上
     grant select(id, username, sex) on testdb.t_user to dba@localhost;
     

   • 權限傳播與識訓

     -- 授權 dba 查詢權限，并且可以將這些權限 grant 給其他用戶
     grant select on testdb.* to dba@localhost with grant option;
     
     -- 識訓 dba 和其傳播用戶的權限
     revoke select on testdb.* from dba@localhost cascade;
     

   Tips: 權限發生改變后， 需要重新加載一下權限，將權限資訊從記憶體中寫入資料庫；

Reference

• https://www.cnblogs.com/hcbin/archive/2010/04/23/1718379.html

標籤：MySQL

上一篇：MySQL 視圖簡介

下一篇：MySQL 基礎