文章目錄

• Mysql 的權限分類
• 權限數(shù)據(jù)的存儲
• 每種權限的修改策略和作用范圍
• 刷新權限
• MySQL 提供的操作權限
• 授權語法格式
• 參考示例
• • 授予用戶擁有某個數(shù)據(jù)庫的全部權限
  • 授予用戶擁有所有數(shù)據(jù)庫的全部權限
  • 授予用戶擁有某個數(shù)據(jù)庫的部分權限
  • 授予用戶擁有某個數(shù)據(jù)庫中的某個表的部分權限
  • 授予用戶也具有給其它用戶授權的權限
  • 授予用戶操作表外鍵的權限
  • 授予用戶創(chuàng)建臨時表的權限
  • 授予用戶操作索引的權限
  • 授予用戶創(chuàng)建視圖的權限
  • 授予用戶查看視圖源代碼的權限
  • 授予創(chuàng)建存儲過程和函數(shù)的權限
  • 授予用戶 file 權限
  • 授予用戶 super 權限
  • 授予用戶 process 權限
  • 授予修改/刪除存儲過程和函數(shù)的權限
  • 授予執(zhí)行/調用存儲過程和函數(shù)的權限
  • 授予用戶只能查詢某個表的特定列
  • 授予用戶執(zhí)行指定存儲過程的權限
  • 授予用戶執(zhí)行指定函數(shù)的權限
  • 授予某個用戶擁有所有數(shù)據(jù)庫的全部權限，且擁有授權給其它賬戶的權限
• 查看用戶所擁有的權限

Mysql 的權限分類

分為以下四種：

1.全局權限

mysql> grant all privileges on *.* to 'test'@'%' with grant option; # 賦予全部的權限 mysql> grant select on *.* to 'test'@'%'; # 賦予查詢的權限 mysql> revoke all privileges on *.* from 'test'@'%'; # 撤銷全部的權限

說明：通過通配符 *.* 賦予權限，會往 mysql.user 表插入一條記錄。

2.db權限

mysql> grant all privileges on testdb.* to 'test'@'%' with grant option; mysql> revoke all privileges on testdb.* from 'test'@'%'; mysql> grant select on testdb.* to 'test'@'%'; mysql> revoke select on testdb.* from 'test'@'%';

說明：通過 db_name.* 授予權限，會往 mysql.db 表插入一條記錄。

3.表權限

mysql> grant all privileges on testdb.student to 'test'@'%' with grant option; mysql> grant select on testdb.student to 'test'@'%';

說明：上述的授權語句，會往 mysql.tables_priv 表插入一條記錄。

4.列權限

mysql> grant select(id), insert(id,a) on testdb.student to 'test'@'%' with grant option;

說明：上述的授權語句，會往 mysql.columns_priv 表插入一條記錄。

權限數(shù)據(jù)的存儲

每種權限的數(shù)據(jù)都會在磁盤和內(nèi)存中存儲，具體的存儲位置為：

1.全局權限
磁盤：表 mysql.user
內(nèi)存：數(shù)組 acl_user

2.db權限
磁盤：表 mysql.db
內(nèi)存：數(shù)組 acl_dbs

3.表權限
磁盤：表 mysql.tables_priv
內(nèi)存：和列權限組成的 hash 結構 column_priv_hash

4.列權限
磁盤：表 mysql.columns_priv
內(nèi)存：和表權限組成的 hash 結構 column_priv_hash

每種權限的修改策略和作用范圍

1.全局權限

策略：已存在的連接不生效，新建立連接立即生效。即已經(jīng)存在的會話不會起作用，必須重新創(chuàng)建會話權限才會生效。

范圍：當前線程，即重啟服務后就失效了

2.db權限

策略：所有連接立即生效，但是已經(jīng)存在的會話必須執(zhí)行 use db_name 后權限才會生效。因為會話在執(zhí)行 use db_name 數(shù)據(jù)庫的相關權限會保存在會話變量中，即使之后別的會話 revoke 或者 grant 權限，也不會影響到該會話，在切換出該 db 之前該會話會一直持有原來的權限

