【MySql】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可以從任何位址通路本地的資料庫,預設可以省略。還可以是 "[email protected].%"、"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 官方文檔

<a href="http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#account-management-sql">http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#account-management-sql</a>