MariaDB/MySQL用戶和權(quán)限管理的示例分析

這篇文章給大家分享的是有關(guān)MariaDB/MySQL用戶和權(quán)限管理的示例分析的內(nèi)容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

MariaDB/MySQL中的user由用戶名和主機名構(gòu)成，如"root@localhost"，同用戶名但不同主機名對MySQL/MariaDB來講是不同的，也就是說"root@localhost"和"root@127.0.0.1"是不同的用戶，盡管它們都是本機的root。

在MariaDB/MySQL服務(wù)器啟動后會載入權(quán)限表到內(nèi)存中，當用戶要連接服務(wù)器，會讀取權(quán)限表來驗證和分配權(quán)限，即在內(nèi)存中進行權(quán)限的讀取和寫入。

MariaDB/MySQL中的權(quán)限系統(tǒng)經(jīng)過兩步驗證：

1.合法性驗證：驗證user是否合法，合法者允許連接服務(wù)器，否則拒絕連接。

2.權(quán)限驗證和分配：對通過合法性驗證的用戶分配對數(shù)據(jù)庫中各對象的操作權(quán)限。

1.1 權(quán)限表

MariaDB/MySQL中的權(quán)限表都存放在mysql數(shù)據(jù)庫中。MySQL5.6以前，權(quán)限相關(guān)的表有user表、db表、host表、tables_priv表、columns_priv表、procs_priv表(存儲過程和函數(shù)相關(guān)的權(quán)限)。從MySQL5.6開始，host表已經(jīng)沒有了。MariaDB中雖然有host表，但卻不用。

這幾個表用的最多的是user表。user表主要分為幾個部分：用戶列、權(quán)限列、安全列、資源控制列以及雜項列，最需要關(guān)注的是用戶列和權(quán)限列。其中權(quán)限列又分為普通權(quán)限(上表中紅色字體)和管理權(quán)限列，如select類的為普通權(quán)限，super權(quán)限為管理權(quán)限。且可以看到，db表中的權(quán)限全都是普通權(quán)限，user表中除了db表中具有的普通權(quán)限還有show_db_pirv和create_tablespace_priv，除此之外還有幾個管理員權(quán)限。也就是說，db中沒有的權(quán)限是無法授予到指定數(shù)據(jù)庫的。例如不能授予super權(quán)限給test數(shù)據(jù)庫。

另外，usage權(quán)限在上表中沒有列出，因為該權(quán)限是所有用戶都有的權(quán)限，它只用來表示能否登錄數(shù)據(jù)庫，它的一個特殊功能是grant僅指定該權(quán)限的時候不會影響現(xiàn)有權(quán)限，也就是說可以拿grant來修改密碼而不影響現(xiàn)有權(quán)限。

需要說明的是，從user表到db表再到tables_priv表最后是columns_priv表，它們的權(quán)限是逐層細化的。user表中的普通權(quán)限是針對所有數(shù)據(jù)庫的，例如在user表中的select_priv為Y，則對所有數(shù)據(jù)庫都有select權(quán)限；db表是針對特定數(shù)據(jù)庫中所有表的，如果只有test數(shù)據(jù)庫中有select權(quán)限，那么db表中就有一條記錄test數(shù)據(jù)庫的select權(quán)限為Y，這樣對test數(shù)據(jù)庫中的所有表都有select權(quán)限，而此時user表中的select權(quán)限就為N(因為為Y的時候是所有數(shù)據(jù)庫都有權(quán)限)；同理tables_priv表也一樣，是針對特定表中所有列的權(quán)限；columns_priv則是針對特定列的權(quán)限。

所以對于已經(jīng)通過身份合法性驗證的用戶的權(quán)限讀取和分配的機制如下：

• 1.讀取uesr表，看看user表是否有對應(yīng)為Y的權(quán)限列，有則分配。

