mysql權限詳解
Mysql權限管理也是通過mysql表來管理的
本文基於mysql8
-
身份驗證：
- 優先從mysql.user中判斷ip、用戶名、密碼是否存在,存在即通過驗證。
-
權限認證：
- 按照mysql.user、db、tables_priv、columns_priv、procs_priv順序驗證，如果對應權限值為Y,表示擁有該權限
1. MySQL 權限級別
-
全局性的管理權限：作用於整個MySQL實例級別 （user表）
- User表：存放用戶賬戶信息以及全局級別（所有數據庫）權限，決定了來自哪些主機的哪些用戶可以訪問數據庫實例，如果`有全局權限則意味着對所有數據庫都有此權限
-
數據庫級別的權限： 作用於某個指定的數據庫上或者所有的數據庫上 （db表）
* Db表：存放數據庫級別的權限，決定了來自哪些主機的哪些用戶可以訪問此數據庫 -
數據庫對象級別的權限：作用於指定的數據庫對象上（表、視圖等）或者所有的數據庫對象上（tables_priv、columns_priv、procs_priv）
- Tables_priv表：
存放表級別的權限，決定了來自哪些主機的哪些用戶可以訪問數據庫的這個表 - Columns_priv表：
存放列級別的權限，決定了來自哪些主機的哪些用戶可以訪問數據庫表的這個字段 - Procs_priv表：
存放存儲過程和函數級別的權限
- Tables_priv表：
權限存在mysql上述表中，待MySQL實例啟動後就加載到內存中
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權限是創建一個用戶之後的默認權限，其本身代表連接登錄權限
3. MySQL權限表結構說明
以user表為例，其他表類似。
注意： 這裡是以host、user為主鍵，所以在創建用戶以及賦權限的時候要特別小心
CREATE USER ‘test12345’@’%’ IDENTIFIED BY ‘123456’;
CREATE USER ‘test12345’@’*’ IDENTIFIED BY ‘123456’;
上面兩條語句會創建兩個用戶
Tables_priv和columns_priv權限值
| Table Name | Column Name | Possible Set Elements |
|---|---|---|
tables_priv |
Table_priv |
'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view', 'Trigger' |
tables_priv |
Column_priv |
'Select', 'Insert', 'Update', 'References' |
columns_priv |
Column_priv |
'Select', 'Insert', 'Update', 'References' |
procs_priv |
Proc_priv |
'Execute', 'Alter Routine', 'Grant' |
4. MySQL權限常用命令
命令說明
- 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網段的主機
# 有理論之後，下面的命令執行完可以在終端執行測試一下
# mysql8經測試，必須先創建用戶之後再賦予權限，賦予權限時，能再加IDENTIFIED BY，創建用戶和賦予權限語句的host、user必須相同
## 賦予用戶權限簡單命令##################################################
select user,host from mysql.user; # 查詢mysql用戶信息
show grants for root@'localhost'; #查看已經授權給用戶的權限信息
show create user root@'localhost'; #查看用戶的其他非授權信息
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456'; #創建用戶，這個時候還沒有權限 192.168.148.132
GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' WITH GRANT OPTION; #賦予權限，這裡賦予了所有權限，會直接在user表添加相關權限信息,這裡不能遠程連接
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION; #和上述兩個語句的區別是，這裡支持遠程訪問，上述語句只能本地訪問
## 回收 mysql 權限#####################################################
CREATE USER 'hsm'@'%' IDENTIFIED BY '123456'; #創建用戶
grant select on Test.User to hsm@'%'; #賦予查詢的權限
show grants for hsm@'%'; # 查詢權限信息
GRANT USAGE ON *.* TO `hsm`@`%`
GRANT SELECT ON `Test`.`User` TO `hsm`@`%`
revoke select on Test.User from hsm@'%'; #回收查詢權限，再次查詢，會發現沒有了select權限
## 刪除用戶權限 #########################################################
drop user hsm@'%' # 刪除用戶
flush privileges; #刷新權限
rename user 'hsm'@'*' to 'hsm'@'%'; # 重命名權限
修改用戶權限說明
- 執行Grant,revoke,set password,rename user命令修改權限之後， MySQL會自動將修改後的權限信息同步加載到系統內存中
- 如果執行insert/update/delete操作上述的系統權限表之後，則必須再執行刷新權限命令才能同步到系統內存中，刷新權限命令包括：
flush privileges/mysqladmin flush-privileges / mysqladmin reload - 如果是修改tables和columns級別的權限，則客戶端的下次操作新權限就會生效
- 如果是修改database級別的權限，則新權限在客戶端執行use database命令後生效
- 如果是修改global級別的權限，則需要重新創建連接新權限才能生效
- 如果是修改global級別的權限，則需要重新創建連接新權限才能生效 (例如修改密碼)
GRANT命令使用說明
- ALL PRIVILEGES 是表示所有權限，你也可以使用select、update等權限。
- ON 用來指定權限針對哪些庫和表。 *.* 中前面的號用來指定數據庫名，後面的號用來指定表名。
- TO 表示將權限賦予某個用戶。
- WITH GRANT OPTION 這個選項表示該用戶可以將自己擁有的權限授權給別人
- 可以使用GRANT重複給用戶添加權限，權限疊加，比如你先給用戶添加一個select權限，然後又給用戶添加一個insert權限，那麼該用戶就同時擁有了select和insert權限。
5. 其他命令
設置MySQL用戶資源限制
- 通過設置全局變量max_user_connections可以限制所有用戶在同一時間連接MySQL實例的數量，但此參數無法對每個用戶區別對待，所以MySQL提供了對每個用戶的資源限制管理
- MAX_QUERIES_PER_HOUR：一個用戶在一個小時內可以執行查詢的次數（基本包含所有語句）
- MAX_UPDATES_PER_HOUR：一個用戶在一個小時內可以執行修改的次數（僅包含修改數據庫或表的語句）
- MAX_CONNECTIONS_PER_HOUR：一個用戶在一個小時內可以連接MySQL的時間
- MAX_USER_CONNECTIONS：一個用戶可以在
同一時間連接MySQL實例的數量
修改 mysql 用戶密碼
alter user test12345@'*' IDENTIFIED BY '1234567'; # 修改密碼為1234567
設置MySQL用戶密碼過期策略
設置系統參數default_password_lifetime作用於所有的用戶賬戶
- default_password_lifetime=180 設置180天過期
- default_password_lifetime=0 設置密碼不過期
ALTER USER 'hsm'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'hsm'@'%' PASSWORD EXPIRE NEVER; #密碼不過期
ALTER USER 'hsm'@'%' PASSWORD EXPIRE DEFAULT; #默認過期策略
ALTER USER 'hsm'@'%' PASSWORD EXPIRE; # 手動強制某個用戶密碼過期
mysql 用戶 lock
通過執行create user/alter user命令中帶account lock/unlock子句設置用戶的lock狀態
create user abc2@localhost identified by 'mysql' account lock; #創建用戶並鎖定用戶
alter user abc2@'localhost' account unlock; # 解鎖用戶
5. 權限例子
- 只授予能滿足需要的最小權限，防止用戶幹壞事。比如用戶只是需要查詢，那就只給select權限就可以了，不要給用戶賦予update、insert或者delete權限。
- 創建用戶的時候限制用戶的登錄主機，一般是限制成指定IP或者內網IP段。
- 初始化數據庫的時候刪除沒有密碼的用戶。安裝完數據庫的時候會自動創建一些用戶，這些用戶默認沒有密碼。
- 為每個用戶設置滿足密碼複雜度的密碼。
- 定期清理不需要的用戶。回收權限或者刪除用戶。
-
創建一個用戶，對數據庫只有可讀權限
CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly'; #創建用戶readonly,%表示可以遠程連接 GRANT select ON *.* TO 'readonly'@'%'; #賦予權限，這裡賦予了所有權限，會直接在user表添加相關權限信息,這裡不能遠程連接 -
創建一個用戶，只賦予用戶某幾個數據庫的權限
CREATE USER 'order_all'@'%' IDENTIFIED BY 'order_all'; #創建用戶order,可以查看訂單表所有權限 GRANT ALL PRIVILEGES ON Test.* TO 'order_all'@'%'; #賦予權限,Test庫的全部權限 GRANT ALL PRIVILEGES ON mypower.* TO 'order_all'@'%'; #賦予權限,mypower庫的全部權限 -
創建一個用戶，只賦予用戶某幾個表的權限
CREATE USER 'table'@'%' IDENTIFIED BY 'table'; #創建用戶order,可以查看訂單表所有權限 GRANT ALL PRIVILEGES ON Test.user TO 'table'@'%'; #賦予權限,Test庫user表的全部權限 GRANT ALL PRIVILEGES ON Test.Test TO 'table'@'%'; #賦予權限,Test庫Test表的全部權限
