【MySql】mysql的許可權體系介紹

北在南方發表於2016-04-13
MySql
mysql 的許可權體系大致分為5個層級:
全域性層級
全域性許可權適用於一個給定伺服器中的所有資料庫。這些許可權儲存在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤銷全域性許可權。
資料庫層級
資料庫許可權適用於一個給定資料庫中的所有目標。這些許可權儲存在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤銷資料庫許可權。
表層級
表許可權適用於一個給定表中的所有列。這些許可權儲存在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表許可權。
列層級
列許可權適用於一個給定表中的單一列。這些許可權儲存在mysql.columns_priv表中。當使用REVOKE時，您必須指定與被授權列相同的列。
子程式層級
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT許可權適用於已儲存的子程式。這些許可權可以被授予為全域性層級和資料庫層級。而且，除了CREATE ROUTINE外，這些許可權可以被授予為子程式層級，並儲存在mysql.procs_priv表中。
這些許可權資訊儲存在下面的系統表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
當使用者連線進來，mysqld會通過上面的這些表對使用者許可權進行驗證！
注意:
當後續目標是一個表、一個已儲存的函式或一個已儲存的過程時，object_type子句應被指定為TABLE、FUNCTION或PROCEDURE。當從舊版本的MySQL升級時，要使用本子句，您必須升級您的授權表。請
我們可以用 CREATE USER 或 GRANT 建立使用者,後者還同時分配相關許可權。而 REVOKE 則用於刪除使用者許可權,DROP USER 刪除賬戶。
MySQL 賦予使用者許可權命令語法為：
grant 許可權 on 資料庫物件 to 使用者;
grant 許可權 on 資料庫物件 to 使用者 identified by “密碼”;
grant 許可權 on 資料庫物件 to 使用者@”ip” identified by “密碼”
GRANT 語法:
GRANT privileges (columns)
  ON what
  TO user IDENTIFIED BY “password”
  WITH GRANT OPTION;
  
privileges 列表:
* ALTER: 修改表和索引。
* CREATE: 建立資料庫和表。
* DELETE: 刪除表中已有的記錄。
* DROP: 拋棄(刪除)資料庫和表。
* INDEX: 建立或拋棄索引。
* INSERT: 向表中插入新行。
* REFERENCE:未使用。
* SELECT: 檢索表中的記錄。
* UPDATE: 修改現存表記錄。
* FILE: 讀或寫伺服器上的檔案。
* PROCESS: 檢視伺服器中執行的執行緒資訊或殺死執行緒。
* RELOAD: 過載授權表或清空日誌、主機快取或表快取。
* SHUTDOWN: 關閉伺服器。
* ALL: 所有許可權,ALL PRIVILEGES同義詞。
* USAGE: 特殊的 “無許可權” 許可權。
user 賬戶包括 “username” 和 “host” 兩部分 即是username@host,後者表示該使用者被允許從何地接入。user@`%` 表示使用者user可以從任何地址訪問本地的資料庫,預設可以省略。還可以是 “user@10.250.7.%”、”user1@%.abc.com” 等。資料庫格式為 db.table,可以是 “test.*” 或 “*.*”,前者表示 test 資料庫的所有表,後者表示所有資料庫的所有表。
子句 “WITH GRANT OPTION” 表示該使用者可以為其他使用者分配許可權。使用grant 命令建立使用者或者進行授權之後，需要使用flush privileges重新整理MySQL的系統許可權相關表，否則會出現拒絕訪問,或者重新啟動mysql伺服器，來使新設定生效。當然後者並不是一種好想法！
比如:
一 grant普通資料使用者yangql402查詢、插入、更新、刪除 資料庫(test)中所有表資料的權利。
grant select, insert, update, delete on test.* to yangql402@`%`;

二 grant資料庫開發人員(yangql402)，建立表、索引、檢視、儲存過程、函式。。。等許可權。 
grant建立、修改、刪除 MySQL 資料表結構許可權。
grant create on test.* to yangql402@`10.250.7.225`;
grant alter  on test.* to yangql402@`10.250.7.225`;
grant drop   on test.* to yangql402@`10.250.7.225`;
 
grant 操作 MySQL 外來鍵許可權，官方文件上說未使用！
grant references on test.* to yangql402@`10.250.7.225`;
 
grant 操作 MySQL 臨時表許可權。
grant create temporary tables on test.* to yangql402@`10.250.7.225`;
 
grant 操作 MySQL 索引許可權。
grant index on test.* to yangql402@`10.250.7.225`;
 
grant 操作 MySQL 檢視、檢視檢視原始碼 許可權。
grant create view on test.* to yangql402@`10.250.7.225`;
grant show   view on test.* to yangql402@`10.250.7.225`;
 
grant 操作 MySQL 儲存過程、函式 許可權。
grant create routine on test.* to yangql402@`10.250.7.225`;
grant alter routine on test.* to yangql402@`10.250.7.225`;
grant execute        on test.* to yangql402@`10.250.7.225`;
 
三 grant 普通DBA管理某個MySQL資料庫(test)的許可權。
grant all privileges on test to dba@`localhost`
其中，關鍵字 “privileges” 可以省略。

四 grant 高階 DBA 管理 MySQL 中所有資料庫的許可權。
grant all on *.* to dba@`localhost`
 
五 MySQL grant 許可權，分別可以作用在多個層次上。
a. grant 作用在整個 MySQL 伺服器上：
grant select on *.* to dba@localhost; — dba 可以查詢 MySQL 中所有資料庫中的表。
grant all    on *.* to dba@localhost; — dba 可以管理 MySQL 中的所有資料庫
b. grant 作用在單個資料庫上：
grant select on test.* to dba@localhost; — dba 可以查詢 test 中的表。
c. grant 作用在單個資料表上：
grant select, insert, update, delete on test.yql8 to dba@localhost;
d. grant 作用在表中的列上：
grant select(id, se, rank) on test.yql8 to dba@localhost;
e. grant 作用在儲存過程、函式上：
grant execute on procedure test.yql8 to `dba`@`localhost`;
grant execute on function test.yql8 to `dba`@`localhost`;

六 檢視使用者許可權
檢視當前使用者自己的許可權：
show grants;
檢視其他 MySQL 使用者許可權：
show grants for dba@localhost;
七 撤銷使用者許可權
使用revoke 命令來登出使用者的許可權,具體語法：
要撤銷所有許可權，需使用以下語法。此語法用於取消對於已命名的使用者的所有全域性層級、資料庫層級、表層級和列層級的許可權。
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] …
也可以指定具體的許可權比如:
REVOKE SELECT FROM yangql402@”10.250.7.249″;
注意:
1 使用GRANT或REVOKE，操作者必須擁有GRANT OPTION許可權，並且您必須用於您正在授予或撤銷的許可權。
2 使用REVOKE撤銷全部許可權,操作者必須擁有mysql資料庫的全域性CREATE USER許可權或UPDATE許可權。
八 刪除使用者：
DROP USER user;
其中user 賬戶包括 “username” 和 “host” 兩部分 即是username@host;如果建立的時候為 yangql@”10.250.7.225″,則刪除的時候必須使用
drop user yangql@”10.250.7.225″，否則會報錯！
mysql> drop user yangql402;
ERROR 1396 (HY000): Operation DROP USER failed for `yangql402`@`10.250.7.225`
mysql> drop user yangql402@`10.250.7.225`;
Query OK, 0 rows affected (0.01 sec)
參考; MYSQL 官方文件


相關文章