Mysql許可權工作原理

mysql是如何來識別一個使用者的呢？

mysql為了安全性考慮，採用主機名+使用者名稱來判斷一個使用者的身份，因為在網際網路中很難透過使用者名稱來判斷一個使用者的身份，但是我們可以透過ip或者主機名判斷一臺機器，某個使用者透過這個機器過來的，我們可以識別為一個使用者，所以mysql中採用使用者名稱+主機名來識別使用者的身份。當一個使用者對mysql傳送指令的時候，mysql就是透過使用者名稱和來源（主機）來斷定使用者的許可權。

Mysql許可權驗證分為2個階段：

1. 階段1：連線資料庫，此時mysql會根據你的使用者名稱及你的來源（ip或者主機名稱）判斷是否有許可權連線

2. 階段2：對mysql伺服器發起請求操作，如create table、select、delete、update、create index等操作，此時mysql會判斷你是否有許可權操作這些指令

許可權生效時間

使用者及許可權資訊放在庫名為mysql的庫中，mysql啟動時，這些內容被讀進記憶體並且從此時生效，所以如果透過直接操作這些表來修改使用者及許可權資訊的，需要重啟mysql或者執行flush privileges;才可以生效。

使用者登入之後，mysql會和當前使用者之間建立一個連線，此時使用者相關的許可權資訊都儲存在這個連線中，存放在記憶體中，此時如果有其他地方修改了當前使用者的許可權，這些變更的許可權會在下一次登入時才會生效。

檢視mysql中所有使用者

使用者資訊在mysql.user表中，如下：

mysql> use mysql;Database changed
mysql> select user,host from user;+---------------+--------------+| user          | host         |+---------------+--------------+| test4         | 127.0.0.%    || test4         | 192.168.11.% || mysql.session | localhost    || mysql.sys     | localhost    || root          | localhost    || test2         | localhost    |+---------------+--------------+6 rows in set (0.00 sec)1234567891011121314

建立使用者

語法：

create user 使用者名稱[@主機名] [identified by '密碼'];1

說明：

1. 主機名預設值為%，表示這個使用者可以從任何主機連線mysql伺服器

2. 密碼可以省略，表示無密碼登入

示例1：不指定主機名
不指定主機名時，表示這個使用者可以從任何主機連線mysql伺服器

mysql> use mysql;Database changed
mysql> select user,host from user;+---------------+-----------+| user          | host      |+---------------+-----------+| mysql.session | localhost || mysql.sys     | localhost || root          | localhost |+---------------+-----------+3 rows in set (0.00 sec)mysql> create user test1;Query OK, 0 rows affected (0.00 sec)mysql> select user,host from user;+---------------+-----------+| user          | host      |+---------------+-----------+| test1         | %         || mysql.session | localhost || mysql.sys     | localhost || root          | localhost |+---------------+-----------+4 rows in set (0.00 sec)mysql> exit
Bye
C:\Users\Think>mysql -utest1
Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 49Server version: 5.7.25-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.123456789101112131415161718192021222324252627282930313233343536373839404142

上面建立了使用者名稱為test1無密碼的使用者，沒有指定主機，可以看出host的預設值為%，表示test1可以從任何機器登入到mysql中。

使用者建立之後可以在mysql庫中透過 select user,host from user;檢視到。

其他示例

create user 'test2'@'localhost' identified by '123';1

說明：test2的主機為localhost表示本機，此使用者只能登陸本機的mysql

create user 'test3'@% identified by '123';1

說明：test3可以從任何機器連線到mysql伺服器

create user 'test4'@'192.168.11.%' identified by '123';1

說明：test4可以從192.168.11段的機器連線mysql

修改密碼【3種方式】

方式1：透過管理員修改密碼

SET PASSWORD FOR '使用者名稱'@'主機' = PASSWORD('密碼');1

方式2：create user 使用者名稱[@主機名] [identified by ‘密碼’];

set password = password('密碼');1

方式3：透過修改mysql.user表修改密碼

use mysql;update user set authentication_string = password('321') where user = 'test1' and host = '%';flush privileges;123

注意：
透過表的方式修改之後，需要執行flush privileges;才能對使用者生效。
5.7中user表中的authentication_string欄位表示密碼，老的一些版本中密碼欄位是password。

給使用者授權

建立使用者之後，需要給使用者授權，才有意義。

語法：

grant privileges ON database.table TO 'username'[@'host'] [with grant option]1

grant命令說明：

