Mysql 用戶權限管理實現

1. MySQL 權限介紹

mysql中存在4個控制權限的表，分別為user表，db表，tables_priv表，columns_priv表，我當前的版本mysql 5.7.22 。

mysql權限表的驗證過程為：

1. 先從user表中的Host,User,Password這3個字段中判斷連接的ip、用戶名、密碼是否存在，存在則通過驗證。
2. 通過身份認證後，進行權限分配，按照user，db，tables_priv，columns_priv的順序進行驗證。即先檢查全局權限表user，如果user中對應的權限為Y，則此用戶對所有數據庫的權限都為Y，將不再檢查db, tables_priv,columns_priv；如果為N，則到db表中檢查此用戶對應的具體數據庫，並得到db中為Y的權限；如果db中為N，則檢查tables_priv中此數據庫對應的具體表，取得表中的權限Y，以此類推。

1.1 MySQL 權限級別

分為：
全局性的管理權限： 作用於整個MySQL實例級別
數據庫級別的權限： 作用於某個指定的數據庫上或者所有的數據庫上
數據庫對象級別的權限：作用於指定的數據庫對象上（表、視圖等）或者所有的數據庫對象上

權限存儲在mysql庫的user, db, tables_priv, columns_priv, and procs_priv這幾個系統表中，待MySQL實例啟動後就加載到內存中

查看mysql 有哪些用戶：

mysql> select user,host from mysql.user;

來看root 用戶在權限系統表中的數據：

mysql> use mysql;
mysql> select * from user where user='root' and host='localhost'\G;  #所有權限都是Y ，就是什麼權限都有
mysql> select * from db where user='root' and host='localhost'\G;  # 沒有此條記錄
mysql> select * from tables_priv where user='root' and host='localhost';  # 沒有此條記錄
 
mysql> select * from columns_priv where user='root' and host='localhost'; # 沒有此條記錄
 
mysql> select * from procs_priv where user='root' and host='localhost'; # 沒有此條記錄

上面說過：權限的驗證過程

查看root@’localhost’用戶的權限

mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

2. MySQL 權限詳解

All/All Privileges權限代表全局或者全數據庫對象級別的所有權限

Alter權限代表允許修改表結構的權限，但必須要求有create和insert權限配合。如果是rename表名，則要求有alter和drop原表， create和insert新表的權限

Alter routine權限代表允許修改或者刪除存儲過程、函數的權限

Create權限代表允許創建新的數據庫和表的權限

Create routine權限代表允許創建存儲過程、函數的權限

Create tablespace權限代表允許創建、修改、刪除表空間和日志組的權限

Create temporary tables權限代表允許創建臨時表的權限

Create user權限代表允許創建、修改、刪除、重命名user的權限

Create view權限代表允許創建視圖的權限

Delete權限代表允許刪除行數據的權限

Drop權限代表允許刪除數據庫、表、視圖的權限，包括truncate table命令

Event權限代表允許查詢，創建，修改，刪除MySQL事件

Execute權限代表允許執行存儲過程和函數的權限

File權限代表允許在MySQL可以訪問的目錄進行讀寫磁盤文件操作，可使用的命令包括load data infile,select … into outfile,load file()函數

Grant option權限代表是否允許此用戶授權或者收回給其他用戶你給予的權限,重新付給管理員的時候需要加上這個權限

Index權限代表是否允許創建和刪除索引

Insert權限代表是否允許在表裡插入數據，同時在執行analyze table,optimize table,repair table語句的時候也需要insert權限

Lock權限代表允許對擁有select權限的表進行鎖定，以防止其他鏈接對此表的讀或寫

Process權限代表允許查看MySQL中的進程信息，比如執行show processlist, mysqladmin processlist, show engine等命令

Reference權限是在5.7.6版本之後引入，代表是否允許創建外鍵

Reload權限代表允許執行flush命令，指明重新加載權限表到系統內存中，refresh命令代表關閉和重新開啟日志文件並刷新所有的表

Replication client權限代表允許執行show master status,show slave status,show binary logs命令

Replication slave權限代表允許slave主機通過此用戶連接master以便建立主從復制關系