范圍：全局，即重啟服務后依舊有效

3.表權限

策略：所有連接立即生效，即已經(jīng)存在的會話也會立刻生效

范圍：全局，即重啟服務后依舊有效

4.列權限
策略：所有連接立即生效，即已經(jīng)存在的會話也會立刻生效

范圍：全局 ，即重啟服務后依舊有效

刷新權限

對于全局權限，FLUSH PRIVILEGES 操作會清空 acl_user 數(shù)組，acl_dbs 數(shù)組，column_priv_hash 集合，然后從 mysql.user 表， mysql.db 表，mysql.table_priv 表，mysql.columns_priv 表讀取數(shù)據(jù)到對應的內(nèi)存對象中。所以說如果內(nèi)存中的權限數(shù)據(jù)和磁盤表中的數(shù)據(jù)一致的話，FLUSH PRIVILEGES 其實是可以不用做的。

使用 GRANT、REVOKE、SET PASSWORD、RENAME USER 等命令來更改用戶的權限，內(nèi)存和磁盤中的數(shù)據(jù)都是同步更新的，即 MySQL 服務器會注意到這些變化并立即將更新后的用戶數(shù)據(jù)和權限數(shù)據(jù)加載至內(nèi)存中，所以執(zhí)行上述這些命令后是不需要 FLUSH PRIVILEGES 的。

FLUSH PRIVILEGES 的使用場景：

如果使用 INSERT、UPDATE、DELETE 等 DML 語句直接修改權限表（mysql.user、mysql.db、mysql.tables_priv、mysql.columns_priv），內(nèi)存中的權限數(shù)據(jù)是不會同步更新的，此時我們就需要重啟服務器（當 MySQL 啟動時，所有的權限都會被加載到內(nèi)存中）或者使用 FLUSH PRIVILEGES 命令來更新內(nèi)存權限數(shù)據(jù)。即權限需在重啟服務器或者 FLUSH PRIVILEGES 之后方可生效。

MySQL 提供的操作權限

另請參見官方文檔：https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

授權語法格式

給用戶授權的語法格式：

mysql> GRANT permission1, permission2, ... ON database_name.table_name TO 'user_name'@'host'; mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO 'user_name'@'host' WITH GRANT OPTION;

說明:
1.GRANT 表示授予的含義
2.permission1, permission2, ... 表示授予的具體權限，如 SELECT、INSERT、UPDATE 等，權限之間使用逗號分隔；如果要授予所的權限則使用 ALL PRIVILEGES 或者 ALL
3.ON 表示這些權限對哪些數(shù)據(jù)庫和表生效，格式：【數(shù)據(jù)庫名.表名】，這里寫 *.* 表示所有數(shù)據(jù)庫的所有表。如果要指定將權限應用到test 庫的 user 表中，可以這么寫：test.user
4.database_name 數(shù)據(jù)庫名
5.table_name 表名
6.TO 將權限授予哪個用戶。格式：【用戶名@登錄IP或域名】
7.user_name 是用戶名；host 是主機名，主機名可以指定為 %，表示沒有限制，在任何主機都可以登錄；也可以指定為 192.168.0.%，表示在 192.168.0.0 ~ 192.168.0.255 這個網(wǎng)段的主機上遠程登錄數(shù)據(jù)庫服務器
8.user_name 和 host 可以使用單引號或者雙引號引起來，也可以直接省略掉
9.identified by 用來指定用戶的登錄密碼
10.with grant option 表示允許用戶將自己的權限授權給其它用戶
11.對同一個用戶多次添加權限時，會和已有權限合并，不會覆蓋已有權限。即權限會自動疊加，不會覆蓋之前授予的權限，比如你先給用戶添加一個 SELECT 權限，后來又給用戶添加了一個 INSERT 權限，那么該用戶就同時擁有了 SELECT 和 INSERT 權限。