• priveleges (許可權列表)，可以是all，表示所有許可權，也可以是select、update等許可權，多個許可權之間用逗號分開。

• ON 用來指定許可權針對哪些庫和表，格式為資料庫.表名 ，點號前面用來指定資料庫名，點號後面用來指定表名，. 表示所有資料庫所有表。

• TO 表示將許可權賦予某個使用者, 格式為username@host，@前面為使用者名稱，@後面接限制的主機，可以是IP、IP段、域名以及%，%表示任何地方。

• WITH GRANT OPTION 這個選項表示該使用者可以將自己擁有的許可權授權給別人。注意：經常有人在建立操作使用者的時候不指定WITH GRANT OPTION選項導致後來該使用者不能使用GRANT命令建立使用者或者給其它使用者授權。
  備註：可以使用GRANT重複給使用者新增許可權，許可權疊加，比如你先給使用者新增一個select許可權，然後又給使用者新增一個insert許可權，那麼該使用者就同時擁有了select和insert許可權。

示例：

grant all on *.* to 'test1'@‘%’;1

說明：給test1授權可以操作所有庫所有許可權，相當於dba

grant select on seata.* to 'test1'@'%';1

說明：test1可以對seata庫中所有的表執行select

grant select,update on seata.* to 'test1'@'%';1

說明：test1可以對seata庫中所有的表執行select、update

grant select(user,host) on mysql.user to 'test1'@'localhost';1

說明：test1使用者只能查詢mysql.user表的user,host欄位

檢視使用者有哪些許可權

show grants for ‘使用者名稱’[@‘主機’]

主機可以省略，預設值為%，示例：

mysql> show grants for 'test1'@'localhost';+--------------------------------------------------------------------+| Grants for test1@localhost                                         |+--------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'test1'@'localhost'                          || GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |+--------------------------------------------------------------------+2 rows in set (0.00 sec)12345678

show grants;

檢視當前使用者的許可權，如：

mysql> show grants;+---------------------------------------------------------------------+| Grants for root@localhost                                           |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION || GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'localhost'              || GRANT DELETE ON `seata`.* TO 'root'@'localhost'                     || GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |+---------------------------------------------------------------------+4 rows in set (0.00 sec)12345678910

撤銷使用者的許可權

語法

revoke privileges ON database.table FROM '使用者名稱'[@'主機'];1

可以先透過show grants命令查詢一下使用者對於的許可權，然後使用revoke命令撤銷使用者對應的許可權，示例：

mysql> show grants for 'test1'@'localhost';+--------------------------------------------------------------------+| Grants for test1@localhost                                         |+--------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'test1'@'localhost'                          || GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |+--------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> revoke select(host) on mysql.user from test1@localhost;Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'test1'@'localhost';+--------------------------------------------------------------+| Grants for test1@localhost                                   |+--------------------------------------------------------------+| GRANT USAGE ON *.* TO 'test1'@'localhost'                    || GRANT SELECT (user) ON `mysql`.`user` TO 'test1'@'localhost' |+--------------------------------------------------------------+2 rows in set (0.00 sec)1234567891011121314151617181920

上面我們先透過grants命令檢視test1的許可權，然後呼叫revoke命令撤銷對mysql.user表host欄位的查詢許可權，最後又透過grants命令檢視了test1的許可權，和預期結果一致。

刪除使用者【2種方式】

方式1：
drop user ‘使用者名稱’[@‘主機’]，示例：

mysql> drop user test1@localhost;Query OK, 0 rows affected (0.00 sec)12

drop的方式刪除使用者之後，使用者下次登入就會起效。

方式2：
透過刪除mysql.user表資料的方式刪除，如下：

delete from user where user='使用者名稱' and host='主機';flush privileges;12

注意透過表的方式刪除的，需要呼叫flush privileges;重新整理許可權資訊（許可權啟動的時候在記憶體中儲存著，透過表的方式修改之後需要重新整理一下）。

授權原則說明

• 只授予能滿足需要的最小許可權，防止使用者幹壞事，比如使用者只是需要查詢，那就只給select許可權就可以了，不要給使用者賦予update、insert或者delete許可權

• 建立使用者的時候限制使用者的登入主機，一般是限制成指定IP或者內網IP段

• 初始化資料庫的時候刪除沒有密碼的使用者，安裝完資料庫的時候會自動建立一些使用者，這些使用者預設沒有密碼

• 為每個使用者設定滿足密碼複雜度的密碼

• 定期清理不需要的使用者，回收許可權或者刪除使用者