Select權限代表允許從表中查看數據，某些不查詢表數據的select執行則不需要此權限，如Select 1+1， Select PI()+2；而且select權限在執行update/delete語句中含有where條件的情況下也是需要的

Show databases權限代表通過執行show databases命令查看所有的數據庫名

Show view權限代表通過執行show create view命令查看視圖創建的語句

Shutdown權限代表允許關閉數據庫實例，執行語句包括mysqladmin shutdown

Super權限代表允許執行一系列數據庫管理命令，包括kill強制關閉某個連接命令， change master to創建復制關系命令，以及create/alter/drop server等命令

Trigger權限代表允許創建，刪除，執行，顯示觸發器的權限

Update權限代表允許修改表中的數據的權限

Usage權限是創建一個用戶之後的默認權限，其本身代表連接登錄權限

2.1 系統權限表

User表：存放用戶賬戶信息以及全局級別（所有數據庫）權限，決定瞭來自哪些主機的哪些用戶可以訪問數據庫實例，如果有全局權限則意味著對所有數據庫都有此權限
Db表：存放數據庫級別的權限，決定瞭來自哪些主機的哪些用戶可以訪問此數據庫
Tables_priv表：存放表級別的權限，決定瞭來自哪些主機的哪些用戶可以訪問數據庫的這個表
Columns_priv表：存放列級別的權限，決定瞭來自哪些主機的哪些用戶可以訪問數據庫表的這個字段
Procs_priv表：存放存儲過程和函數級別的權限

最重要的還是user表

2.1.1 User和 db 權限表的結構

User權限表結構中的特殊字段：

• Plugin,authentication_string字段存放用戶認證信息
• Password_expired設置成’Y’則表明允許DBA將此用戶的密碼設置成過期而且過期後要求用戶的使用者重置密碼（alter user/set password重置密碼）
• Password_last_changed作為一個時間戳字段代表密碼上次修改時間，執行create user/alter user/set password/grant等命令創建用戶或修改用戶密碼時此數值自動更新
• Password_lifetime代表從password_last_changed時間開始此密碼過期的天數
• Account_locked代表此用戶被鎖住，無法使用

在mysql 5.7 以前在user表有password 這個字段。

2.1.2 Tables_priv和columns_priv權限表結構

Tables_priv和columns_priv權限值

2.1.3 procs_priv權限表結構

• Routine_type是枚舉類型，代表是存儲過程還是函數
• Timestamp和grantor兩個字段暫時沒用

系統權限表字段長度限制表

權限認證中的大小寫敏感問題

• 字段user,password,authencation_string,db,table_name大小寫敏感
• 字段host,column_name,routine_name大小寫不敏感

2.2 用戶權限信息管理

2.2.1 查看用戶權限信息

查看MYSQL有哪些用戶

mysql> select user,host from mysql.user;

查看已經授權給用戶的權限信息
例如root

mysql> show grants for root@'localhost';

查看用戶的其他非授權信息

mysql> show create user root@'localhost';

2.2.2 用戶組成

MySQL的授權用戶由兩部分組成：用戶名和登錄主機名

• 表達用戶的語法為’user_name’@’host_name’
• 單引號不是必須，但如果其中包含特殊字符則是必須的
• ”@‘localhost’代表匿名登錄的用戶
• Host_name可以使主機名或者ipv4/ipv6的地址。 Localhost代表本機， 127.0.0.1代表ipv4本機地址， ::1代表ipv6的本機地址
• Host_name字段允許使用%和_兩個匹配字符，比如’%’代表所有主機， ‘%.mysql.com’代表

來自mysql.com這個域名下的所有主機， ‘192.168.1.%’代表所有來自192.168.1網段的主機

2.2.3 修改用戶權限

執行Grant,revoke,set password,rename user命令修改權限之後， MySQL會自動將修改後的權限信息同步加載到系統內存中

如果執行insert/update/delete操作上述的系統權限表之後，則必須再執行刷新權限命令才能同步到系統內存中，刷新權限命令包括：flush privileges/mysqladmin flush-privileges / mysqladmin reload

如果是修改tables和columns級別的權限，則客戶端的下次操作新權限就會生效