• 2.讀取db表，看看db表中是否有哪個數(shù)據(jù)庫分配了對應(yīng)的權(quán)限。

• 3.讀取tables_priv表，看看哪些表中有對應(yīng)的權(quán)限。

• 4.讀取columns_priv表，看看對哪些具體的列有什么權(quán)限。

例如，為某一用戶授予test數(shù)據(jù)庫的select權(quán)限?？梢钥吹絬ser表中的select_priv為N，而db表中的select為Y。

GRANT SELECT ON test.* TO 'long'@'192.168.100.1' IDENTIFIED BY '123456';
SELECT host,user,select_priv FROM mysql.user;
SELECT * FROM mysql.db;

1.2 圖解認證和權(quán)限分配的兩個階段

1.3 權(quán)限生效時機

在服務(wù)器啟動時讀取權(quán)限表到內(nèi)存中，從此時開始權(quán)限表生效。

之后使用grant、revoke、set password 等命令也會隱含的刷新權(quán)限表到內(nèi)存中。

另外，使用顯式的命令flush privileges或mysqladmin flush-privileges或mysqladmin reolad也會將上述幾張權(quán)限表重新刷到內(nèi)存中以供后續(xù)的身份驗證和權(quán)限驗證、分配。

用戶管理分為幾個方面，創(chuàng)建用戶、對用戶授權(quán)、修改和刪除用戶。

2.1 創(chuàng)建用戶

創(chuàng)建賬號有幾種方法。

• 1.使用grant直接對賬號授權(quán)，賬號不存在則會創(chuàng)建；

• 2.向mysql.user表中插入記錄；

• 3.使用create user命令。

后兩種方法創(chuàng)建的用戶初始時沒有任何權(quán)限(只有usage登錄數(shù)據(jù)庫的權(quán)限)，并且修改權(quán)限后要使用 FLUSH PRIVILEGES 語句或執(zhí)行 mysqladmin flush-privileges 或 mysqladmin reload 命令刷新權(quán)限表到內(nèi)存中，而第一種方法簡便的多，創(chuàng)建用戶后會自動刷新權(quán)限表。