參考示例

授予用戶擁有某個數(shù)據(jù)庫的全部權限

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'host';

注：
1.星號 * 是通配符，表示匹配全部的表
2.PRIVILEGES 關鍵詞可以省略

授予用戶擁有所有數(shù)據(jù)庫的全部權限

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'host';

注：星號是通配符。第一個星號表示匹配任意的數(shù)據(jù)庫，第二個星號表示匹配任意的表。

授予用戶擁有某個數(shù)據(jù)庫的部分權限

mysql> GRANT SELECT, INSERT, DELETE ON database_name.* TO 'user_name'@'host'; mysql> grant select,update on test.* to lwx@37.114.28.114;

注：用戶表中雖然 user 和 host 是兩個字段，但是授權的時候必須通過 user@host 來指定被授權的用戶，因為這個兩個字段是聯(lián)合主鍵，兩個字段聯(lián)合起來必須唯一。

授予用戶擁有某個數(shù)據(jù)庫中的某個表的部分權限

例如，授予用戶 pig@% 擁有數(shù)據(jù)庫 test 中的表 user 的查詢、插入權限：

mysql> GRANT SELECT, INSERT ON test.user TO 'pig'@'%';

授予用戶也具有給其它用戶授權的權限

語法格式：

mysql> GRANT permission1, permission2, ... ON database_name.table_name TO 'user_name'@'host' WITH GRANT OPTION;

例如，授予用戶 liaowenxiong@% 可以給其它用戶授予針對數(shù)據(jù)庫 test 中的表 student 的查詢、插入的權限的權限：

mysql> GRANT select,insert ON test.student TO 'liaowenxiong'@'%' WITH GRANT OPTION;

授予用戶操作表外鍵的權限

mysql> grant references on qydpw.* to ‘lwx’@‘192.168.0.%’;

授予用戶創(chuàng)建臨時表的權限

mysql> grant create temporary tables on qydpw.* to ‘lwx’@‘192.168.0.%’;

授予用戶操作索引的權限

mysql> grant index on qydpw.* to ‘developer’@‘192.168.0.%’;

授予用戶創(chuàng)建視圖的權限

mysql> grant create view on qydpw.* to ‘developer’@‘192.168.0.%’;

授予用戶查看視圖源代碼的權限

mysql> grant show view on qydpw.* to ‘developer’@‘192.168.0.%’;

授予創(chuàng)建存儲過程和函數(shù)的權限

mysql> grant create routine on qydpw.* to ‘developer’@‘192.168.0.%’; mysql> grant create routine on qydpw.* to ‘lwx’@‘127.0.0.1’;

授予用戶 file 權限

擁有 file 權限才可以執(zhí)行 select into outfile 和 load data infile 語句。

mysql> grant file on *.* to ‘lwx’@‘localhost’;

授予用戶 super 權限

擁有 super 權限，用戶可以終止任何查詢，使用 set 語句修改系統(tǒng)變量，使用 CHANGE MASTER 和 PURGE MASTER LOGS。

mysql> grant super on *.* to ‘lwx’@‘localhost’;

授予用戶 process 權限

通過這個權限，用戶可以執(zhí)行 SHOW PROCESSLIST 和 KILL 命令。默認情況下，每個用戶都可以執(zhí)行 SHOW PROCESSLIST 命令，但是只能查詢本用戶的進程。

