Mysql 用戶權(quán)限管理實現(xiàn)
1. MySQL 權(quán)限介紹
mysql中存在4個控制權(quán)限的表,分別為user表,db表,tables_priv表,columns_priv表
,我當(dāng)前的版本mysql 5.7.22 。
mysql權(quán)限表的驗證過程為:
- 先從user表中的Host,User,Password這3個字段中判斷連接的ip、用戶名、密碼是否存在,存在則通過驗證。
- 通過身份認證后,進行權(quán)限分配,按照user,db,tables_priv,columns_priv的順序進行驗證。即先檢查全局權(quán)限表user,如果user中對應(yīng)的權(quán)限為Y,則此用戶對所有數(shù)據(jù)庫的權(quán)限都為Y,將不再檢查db, tables_priv,columns_priv;如果為N,則到db表中檢查此用戶對應(yīng)的具體數(shù)據(jù)庫,并得到db中為Y的權(quán)限;如果db中為N,則檢查tables_priv中此數(shù)據(jù)庫對應(yīng)的具體表,取得表中的權(quán)限Y,以此類推。
1.1 MySQL 權(quán)限級別
分為:
全局性的管理權(quán)限: 作用于整個MySQL實例級別
數(shù)據(jù)庫級別的權(quán)限: 作用于某個指定的數(shù)據(jù)庫上或者所有的數(shù)據(jù)庫上
數(shù)據(jù)庫對象級別的權(quán)限:作用于指定的數(shù)據(jù)庫對象上(表、視圖等)或者所有的數(shù)據(jù)庫對象上
權(quán)限存儲在mysql庫的user, db, tables_priv, columns_priv, and procs_priv這幾個系統(tǒng)表中,待MySQL實例啟動后就加載到內(nèi)存中
查看mysql 有哪些用戶:
mysql> select user,host from mysql.user;
來看root 用戶在權(quán)限系統(tǒng)表中的數(shù)據(jù)
:
mysql> use mysql; mysql> select * from user where user='root' and host='localhost'\G; #所有權(quán)限都是Y ,就是什么權(quán)限都有 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'; # 沒有此條記錄
上面說過:權(quán)限的驗證過程
查看root@'localhost'用戶的權(quán)限
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 權(quán)限詳解
All/All Privileges權(quán)限代表全局或者全數(shù)據(jù)庫對象級別的所有權(quán)限
Alter權(quán)限代表允許修改表結(jié)構(gòu)的權(quán)限,但必須要求有create和insert權(quán)限配合。如果是rename表名,則要求有alter和drop原表, create和insert新表的權(quán)限
Alter routine權(quán)限代表允許修改或者刪除存儲過程、函數(shù)的權(quán)限
Create權(quán)限代表允許創(chuàng)建新的數(shù)據(jù)庫和表的權(quán)限
Create routine權(quán)限代表允許創(chuàng)建存儲過程、函數(shù)的權(quán)限
Create tablespace權(quán)限代表允許創(chuàng)建、修改、刪除表空間和日志組的權(quán)限
Create temporary tables權(quán)限代表允許創(chuàng)建臨時表的權(quán)限
Create user權(quán)限代表允許創(chuàng)建、修改、刪除、重命名user的權(quán)限
Create view權(quán)限代表允許創(chuàng)建視圖的權(quán)限
Delete權(quán)限代表允許刪除行數(shù)據(jù)的權(quán)限
Drop權(quán)限代表允許刪除數(shù)據(jù)庫、表、視圖的權(quán)限,包括truncate table命令
Event權(quán)限代表允許查詢,創(chuàng)建,修改,刪除MySQL事件
Execute權(quán)限代表允許執(zhí)行存儲過程和函數(shù)的權(quán)限
File權(quán)限代表允許在MySQL可以訪問的目錄進行讀寫磁盤文件操作,可使用的命令包括load data infile,select … into outfile,load file()函數(shù)
Grant option權(quán)限代表是否允許此用戶授權(quán)或者收回給其他用戶你給予的權(quán)限,重新付給管理員的時候需要加上這個權(quán)限
Index權(quán)限代表是否允許創(chuàng)建和刪除索引
Insert權(quán)限代表是否允許在表里插入數(shù)據(jù),同時在執(zhí)行analyze table,optimize table,repair table語句的時候也需要insert權(quán)限
Lock權(quán)限代表允許對擁有select權(quán)限的表進行鎖定,以防止其他鏈接對此表的讀或?qū)?/p>
Process權(quán)限代表允許查看MySQL中的進程信息,比如執(zhí)行show processlist, mysqladmin processlist, show engine等命令
Reference權(quán)限是在5.7.6版本之后引入,代表是否允許創(chuàng)建外鍵
Reload權(quán)限代表允許執(zhí)行flush命令,指明重新加載權(quán)限表到系統(tǒng)內(nèi)存中,refresh命令代表關(guān)閉和重新開啟日志文件并刷新所有的表
Replication client權(quán)限代表允許執(zhí)行show master status,show slave status,show binary logs命令
Replication slave權(quán)限代表允許slave主機通過此用戶連接master以便建立主從復(fù)制關(guān)系
Select權(quán)限代表允許從表中查看數(shù)據(jù),某些不查詢表數(shù)據(jù)的select執(zhí)行則不需要此權(quán)限,如Select 1+1, Select PI()+2;而且select權(quán)限在執(zhí)行update/delete語句中含有where條件的情況下也是需要的
Show databases權(quán)限代表通過執(zhí)行show databases命令查看所有的數(shù)據(jù)庫名
Show view權(quán)限代表通過執(zhí)行show create view命令查看視圖創(chuàng)建的語句
Shutdown權(quán)限代表允許關(guān)閉數(shù)據(jù)庫實例,執(zhí)行語句包括mysqladmin shutdown
Super權(quán)限代表允許執(zhí)行一系列數(shù)據(jù)庫管理命令,包括kill強制關(guān)閉某個連接命令, change master to創(chuàng)建復(fù)制關(guān)系命令,以及create/alter/drop server等命令
Trigger權(quán)限代表允許創(chuàng)建,刪除,執(zhí)行,顯示觸發(fā)器的權(quán)限
Update權(quán)限代表允許修改表中的數(shù)據(jù)的權(quán)限
Usage權(quán)限是創(chuàng)建一個用戶之后的默認權(quán)限,其本身代表連接登錄權(quán)限
2.1 系統(tǒng)權(quán)限表
User表
:存放用戶賬戶信息以及全局級別(所有數(shù)據(jù)庫)權(quán)限,決定了來自哪些主機的哪些用戶可以訪問數(shù)據(jù)庫實例,如果有全局權(quán)限則意味著對所有數(shù)據(jù)庫都有此權(quán)限
Db表:存放數(shù)據(jù)庫級別
的權(quán)限,決定了來自哪些主機的哪些用戶可以訪問此數(shù)據(jù)庫
Tables_priv表:存放表級別的權(quán)限
,決定了來自哪些主機的哪些用戶可以訪問數(shù)據(jù)庫的這個表
Columns_priv表:存放列級別的權(quán)限
,決定了來自哪些主機的哪些用戶可以訪問數(shù)據(jù)庫表的這個字段
Procs_priv表:存放存儲過程和函數(shù)
級別的權(quán)限
最重要的還是user表
2.1.1 User和 db 權(quán)限表的結(jié)構(gòu)
表名 | user |
db |
---|---|---|
范圍列 | Host |
Host |
User |
Db |
|
User |
||
權(quán)限列 | 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 |
||
password_last_changed |
||
password_lifetime |
||
account_locked |
||
資源控制列 | max_questions |
|
max_updates |
||
max_connections |
||
max_user_connections |
User權(quán)限表結(jié)構(gòu)中的特殊字段:
- Plugin,authentication_string字段存放用戶認證信息
- Password_expired設(shè)置成'Y'則表明允許DBA將此用戶的密碼設(shè)置成過期而且過期后要求用戶的使用者重置密碼(alter user/set password重置密碼)
- Password_last_changed作為一個時間戳字段代表密碼上次修改時間,執(zhí)行create user/alter user/set password/grant等命令創(chuàng)建用戶或修改用戶密碼時
此數(shù)值自動更新
- Password_lifetime代表從password_last_changed時間開始此密碼過期的天數(shù)
Account_locked代表此用戶被鎖住,無法使用
在mysql 5.7 以前在user表有password 這個字段。
2.1.2 Tables_priv和columns_priv權(quán)限表結(jié)構(gòu)
表名 | tables_priv |
columns_priv |
---|---|---|
范圍列 | Host |
Host |
Db |
Db |
|
User |
User |
|
Table_name |
Table_name |
|
Column_name |
||
權(quán)限列 | Table_priv |
Column_priv |
Column_priv |
||
其他列 | Timestamp |
Timestamp |
Grantor |
Tables_priv和columns_priv權(quán)限值
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' |
2.1.3 procs_priv權(quán)限表結(jié)構(gòu)
Table Name | procs_priv |
---|---|
Scope columns | Host |
Db |
|
User |
|
Routine_name |
|
Routine_type |
|
Privilege columns | Proc_priv |
Other columns | Timestamp |
Grantor |
- Routine_type是枚舉類型,代表是存儲過程還是函數(shù)
- Timestamp和grantor兩個字段暫時沒用
系統(tǒng)權(quán)限表字段長度限制表
Column Name | Maximum Permitted Characters |
---|---|
Host , Proxied_host |
60 |
User , Proxied_user |
32 |
Password |
41 |
Db |
64 |
Table_name |
64 |
Column_name |
64 |
Routine_name |
64 |
權(quán)限認證中的大小寫敏感問題
- 字段user,password,authencation_string,db,table_name大小寫敏感
- 字段host,column_name,routine_name大小寫不敏感
2.2 用戶權(quán)限信息管理
2.2.1 查看用戶權(quán)限信息
查看MYSQL有哪些用戶
mysql> select user,host from mysql.user;
查看已經(jīng)授權(quán)給用戶的權(quán)限信息
例如root
mysql> show grants for root@'localhost';
查看用戶的其他非授權(quán)信息
mysql> show create user root@'localhost';
2.2.2 用戶組成
MySQL的授權(quán)用戶由兩部分組成:用戶名和登錄主機名
- 表達用戶的語法為'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網(wǎng)段的主機
User值 | Host 值 | 允許的連接 |
---|---|---|
'fred' | 'h1.example.net' | fred,連接 h1.example.net |
'' | 'h1.example.net' | 任何用戶,從中連接 h1.example.net |
'fred' | '%' | fred,從任何主機連接 |
'' | '%' | 任何用戶,從任何主機連接 |
'fred' | '%.example.net' | fred,從example.net域中的任何主機連接 |
'fred' | 'x.example.%' | fred,從連接 x.example.net,x.example.com, x.example.edu,等; 這可能沒用 |
'fred' | '198.51.100.177' | fred,從主機與IP地址連接 198.51.100.177 |
'fred' | '198.51.100.%' | fred,從198.51.100C類子網(wǎng)中的任何主機連接 |
'fred' | '198.51.100.0/255.255.255.0' | 與前面的示例相同 |
2.2.3 修改用戶權(quán)限
執(zhí)行Grant,revoke,set password,rename user命令修改權(quán)限之后, MySQL會自動將修改后的權(quán)限信息同步加載到系統(tǒng)內(nèi)存中
如果執(zhí)行insert/update/delete操作上述的系統(tǒng)權(quán)限表之后,則必須再執(zhí)行刷新權(quán)限命令才能同步到系統(tǒng)內(nèi)存中,刷新權(quán)限命令包括:flush privileges
/mysqladmin flush-privileges / mysqladmin reload
如果是修改tables和columns級別的權(quán)限,則客戶端的下次操作新權(quán)限就會生效
如果是修改database級別的權(quán)限,則新權(quán)限在客戶端執(zhí)行use database命令后生效
如果是修改global級別的權(quán)限,則需要重新創(chuàng)建連接新權(quán)限才能生效
如果是修改global級別的權(quán)限,則需要重新創(chuàng)建連接新權(quán)限才能生效 (例如修改密碼)
2.2.4 創(chuàng)建 mysql 用戶
有兩種方式創(chuàng)建MySQL授權(quán)用戶
執(zhí)行create user/grant命令
(推薦方式)- 通過insert語句直接操作MySQL系統(tǒng)權(quán)限表
# 創(chuàng)建finley 這只是創(chuàng)建用戶并沒有權(quán)限 mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass'; # 把finley 變成管理員用戶 mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH GRANT OPTION; #創(chuàng)建用戶并賦予RELOAD,PROCESS權(quán)限 ,在所有的庫和表上 mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456'; # 創(chuàng)建keme用戶,在test庫,temp表, 上的id列只有select 權(quán)限 mysql> grant select(id) on test.temp to keme@'localhost' identified by '123456';
2.2.4 回收 mysql 權(quán)限
通過revoke命令收回用戶權(quán)限,回收的時候看一下這個用戶有哪些權(quán)限然后回收
我對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 用戶
通過執(zhí)行drop user
命令刪除MySQL用戶
還可以通過系統(tǒng)權(quán)限表刪除(不建議)
mysql> drop user admin@'localhost';
2.2.6 設(shè)置MySQL用戶資源限制
通過設(shè)置全局變量max_user_connections可以限制所有用戶在同一時間連接MySQL實例的數(shù)量,但此參數(shù)無法對每個用戶區(qū)別對待,所以MySQL提供了對每個用戶的資源限制管理
MAX_QUERIES_PER_HOUR:一個用戶在一個小時內(nèi)可以執(zhí)行查詢的次數(shù)(基本包含所有語句)
MAX_UPDATES_PER_HOUR:一個用戶在一個小時內(nèi)可以執(zhí)行修改的次數(shù)(僅包含修改數(shù)據(jù)庫或表的語句)
MAX_CONNECTIONS_PER_HOUR:一個用戶在一個小時內(nèi)可以連接MySQL的時間
MAX_USER_CONNECTIONS:一個用戶可以在同一時間連接MySQL實例的數(shù)量
從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"
創(chuàng)建用戶時指定密碼
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
修改當(dāng)前會話本身用戶密碼的方式包括:
mysql> ALTER USER USER() IDENTIFIED BY 'mypass'; mysql> SET PASSWORD = PASSWORD('mypass');
2.2.8 設(shè)置MySQL用戶密碼過期策略
設(shè)置系統(tǒng)參數(shù)default_password_lifetime作用于所有的用戶賬戶
- default_password_lifetime=180 設(shè)置180天過期
- default_password_lifetime=0 設(shè)置密碼不過期
如果為每個用戶設(shè)置了密碼過期策略,則會覆蓋上述系統(tǒng)參數(shù)
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
通過執(zhí)行create user/alter user命令中帶account lock/unlock子句設(shè)置用戶的lock狀態(tài)
Create user語句默認的用戶是unlock狀態(tài)
# 創(chuàng)建的時候給用戶鎖定 mysql> create user abc2@localhost identified by 'mysql' account lock;
Alter user語句默認不會修改用戶的lock/unlock狀態(tài)
# 修改用戶為unlock mysql> alter user abc2@'localhost' account unlock;
當(dāng)客戶端使用lock狀態(tài)的用戶登錄MySQL時,會收到如此報錯
Access denied for user ‘user_name'@'host_name'.
Account is locked.
官方文檔:https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html
到此這篇關(guān)于Mysql 用戶權(quán)限管理實現(xiàn)的文章就介紹到這了,更多相關(guān)Mysql 用戶權(quán)限管理內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Mysql數(shù)據(jù)庫的增刪改查、備份、還原等基本操作大全
這篇文章主要介紹了Mysql數(shù)據(jù)庫的增刪改查、備份、還原等基本操作,本文通過示例代碼給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-06-06允許任意IP訪問mysql數(shù)據(jù)庫的方法詳解
MYSQL默認只能本地連接,即127.0.0.1和localhost,其他主機IP無法訪問數(shù)據(jù)庫,那么如何允許任意IP訪問mysql數(shù)據(jù)庫,所以本文小編將給大家介紹允許任意IP訪問mysql數(shù)據(jù)庫的方法,文中通過代碼示例介紹的非常詳細,需要的朋友可以參考下2024-01-01設(shè)置Mysql5.6允許外網(wǎng)訪問的詳細流程分享
今天小編就為大家分享一篇設(shè)置Mysql5.6允許外網(wǎng)訪問的詳細流程分享,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2018-05-05