grant和revoke語法：

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user  [IDENTIFIED [BY [PASSWORD] 'password'][WITH with_option [with_option]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

with_option:  
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
  | MAX_STATEMENT_TIME time

grant可以在庫、表、函數(shù)、存儲過程、特定列上授權(quán)，且一次性可以為多個用戶授予多個對象的權(quán)限。其中 with grant option 表示擁有該權(quán)限后的用戶可以給別的用戶授予自身所擁有的權(quán)限。

revoke表示收回權(quán)限，注意revoke無法收回usage權(quán)限。

其中user的表示方法是 '用戶名'@'主機名' ，主機名部分可以是主機名，可以是IP地址，可以是localhost，可以是通配符組成的主機名(空的host值也表示所有host，等價于'user_name'@'%')。如下示例：

對于網(wǎng)段地址，可以指定掩碼來表示，如192.168.100.1/255.255.255.0，不能使用cidr格式的掩碼記錄方式，也不能指定非8、16、24、32位的掩碼，如192.168.100.1/255.255.255.240是不允許的。

如果在user表中的用戶有交叉部分，如root既可以從localhost登錄，也可以從127.0.0.1登錄，還可以從本機IP192.168.100.61登錄，還可以從網(wǎng)段地址192.168.100.%登錄，那么到底會從哪個登錄？

在讀取權(quán)限表user到內(nèi)存中的時候，首先會根據(jù)host列的具體性進行排序，然后再根據(jù)user列進行具體性排序(即理解為order by host,user)，然后從上到下掃描，首次掃描到符合的記錄就使用該記錄登錄。具體性的意思是越具體的user優(yōu)先級越高，通配符范圍越寬的user優(yōu)先級越低。例如root@localhost的具體性比root@'%'的具體性高，后者又比'%'@'%'的具體性高。

2.2 create user和alter user

在MySQL 5.6.7之前，不要使用這兩個命令創(chuàng)建用戶和修改用戶，因為它們會在mysql.user表的password列設(shè)置空串。到mysql5.6.7解決了這個問題。MariaDB可隨意使用。

語法：

CREATE [OR REPLACE] USER [IF NOT EXISTS] 
 user_specification [,user_specification] ...
  [WITH resource_option [resource_option] ...]

user_specification:
  username [authentication_option]

authentication_option:
  IDENTIFIED BY 'authentication_string' 
  
resource_option:
  MAX_QUERIES_PER_HOUR count
  | MAX_UPDATE_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

例如：

create user 'longshuai'@'127.0.0.1' identified by '123456';

alter user和create user語法基本一致，但在MySQL中有讓密碼過期的功能，而在MariaDB中不支持該功能。

ALTER USER user_specification [, user_specification] ...
user_specification:
    user PASSWORD EXPIRE

例如，讓剛才創(chuàng)建的用戶過期。

alter user 'longshuai'@'127.0.0.1' password expire;

2.3 記錄創(chuàng)建用戶的時間

MariaDB/MySQL中user的元數(shù)據(jù)信息都存放在mysql.user表中，但是在這個表中的信息分類很少，常用的就只有用戶類列和權(quán)限類列，沒有用戶的創(chuàng)建時間。

可以通過新增一列來記錄用戶的創(chuàng)建時間。

alter table mysql.user add column create_time timestamp default current_timestamp;

這樣以后新建用戶都會記錄創(chuàng)建時間。但是顯然，對于已有的用戶是沒有記錄時間的，它們的值都為'0000-00-00 00:00:00'。

MariaDB [mysql]> select host,user,create_time from mysql.user;
+---------------------+-----------+---------------------+
| host                | user      | create_time         |
+---------------------+-----------+---------------------+
| localhost           | root      | 2018-04-21 05:58:19 |
| 127.0.0.1           | root      | 2018-04-21 05:58:19 |
| ::1                 | root      | 2018-04-21 05:58:19 |
| localhost           |           | 2018-04-21 05:58:19 |
| 192.168.100.%       | root      | 2018-04-21 05:58:19 |
| 192.168.100.1       | long      | 2018-04-21 05:58:19 |
| 127.0.0.1           | longshuai | 2018-04-21 05:58:19 |
| 192.168.100.1       | longshuai | 0000-00-00 00:00:00 |
+---------------------+-----------+---------------------+

2.4 查看用戶權(quán)限

可以使用show grants語句查看某個user的權(quán)限信息。

例如：

MariaDB [mysql]> show grants for 'root'@'localhost';

Grants for root@localhost                                                                                    
-----------------------------------------------------------------------------------------------------------------
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION 
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

MariaDB [mysql]>SHOW GRANTS FOR 'long'@'192.168.100.1';
Grants for long@192.168.100.1   
-----------------------------------------------------------------------------------------------------------
GRANT USAGE ON *.* TO 'long'@'192.168.100.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' 
GRANT SELECT ON `test`.* TO 'long'@'192.168.100.1'

2.5 revoke命令的嚴格性

revoke命令回收權(quán)限時必須要明確指定回收的數(shù)據(jù)庫對象以及用戶名，其中usage權(quán)限無法回收。特別要說明的是revoke all，當你以為它會回收所有權(quán)限的時候，它可能一點權(quán)限都沒有回收。也就是說revoke命令的書寫非常嚴格。

用戶 'long'@'192.168.100.1' 在 *.* 上具有usage權(quán)限，在test.*上具有select權(quán)限。

MariaDB [mysql]> SHOW GRANTS FOR 'long'@'192.168.100.1';
Grants for long@192.168.100.1 
-----------------------------------------------------------------------------------------------------------
GRANT USAGE ON *.* TO 'long'@'192.168.100.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' 
GRANT SELECT ON `test`.* TO 'long'@'192.168.100.1'

對該用戶在 *.* 上進行revoke all，再次查看權(quán)限，發(fā)現(xiàn)權(quán)限根本一點變化都沒有。因為usage權(quán)限無法回收，而select權(quán)限是在test.*上而非*.*上。

REVOKE ALL ON *.* FROM 'long'@'192.168.100.1';

要回收test.*上的select權(quán)限，必須在revoke中指定test.*，而不能是 *.* 。以下兩個語句都能回收。

revoke select on test.* from 'long'@'192.168.100.1';
revoke all on test.* from 'long'@'192.168.100.1';

2.6 刪除用戶

直接使用drop user命令或者從mysql.user表中刪除對應(yīng)記錄。

drop user user_name1,username2...

注意，刪除表中用戶記錄的時候不會從現(xiàn)有用戶中回收對該表的權(quán)限，當下次再創(chuàng)建同名表的時候，會自動為用戶授予該表的權(quán)限造成權(quán)限外流。

因此，建議使用drop user語句來刪除用戶。

3.1 設(shè)置密碼

(1)grant all on *.* to 'root'@'localhost' identified by '123456' with grant option;
(2)grant usage on *.* to 'root'@'localhost' identified by '123456' with grant option;

使用usage權(quán)限表示在不影響現(xiàn)有權(quán)限的情況下使用grant來修改密碼。

(3)set password [for 'root'@'localhost'] =password('123456');

password函數(shù)中必須加引號，不寫user時是為當前用戶修改。

(4)alter user root@localhost identified by '123456';
(5)mysqladmin -uroot -h localhost -p'old_password' password 'new_password';
(6)update mysql.user set password=password('123456') where user='root' and host='localhost';

其中g(shù)rant和set password語句可以直接刷新權(quán)限表，其他語句需要使用 flush privileges 或其他刷新語句。

3.2 恢復(fù)root密碼

可以在啟動mysql服務(wù)時使用mysqld_safe服務(wù)程序并指定"--skip-grant-tables"選項表示跳過授權(quán)表，這樣登陸mysql服務(wù)器將不需要任何權(quán)限，包括密碼認證也不需要，但是同樣受限的是不能操作任何權(quán)限相關(guān)的內(nèi)容，比如修改權(quán)限，刷新授權(quán)表等。這通常是mysql管理員密碼忘記的時候使用的選項。由于跳過授權(quán)表使得mysql服務(wù)器極不安全，任何用戶都能直接登錄服務(wù)器，所以通常和"--skip-networking"選項一起使用來禁止來自網(wǎng)絡(luò)的服務(wù)器連接請求，這樣只能使用localhost或者127.0.0.1作為host來登錄。

另外，使用mysqld_safe啟動無授權(quán)表的服務(wù)前要停止已有的MySQL實例。由于跳過授權(quán)表無法操作權(quán)限相關(guān)內(nèi)容，所以修改mysql.user表中的管理員賬號的密碼字段是唯一修改方法。修改密碼后記得重啟MySQL服務(wù)。

步驟如下：

[root@xuexi mysql]# service mysqld stop
[root@xuexi mysql]# mysqld_safe --skip-grant-tables --skip-networking &
[root@xuexi mysql]# mysql
mysql> update mysql.user set password=password("123456") where user='root' and host='localhost';
mysql> flush privileges;
mysql> select user,host,password from mysql.user where user='root' and host='localhost';
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
1 row in set
mysql> \q
[root@xuexi mysql]# service mysqld stop
[root@xuexi mysql]# service mysqld start
[root@xuexi mysql]# mysql -uroot -p123456
mysql> \q

如果要找回多實例的密碼，則在mysqld_safe命令中使用 --defaults-file 指定對應(yīng)的配置文件即可。

感謝各位的閱讀！關(guān)于“MariaDB/MySQL用戶和權(quán)限管理的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！