mysql> SHOW PROCESSLIST; +------+-------+---------------------+------------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+-------+---------------------+------------+---------+------+----------+------------------+ | 2515 | root | localhost | qydpw | Query | 0 | starting | SHOW PROCESSLIST | | 2596 | qydpw | 47.114.59.224:46182 | qydpw | Sleep | 1779 | | NULL | | 2597 | qydpw | 47.114.59.224:46192 | qydpw | Sleep | 1689 | | NULL | | 2598 | qydpw | 47.114.59.224:46194 | qydpw | Sleep | 1686 | | NULL | | 2599 | qydpw | 47.114.59.224:46196 | production | Sleep | 1648 | | NULL | | 2600 | qydpw | 47.114.59.224:46208 | production | Sleep | 1505 | | NULL | | 2601 | qydpw | 47.114.59.224:46212 | qydpw | Sleep | 1444 | | NULL | | 2602 | qydpw | 47.114.59.224:46218 | qydpw | Sleep | 1333 | | NULL | | 2603 | qydpw | 47.114.59.224:46230 | production | Sleep | 1104 | | NULL | | 2604 | qydpw | 47.114.59.224:46234 | production | Sleep | 1066 | | NULL | | 2605 | qydpw | 47.114.59.224:46244 | qydpw | Sleep | 833 | | NULL | | 2606 | qydpw | 47.114.59.224:46246 | qydpw | Sleep | 814 | | NULL | | 2607 | qydpw | 47.114.59.224:46250 | production | Sleep | 792 | | NULL | | 2608 | qydpw | 47.114.59.224:46252 | production | Sleep | 788 | | NULL | | 2609 | qydpw | 47.114.59.224:46256 | production | Sleep | 727 | | NULL | | 2610 | qydpw | 47.114.59.224:46258 | qydpw | Sleep | 692 | | NULL | | 2611 | qydpw | 47.114.59.224:46268 | production | Sleep | 489 | | NULL | | 2612 | qydpw | 47.114.59.224:46270 | qydpw | Sleep | 480 | | NULL | | 2613 | qydpw | 47.114.59.224:46274 | qydpw | Sleep | 416 | | NULL | | 2614 | qydpw | 47.114.59.224:46280 | production | Sleep | 323 | | NULL | | 2615 | qydpw | 47.114.59.224:46290 | production | Sleep | 90 | | NULL | +------+-------+---------------------+------------+---------+------+----------+------------------+

注意：super、process、file 等權限不能夠指定某個數(shù)據(jù)庫，on 后面必須跟 *.*，如下示例：

mysql> grant super on pyt.* to lwx@localhost; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES mysql> grant super on *.* to lwx@localhost; Query OK, 0 rows affected (0.01 sec)

授予修改/刪除存儲過程和函數(shù)的權限

mysql> grant alter routine on qydpw.* to developer@192.168.0.%;

授予執(zhí)行/調用存儲過程和函數(shù)的權限

mysql> grant execute on qydpw.* to developer@192.168.0.%;

授予用戶只能查詢某個表的特定列

mysql> grant select(advice_id,user_id) on qydpw.tf_advice to lwx@localhost;

授予用戶執(zhí)行指定存儲過程的權限

例如，授予用戶執(zhí)行存儲過程 sys.diagnostics 的權限：

mysql> grant execute on procedure sys.diagnostics to lwx@127.0.0.1;

授予用戶執(zhí)行指定函數(shù)的權限

例如，授予用戶執(zhí)行函數(shù) sys.ps_thread_account 的權限：

mysql> grant execute on function sys.ps_thread_account to lwx@127.0.0.1;

授予某個用戶擁有所有數(shù)據(jù)庫的全部權限，且擁有授權給其它賬戶的權限

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'host' WITH GRANT OPTION;

查看用戶所擁有的權限

查看其它用戶的權限：

mysql> SHOW GRANTS FOR 'user_name'@'host';

例如，查看用戶 liaowenxiong@% 所擁有的權限：

mysql> show grants for 'liaowenxiong'@'%'; +--------------------------------------------------------------------------+ | Grants for liaowenxiong@% | +--------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'liaowenxiong'@'%' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'liaowenxiong'@'%' | +--------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

查看當前登錄用戶自己的權限：

mysql> show grants;

總結

以上是生活随笔為你收集整理的MySQL数据库的用户授权_查看权限的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。