2019男人天堂,又黄又爽无遮挡,激情综合网av

主頁 > 知識庫 > Mysql 用戶權(quán)限管理實現(xiàn)

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、用戶名、密碼是否存在，存在則通過驗證。
通過身份認(rèn)證后，進(jìn)行權(quán)限分配，按照user，db，tables_priv，columns_priv的順序進(jìn)行驗證。即先檢查全局權(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可以訪問的目錄進(jìn)行讀寫磁盤文件操作，可使用的命令包括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)限的表進(jìn)行鎖定，以防止其他鏈接對此表的讀或?qū)?/p>

Process權(quán)限代表允許查看MySQL中的進(jìn)程信息，比如執(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主機(jī)通過此用戶連接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強(qiáng)制關(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)建一個用戶之后的默認(rèn)權(quán)限，其本身代表連接登錄權(quán)限

2.1 系統(tǒng)權(quán)限表

User表：存放用戶賬戶信息以及全局級別（所有數(shù)據(jù)庫）權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問數(shù)據(jù)庫實例，如果有全局權(quán)限則意味著對所有數(shù)據(jù)庫都有此權(quán)限
Db表：存放數(shù)據(jù)庫級別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問此數(shù)據(jù)庫
Tables_priv表：存放表級別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問數(shù)據(jù)庫的這個表
Columns_priv表：存放列級別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問數(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字段存放用戶認(rèn)證信息
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)限認(rè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)用戶由兩部分組成：用戶名和登錄主機(jī)名

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

來自mysql.com這個域名下的所有主機(jī)， ‘192.168.1.%'代表所有來自192.168.1網(wǎng)段的主機(jī)

User值	Host 值	允許的連接
'fred'	'h1.example.net'	fred，連接 h1.example.net
''	'h1.example.net'	任何用戶，從中連接 h1.example.net
'fred'	'%'	fred，從任何主機(jī)連接
''	'%'	任何用戶，從任何主機(jī)連接
'fred'	'%.example.net'	fred，從example.net域中的任何主機(jī)連接
'fred'	'x.example.%'	fred，從連接 x.example.net，x.example.com， x.example.edu，等; 這可能沒用
'fred'	'198.51.100.177'	fred，從主機(jī)與IP地址連接 198.51.100.177
'fred'	'198.51.100.%'	fred，從198.51.100C類子網(wǎng)中的任何主機(jī)連接
'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域名主機(jī)連接user用戶的連接，而不是分別指從host1.example.com和host2.example.com主機(jī)過來的連接

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; 默認(rèn)過期策略

手動強(qiáng)制某個用戶密碼過期

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語句默認(rèn)的用戶是unlock狀態(tài)

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

Alter user語句默認(rèn)不會修改用戶的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)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

詳解MySQL 用戶權(quán)限管理
詳解MySQL InnoDB存儲引擎的內(nèi)存管理
MySQL存儲過程的創(chuàng)建、調(diào)用與管理詳解
Mysql實戰(zhàn)練習(xí)之簡單圖書管理系統(tǒng)

標(biāo)簽：迪慶南充龍巖西寧麗水自貢徐州無錫

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Mysql 用戶權(quán)限管理實現(xiàn)》，本文關(guān)鍵詞 Mysql,用戶,權(quán)限,管理,實現(xiàn),；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。