MySQL許可權管理
一、 MySQL的許可權型別簡介
MySQL資料庫提供了3種不同層次的許可權型別。
1） 管理許可權。此類許可權用來管理資料庫伺服器，這些許可權是全域性的，不單獨針對特定的資料庫。
2） 資料庫級別許可權。此類許可權作用於某個指定資料庫或者所有資料庫及其內的所有物件。
3） 物件級別許可權。此類許可權僅對資料庫內的物件級別，如表、檢視、索引及儲存過程等。
下表展示了MySQL中的各種許可權，及其在許可權表中的列名稱和許可權型別。
|
Privilege |
Column |
Context |
|
Create_priv |
databases, tables, or indexes |
|
|
Drop_priv |
databases, tables, or views |
|
|
Grant_priv |
databases, tables, or stored routines |
|
|
Lock_tables_priv |
databases |
|
|
References_priv |
databases or tables |
|
|
Event_priv |
databases |
|
|
Alter_priv |
tables |
|
|
Delete_priv |
tables |
|
|
Index_priv |
tables |
|
|
Insert_priv |
tables or columns |
|
|
Select_priv |
tables or columns |
|
|
Update_priv |
tables or columns |
|
|
Create_tmp_table_priv |
tables |
|
|
Trigger_priv |
tables |
|
|
Create_view_priv |
views |
|
|
Show_view_priv |
views |
|
|
Alter_routine_priv |
stored routines |
|
|
Create_routine_priv |
stored routines |
|
|
Execute_priv |
stored routines |
|
|
File_priv |
file access on server host |
|
|
Create_tablespace_priv |
server administration |
|
|
Create_user_priv |
server administration |
|
|
Process_priv |
server administration |
|
|
see proxies_priv table |
server administration |
|
|
Reload_priv |
server administration |
|
|
Repl_client_priv |
server administration |
|
|
Repl_slave_priv |
server administration |
|
|
Show_db_priv |
server administration |
|
|
Shutdown_priv |
server administration |
|
|
Super_priv |
server administration |
|
|
|
server administration |
|
|
|
server administration |
二、 MySQL的許可權控制原理
2.1 MySQL帳號簡介
MySQL中，帳號名稱包含使用者名稱和主機名（或者IP地址）2個部分，中間用@隔開，格式為'user_name'@'host_name'。
在連線認證階段， MySQL 透過使用者名稱和 主機名聯合進行確認，例如 MySQL 安裝後預設建立的賬戶 root@localhost 表示使用者 root 只能從本地 （localhost） 進行連線才可以透過認證，此使用者從其他任何主機對資料庫進行的連線都將被拒絕。 也就是說， 同樣的一個使用者名稱， 如果來自不同的 IP 地址，則 MySQL 將其視為不同的使用者。
2.2 MySQL許可權表
在許可權存取的過程中，系統會用到“mysql”資料庫中 user、 db 、tables_priv column、spriv和procs_priv這 5個最重要的許可權表，這幾個表中，最重要的是user表，表結構定義如下所示。
|
表名 |
user |
db |
|
使用者列 |
Host |
Host |
|
|
User |
Db |
|
|
Password |
User |
|
許可權列 |
Select_priv |
Select_priv |
|
|
Insert_priv |
Insert_priv |
|
|
Update_priv |
Update_priv |
|
|
Delete_priv |
Delete_priv |
|
|
Index_priv |
Index_priv |
|
|
Alter_priv |
Alter_priv |
|
|
Create_priv |
Create_priv |
|
|
Drop_priv |
Drop_priv |
|
|
Grant_priv |
Grant_priv |
|
|
Create_view_priv |
Create_view_priv |
|
|
Show_view_priv |
Show_view_priv |
|
|
Create_routine_priv |
Create_routine_priv |
|
|
Alter_routine_priv |
Alter_routine_priv |
|
|
Execute_priv |
Execute_priv |
|
|
Trigger_priv |
Trigger_priv |
|
|
Event_priv |
Event_priv |
|
|
Create_tmp_table_priv |
Create_tmp_table_priv |
|
|
Lock_tables_priv |
Lock_tables_priv |
|
|
References_priv |
References_priv |
|
|
Reload_priv |
|
|
|
Shutdown_priv |
|
|
|
Process_priv |
|
|
|
File_priv |
|
|
|
Show_db_priv |
|
|
|
Super_priv |
|
|
|
Repl_slave_priv |
|
|
|
Repl_client_priv |
|
|
|
Create_user_priv |
|
|
|
Create_tablespace_priv |
|
|
安全列 |
ssl_type |
|
|
|
ssl_cipher |
|
|
|
x509_issuer |
|
|
|
x509_subject |
|
|
|
plugin |
|
|
|
authentication_string |
|
|
|
password_expired |
|
|
資源控制列 |
max_questions |
|
|
|
max_updates |
|
|
|
max_connections |
|
|
|
max_user_connections |
|
User中的列主要分為 4 個部分：使用者列、許可權列、安全列和資源控制列。通常用得最多的是使用者列和許可權列， 其中許可權列又分為普通許可權和管理許可權。 普通許可權主要用於資料庫的操作，比如 select_priv、create_priv 等；而管理許可權主要用來對資料庫進行管理的操作，比如 process_priv、super_priv 等。
2.3 MySQL許可權系統的認證過程
MySQL 許可權系統透過下面兩個階段進行認證：
（1）對連線的使用者進行身份認證，合法的使用者透過認證，不合法的使用者拒絕連線；
（2）對透過認證的合法使用者賦予相應的許可權，使用者可以在這些許可權範圍內對資料庫做相應的操作。
當使用者進行連線的時候，許可權表的存取過程有以下兩個階段。先從 user 表中的 host、user 和 password 這 3 個欄位中判斷連線的 IP、使用者名稱和密碼是
否存在於表中，如果存在，則透過身份驗證，否則拒絕連線。如 果 通 過 身 份 驗 證 ， 則 按 照 以 下 權 限 表 的 順 序 得 到 數 據 庫 權 限 ：user?db?tables_priv?columns_priv。
在這幾個許可權表中，許可權範圍依次遞減，全域性許可權覆蓋區域性許可權。下面以一個例子來說明這個許可權範圍的問題。
1）建立帳號xiang@localhost，並賦予所有資料庫上的所以表的查詢許可權。
mysql> grant select on *.* to xiang@localhost identified by '123';
Query OK, 0 rows affected (0.01 sec)
*************************** 1. row ***************************
Host: localhost
User: xiang
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.03 sec)
mysql>
2）再看看db表
mysql> select * from db where user='xiang'\G;
Empty set (0.00 sec)
可以看到，user 表的的 select_priv 列是“Y” ，而 db 表中並沒有記錄，也就是說，對所有資料庫都具有相同許可權的使用者記錄並不需要記入 db 表，而僅僅需要將 user 表中的select_priv 改為“Y”即可。換句話說，user 表中的每個許可權都代表了對所有資料庫都有的許可權。
3）將xiang@localhost上的許可權改為只對kevin資料庫上的所有表的查詢許可權。
mysql> revoke select on *.* from xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on kevin.* to xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where user='xiang' and host='localhost'\G;
*************************** 1. row ***************************
Host: localhost
User: xiang
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
mysql> select * from db where user='xiang'\G;
*************************** 1. row ***************************
Host: localhost
Db: kevin
User: xiang
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)
可以看到，user 表中的 select_priv 變為“N” ，而 db 表中則增加了 db 為 kevin 的一條記錄。也就是說，當只授予部分資料庫某些許可權時，user 表中的相應許可權列保持“N” ，而將具體的資料庫許可權寫入 db 表。同樣的，table 和 column 的許可權機制和 db 類似。
從 上 面 例 子 可 以 看 出 ， 當 用 戶 通 過 權 限 認 證 ， 進 行 權 限 分 配 時 ， 將 按 照user?db?tables_priv?columns_priv 的順序進行許可權分配，即先檢查全域性許可權表 user，如果 user 中對應許可權為 “Y” ， 則此使用者對所有資料庫的許可權都為 “Y” ， 將不再檢查 db、 tables_priv和 columns_priv；如果為 “N” ，則到 db 表中檢查此使用者對應的具體資料庫，並得到 db 中為“Y”的許可權；如果 db 中相應許可權為“N” ，則檢查 tables_priv 中此資料庫對應的具體表，取得表中為“Y”的許可權；如果 tables_priv 中相應許可權為“N” ，則檢查 columns_priv 中此表對應的具體列，取得列中為“Y”的許可權。
2.4 MySQL的帳號管理
（1） 建立帳號。
有2種操作方法可以建立帳號：使用grant語法建立或者直接操作授權表。推薦使用第一種方式。
Grant語法很簡單：
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
示例如下：
例1：
mysql> grant all privileges on *.* to xiang@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
例2：授予 SUPER、PROCESS、FILE 許可權給使用者 xiang@localhost。
mysql> grant super,process,file on kevin.* to xiang@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
因為這幾個許可權都屬於管理許可權， 因此不能夠指定某個資料庫， on 後面必須跟 “*.*”，否則會報上面的錯誤。
mysql> grant super, process, file on *.* to xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
例3：只授予登入許可權給 xiang@localhost。
mysql> grant usage on *.* to xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
（2） 檢視和更改帳號許可權。
檢視帳號許可權，可以用如下命令：
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
更改許可權：
許可權可以新增和回收。和帳號建立一樣，變更許可權也有2種方式：使用grant和revoke命令方式，或者直接修改許可權表。
和建立賬號語法完全一樣，grant 可以直接用來對賬號進行增加。其實 grant 語句在執行的時候，如果許可權表中不存在目標賬號，則建立賬號；如果已經存在，則執行許可權的新增。這裡就不給示例了。
Revoke語句可以回收已經賦予的許可權。示例如下：
mysql> revoke select on *.* from xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
注意，usage 許可權不能被回收，也就是說，REVOKE 使用者並不能刪除使用者。
（3） 刪除帳號。
刪除帳號同樣也有2種方式：drop user命令和直接修改許可權表
Drop user命令很簡單，舉例如下：
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
*************************** 2. row ***************************
Grants for xiang@localhost: GRANT SELECT ON `kevin`.* TO 'xiang'@'localhost'
2 rows in set (0.00 sec)
mysql> drop user xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for xiang@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'xiang' on host 'localhost'
mysql>
修改許可權表方法，只要把相關許可權表中的使用者記錄刪除即可。
（4） 修改的許可權何時生效
當MySQL啟動時，會將許可權表在資料載入記憶體，當帳號透過身份認證後，就在記憶體中進行相應許可權的存取。
當我們使用grant、revoke、set password或者rename user等命令修改使用者許可權時，mysql會捕獲到這些許可權變化並重新載入更新後的許可權表。
但是 ，當我們使用insert、update、delete等sql語句直接操作許可權表修改帳號許可權時，伺服器是不會自動重新載入更新後的許可權的。這時，我們需要透過如下這些命令老告訴伺服器去主動載入新的許可權表。
FLUSH PRIVILEGES
mysqladmin flush-privileges
mysqladmin reload
示例如下：
先給帳號xiang@localhost新增對kevin資料庫內所有表的查詢許可權
mysql> grant select on kevin.* to xiang@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
*************************** 2. row ***************************
Grants for xiang@localhost: GRANT SELECT ON `kevin`.* TO 'xiang'@'localhost'
2 rows in set (0.00 sec)
透過直接修改許可權表的方式回收帳號xiang@localhost對kevin資料庫內所有表的查詢許可權
mysql> delete from db where user='xiang';
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
*************************** 2. row ***************************
Grants for xiang@localhost: GRANT SELECT ON `kevin`.* TO 'xiang'@'localhost'
2 rows in set (0.01 sec)
可以看到，這個時候雖然我們已經將db表中的許可權手動刪除掉了，但是記憶體中的許可權表沒有及時更新，因此依然可以查到相關的許可權。
重新整理許可權列表，再次查詢
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for xiang@localhost\G;
*************************** 1. row ***************************
Grants for xiang@localhost: GRANT USAGE ON *.* TO 'xiang'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
這時記憶體中的許可權表重新載入過，許可權已經更新了。
（5） 修改使用者密碼
方法 1：可以用 mysqladmin 命令在命令列指定密碼。
shell> mysqladmin -u xiang -h localhost password "123"
方法 2：執行 SET PASSWORD 語句。下例中將賬號xiang@localhost的密碼改為'123'。
mysql> SET PASSWORD FOR xiang@localhost = PASSWORD('123');
如果是更改自己的密碼，可以省略 for 語句：
mysql> SET PASSWORD = PASSWORD('123');
方法 3：還可以在全域性級別使用 GRANT USAGE 語句(在*.*)來指定某個賬戶的密碼而
不影響賬戶當前的許可權。
mysql> GRANT USAGE ON *.* TO xiang@localhost IDENTIFIED BY '123';
方法 4：直接更改資料庫的 user 表。
mysql> UPDATE user SET Password = PASSWORD('123') WHERE user = 'xiang' and host='localhost';
mysql> FLUSH PRIVILEGES;
注意：更改密碼時候一定要使用 PASSWORD 函式（mysqladmin 和 GRANT 兩種方式不用寫，會自動加上）。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28258625/viewspace-1608851/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql——許可權管理MySql
- Mysql 許可權管理MySql
- MySQL許可權管理實戰MySql
- mysql 管理:mysql 執行許可權(轉)MySql
- mysql使用者許可權管理MySql
- MySQL 使用者管理與許可權管理MySql
- mysql許可權MySql
- MySQL 許可權MySql
- MySQL 使用者及許可權管理?MySql
- MySQL使用者及許可權管理MySql
- MySQL之許可權管理和備份MySql
- MySQL使用者與許可權管理MySql
- Linux-許可權管理（ACL許可權）Linux
- MySQL資料庫許可權體系入門(4)---管理全域性許可權MySql資料庫
- PostgreSQL：許可權管理SQL
- oracle 許可權管理Oracle
- sql許可權管理SQL
- 許可權管理策略
- [MySQL光速入門]031 許可權管理MySql
- MySQL-03.使用者管理和許可權管理MySql
- MySQL資料庫許可權體系入門(5)---管理資料庫許可權MySql資料庫
- MySQL資料庫許可權體系入門(3)---管理使用者許可權MySql資料庫
- django開發之許可權管理(一)——許可權管理詳解(許可權管理原理以及方案)、不使用許可權框架的原始授權方式詳解Django框架
- Linux 許可權管理之目錄許可權限制Linux
- mysql 使用者及許可權管理 小結MySql
- Mysql許可權管理以及sql資料備份MySql
- mysql 使用者管理和許可權設定MySql
- mysql許可權參考MySql
- MySQL許可權問題MySql
- MySQL reload許可權MySql
- MySQL許可權系統MySql
- [玄月]MySQL許可權MySql
- Security 10：許可權管理
- SQL Server 許可權管理SQLServer
- 許可權管理[Linux]Linux
- MongoDB 3.0.8 許可權管理MongoDB
- 【許可權管理】Oracle中檢視、回收使用者許可權Oracle
- DRF內建許可權元件之自定義許可權管理類元件