如果是修改database級別的權限，則新權限在客戶端執行use database命令後生效

如果是修改global級別的權限，則需要重新創建連接新權限才能生效

如果是修改global級別的權限，則需要重新創建連接新權限才能生效 (例如修改密碼)

2.2.4 創建 mysql 用戶

有兩種方式創建MySQL授權用戶

• 執行create user/grant命令（推薦方式）
• 通過insert語句直接操作MySQL系統權限表

# 創建finley 這隻是創建用戶並沒有權限
mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
# 把finley 變成管理員用戶
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH
GRANT OPTION;
#創建用戶並賦予RELOAD,PROCESS權限 ，在所有的庫和表上
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456';
 
# 創建keme用戶，在test庫，temp表， 上的id列隻有select 權限
mysql> grant select(id) on test.temp to keme@'localhost' identified by '123456';

2.2.4 回收 mysql 權限

通過revoke命令收回用戶權限,回收的時候看一下這個用戶有哪些權限然後回收
我對admin 用戶做測試

mysql> show grants for admin@'localhost';
mysql> select user,host from mysql.user;
mysql> revoke PROCESS ON *.* FROM admin@'localhost';

2.2.5 刪除 mysql 用戶

通過執行drop user命令刪除MySQL用戶
還可以通過系統權限表刪除（不建議）

mysql> drop user admin@'localhost';

2.2.6 設置MySQL用戶資源限制

通過設置全局變量max_user_connections可以限制所有用戶在同一時間連接MySQL實例的數量，但此參數無法對每個用戶區別對待，所以MySQL提供瞭對每個用戶的資源限制管理

MAX_QUERIES_PER_HOUR：一個用戶在一個小時內可以執行查詢的次數（基本包含所有語句）

MAX_UPDATES_PER_HOUR：一個用戶在一個小時內可以執行修改的次數（僅包含修改數據庫或表的語句）

MAX_CONNECTIONS_PER_HOUR：一個用戶在一個小時內可以連接MySQL的時間

MAX_USER_CONNECTIONS：一個用戶可以在同一時間連接MySQL實例的數量

從5.0.3版本開始，對用戶‘user’@‘%.example.com’的資源限制是指所有通過example.com域名主機連接user用戶的連接，而不是分別指從host1.example.com和host2.example.com主機過來的連接

2.2.7 修改 mysql 用戶密碼

修改用戶密碼的方式包括：

mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
shell> mysqladmin -u user_name -h host_name password "new_password"

創建用戶時指定密碼

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

修改當前會話本身用戶密碼的方式包括：

mysql> ALTER USER USER() IDENTIFIED BY 'mypass';
mysql> SET PASSWORD = PASSWORD('mypass');

2.2.8 設置MySQL用戶密碼過期策略

設置系統參數default_password_lifetime作用於所有的用戶賬戶

• default_password_lifetime=180 設置180天過期
• default_password_lifetime=0 設置密碼不過期

如果為每個用戶設置瞭密碼過期策略，則會覆蓋上述系統參數

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; 密碼不過期
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; 默認過期策略

手動強制某個用戶密碼過期

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

2.2.9 mysql 用戶 lock

通過執行create user/alter user命令中帶account lock/unlock子句設置用戶的lock狀態

Create user語句默認的用戶是unlock狀態

# 創建的時候給用戶鎖定
mysql> create user abc2@localhost identified by 'mysql' account lock;

Alter user語句默認不會修改用戶的lock/unlock狀態

# 修改用戶為unlock
mysql> alter user abc2@'localhost' account unlock;

當客戶端使用lock狀態的用戶登錄MySQL時，會收到如此報錯
Access denied for user ‘user_name’@’host_name’.
Account is locked.

官方文檔：https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

到此這篇關於Mysql 用戶權限管理實現的文章就介紹到這瞭,更多相關Mysql 用戶權限管理內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

• MySQL 權限控制細節分析
• MySQL用戶管理與PostgreSQL用戶管理的區別說明
• MySQL 權限控制詳解
• MySQL系列之六 用戶與授權
• MySQL與PHP的基礎與應用專題之數據控制