MariaDB/MySQL用戶和權限管理的示例分析

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

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

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

MariaDB/MySQL中的權限系統經過兩步驗證：

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

2.權限驗證和分配：對通過合法性驗證的用戶分配對數據庫中各對象的操作權限。

1.1 權限表

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

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

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

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

所以對于已經通過身份合法性驗證的用戶的權限讀取和分配的機制如下：

• 1.讀取uesr表，看看user表是否有對應為Y的權限列，有則分配。

• 2.讀取db表，看看db表中是否有哪個數據庫分配了對應的權限。

• 3.讀取tables_priv表，看看哪些表中有對應的權限。

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

例如，為某一用戶授予test數據庫的select權限。可以看到user表中的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 圖解認證和權限分配的兩個階段

1.3 權限生效時機

在服務器啟動時讀取權限表到內存中，從此時開始權限表生效。

之后使用grant、revoke、set password 等命令也會隱含的刷新權限表到內存中。

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

用戶管理分為幾個方面，創建用戶、對用戶授權、修改和刪除用戶。

2.1 創建用戶

創建賬號有幾種方法。

• 1.使用grant直接對賬號授權，賬號不存在則會創建；

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

• 3.使用create user命令。

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

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可以在庫、表、函數、存儲過程、特定列上授權，且一次性可以為多個用戶授予多個對象的權限。其中 with grant option 表示擁有該權限后的用戶可以給別的用戶授予自身所擁有的權限。

revoke表示收回權限，注意revoke無法收回usage權限。

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

對于網段地址，可以指定掩碼來表示，如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登錄，還可以從網段地址192.168.100.%登錄，那么到底會從哪個登錄？

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

2.2 create user和alter user

在MySQL 5.6.7之前，不要使用這兩個命令創建用戶和修改用戶，因為它們會在mysql.user表的password列設置空串。到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

例如，讓剛才創建的用戶過期。

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

2.3 記錄創建用戶的時間

MariaDB/MySQL中user的元數據信息都存放在mysql.user表中，但是在這個表中的信息分類很少，常用的就只有用戶類列和權限類列，沒有用戶的創建時間。

可以通過新增一列來記錄用戶的創建時間。

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

這樣以后新建用戶都會記錄創建時間。但是顯然，對于已有的用戶是沒有記錄時間的，它們的值都為'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 查看用戶權限

可以使用show grants語句查看某個user的權限信息。

例如：

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命令回收權限時必須要明確指定回收的數據庫對象以及用戶名，其中usage權限無法回收。特別要說明的是revoke all，當你以為它會回收所有權限的時候，它可能一點權限都沒有回收。也就是說revoke命令的書寫非常嚴格。

用戶 'long'@'192.168.100.1' 在 *.* 上具有usage權限，在test.*上具有select權限。

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，再次查看權限，發現權限根本一點變化都沒有。因為usage權限無法回收，而select權限是在test.*上而非*.*上。

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

要回收test.*上的select權限，必須在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表中刪除對應記錄。

drop user user_name1,username2...

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

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

3.1 設置密碼

(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權限表示在不影響現有權限的情況下使用grant來修改密碼。

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

password函數中必須加引號，不寫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';

其中grant和set password語句可以直接刷新權限表，其他語句需要使用 flush privileges 或其他刷新語句。

3.2 恢復root密碼

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

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

步驟如下：

[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 指定對應的配置文件即可。

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