mysql使用者許可權管理

一、使用者管理

使用者許可權相關的表

1 、 --user

user 表是 MySQL 中最重要的一個許可權表，用來記錄允許連線到伺服器的賬號資訊。需要注意的是，在 user 表裡啟用的所有許可權都是全域性級的，適用於所有資料庫。

mysql> select Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from user;

 

2 、 --db

db 表比較常用，是 MySQL 資料庫中非常重要的許可權表，表中儲存了使用者對某個資料庫的操作許可權

mysql> select Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from db;

 

3 、 --tables_priv

tables_priv 表用來對單個表進行許可權設定

mysql> select Host,Db,User,Table_name,Grantor,Table_priv,Column_priv from tables_priv;

 

4 、 --columns_priv

columns_priv 表用來對單個資料列進行許可權設定

mysql> select Host,Db,User,Table_name,Column_name,Column_priv from columns_priv;

5 、 --procs_priv

procs_priv 表可以對儲存過程和儲存函式進行許可權設定

mysql> select Host,Db,User,Table_name,Column_name,Column_priv from columns_priv;

 

建立使用者

MySQL 在安裝時，會預設建立一個名為 root 的使用者，該使用者擁有超級許可權，可以控制整個 MySQL 伺服器。為了避免有人惡意使用 root 使用者控制資料庫，我們通常建立一些具有適當許可權的使用者。

MySQL 提供了以下 3 種方法建立使用者。

l  使用 CREATE USER 語句建立使用者

l  在 mysql.user 表中新增使用者

l  使用 GRANT 語句建立使用者

新建立的使用者擁有的許可權很少，它們只能執行不需要許可權的操作。如登入 MySQL 、使用 SHOW 語句查詢所有儲存引擎和字符集的列表等。如果兩個使用者的使用者名稱相同，但主機名不同， MySQL 會將它們視為兩個使用者，並允許為這兩個使用者分配不同的許可權集合。

1 、 CREATE USER 語句

可以使用 CREATE USER  語句來建立 MySQL 使用者，並設定相應的密碼。其基本語法格式如下：

CREATE USER < 使用者 > [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ , 使用者 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

例子：

mysql> CREATE USER 'test1'@'%' identified by 'test1','test2'@'%' identified by 'test2';

 

在實際應用中，我們應避免明文指定密碼，可以透過 PASSWORD 關鍵字使用密碼的雜湊值設定密碼。

mysql> SELECT password('test3');

 

mysql> create user 'test3'@'%' identified by password '*F357E78CABAD76FD3F1018EF85D78499B6ACC431';

使用test3/test3 登入成功

 

 

2、 在 mysql.user 表中新增

可以使用 INSERT 語句將使用者的資訊新增到 mysql.user 表中，但必須擁有對 mysql.user 表的 INSERT 許可權。通常 INSERT 語句只新增 Host 、 User 和  authentication_string 這 3 個欄位的值。

MySQL 5.7 的 user 表中的密碼欄位從 Password 變成了 authentication_string ，如果你使用的是 MySQL 5.7 之前的版本，將 authentication_string 欄位替換成 Password 即可。

使用 INSERT  語句建立使用者的程式碼如下：

INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('hostname', 'username', PASSWORD('password'), '', '', '');

由於 mysql 資料庫的 user 表中， ssl_cipher 、 x509_issuer 和 x509_subject 這 3 個欄位沒有預設值，所以向 user 表插入新記錄時，一定要設定這 3 個欄位的值，否則 INSERT 語句將不能執行。

例子：

mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('%', 'test4', PASSWORD('test4'), '', '', '');

 

使用 insert 語句新增使用者後需要重新整理許可權才會生效

mysql> flush privileges;

3、 使用 GRANT 語句 （推薦該方法）

雖然 CREATE USER 和 INSERT INTO 語句都可以建立普通使用者，但是這兩種方式不便授予使用者許可權。於是 MySQL 提供了 GRANT 語句。

使用 GRANT 語句建立使用者的基本語法形式如下 :

GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']

其中：

l  priv_type 參數列示新使用者的許可權；

l  database.table 參數列示新使用者的許可權範圍，即只能在指定的資料庫和表上使用自己的許可權；

l  user 引數指定新使用者的賬號，由使用者名稱和主機名構成；

l  IDENTIFIED BY 關鍵字用來設定密碼；

l  password 參數列示新使用者的密碼，新建使用者必須同時指定密碼。

例子：

mysql> grant all privileges on *.* to 'test5'@'%' identified by 'test5';

 

Grant 語句也可以用於授權、修改使用者密碼。

修改使用者

在 MySQL 中，我們可以使用 RENAME USER 語句修改一個或多個已經存在的使用者賬號。

語法格式如下：

RENAME USER < 舊使用者 > TO < 新使用者 >

其中：

< 舊使用者 > ：系統中已經存在的 MySQL 使用者賬號。

< 新使用者 > ：新的 MySQL 使用者賬號。

使用 RENAME USER 語句時應注意以下幾點：

l  RENAME USER 語句用於對原有的 MySQL 使用者進行重新命名。

l  若系統中舊賬戶不存在或者新賬戶已存在，該語句執行時會出現錯誤。

l  使用 RENAME USER 語句，必須擁有 mysql 資料庫的 UPDATE 許可權或全域性 CREATE USER 許可權。

例子：

mysql> rename user 'test1'@'%' to 'test1s'@'%';

 

刪除使用者

在 MySQL 資料庫中，可以使用 DROP USER 語句刪除使用者，也可以直接在 mysql.user 表中刪除使用者以及相關許可權。

1. 使用 DROP USER 語句刪除普通使用者

使用 DROP USER 語句刪除使用者的語法格式如下：

DROP USER < 使用者 1> [ , < 使用者 2> ] …

其中，使用者用來指定需要刪除的使用者賬號。

使用 DROP USER 語句應注意以下幾點：

l  DROP USER 語句可用於刪除一個或多個使用者，並撤銷其許可權。

l  使用 DROP USER 語句必須擁有 mysql 資料庫的 DELETE 許可權或全域性 CREATE USER 許可權。

l  在 DROP USER 語句的使用中，若沒有明確地給出賬戶的主機名，則該主機名預設為“ % ”。

注意：使用者的刪除不會影響他們之前所建立的表、索引或其他資料庫物件，因為 MySQL 並不會記錄是誰建立了這些物件。

例子：

mysql> drop user test1s;

mysql> drop user test;

 

mysql> drop user 'test'@'localhost';

 

2. 使用 DELETE 語句刪除普通使用者

可以使用 DELETE 語句直接刪除 mysql.user 表中相應的使用者資訊，但必須擁有 mysql.user 表的 DELETE 許可權。其基本語法格式如下：

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

Host 和 User 這兩個欄位都是 mysql.user 表的主鍵。因此，需要兩個欄位的值才能確定一條記錄。

二、許可權管理

許可權檢視

在 MySQL 中，可以透過檢視 mysql.user 表中的資料記錄來檢視相應的使用者許可權，也可以使用 SHOW GRANTS 語句查詢使用者的許可權。

1 、使用 select 檢視許可權

mysql 資料庫下的 user 表中儲存著使用者的基本許可權，可以使用 SELECT 語句來檢視 , 相關的表如下 ：

SELECT * FROM mysql.user;

Select * from mysql.db;

Select * from tables_priv;

Select * from columns_priv;

Select * from procs_priv;

查詢上述表，必須擁有對相關表的查詢許可權。

常用查詢：

mysql> select Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from user;

mysql> select Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from db;

mysql> select Host,Db,User,Table_name,Grantor,Table_priv,Column_priv from tables_priv;

mysql> select Host,Db,User,Table_name,Column_name,Column_priv from columns_priv;

mysql> select Host,Db,User,Table_name,Column_name,Column_priv from columns_priv;

可根據需要調整。

新建立的使用者只有登入 MySQL 伺服器的許可權，沒有任何其它許可權，不能查詢 user 表。

2 、使用 show grant 檢視許可權

使用 SHOW GRANTS FOR 語句檢視許可權。其語法格式如下：

> SHOW GRANTS FOR 'username'@'hostname';

其中，username 表示使用者名稱， hostname 表示主機名或主機 IP ，未指定hostname 的情況下， mysql 預設主機名為 ’ % ’ 。

例子：

mysql> show grants for test2;

mysql> show grants for test2@'localhost';

 

授權

在 MySQL 中，擁有 GRANT 許可權的使用者才可以執行 GRANT 語句，其語法格式如下：

GRANT priv_type [(column_list)] ON database.table

TO user [IDENTIFIED BY [PASSWORD] 'password']

[, user[IDENTIFIED BY [PASSWORD] 'password']] ...

[WITH with_option [with_option]...]

其中：

l  priv_type 參數列示許可權型別；

l  columns_list 參數列示許可權作用於哪些列上，省略該引數時，表示作用於整個表；

l  database.table 用於指定許可權的級別；

l  user 參數列示使用者賬戶，由使用者名稱和主機名構成，格式是“ 'username'@'hostname' ”；

l  IDENTIFIED BY 引數用來為使用者設定密碼；

l  password 引數是使用者的新密碼。

 

WITH 關鍵字後面帶有一個或多個 with_option 引數。這個引數有 5 個選項，詳細介紹如下：

l  GRANT OPTION ：被授權的使用者可以將這些許可權賦予給別的使用者；

l  MAX_QUERIES_PER_HOUR count ：設定每個小時可以允許執行 count 次查詢；

l  MAX_UPDATES_PER_HOUR count ：設定每個小時可以允許執行 count 次更新；

l  MAX_CONNECTIONS_PER_HOUR count ：設定每小時可以建立 count 個連線 ;

l  MAX_USER_CONNECTIONS count ：設定單個使用者可以同時具有的 count 個連線。

 

MySQL 中可以授予的許可權有如下幾組：

l  列許可權，和表中的一個具體列相關。例如，可以使用 UPDATE 語句更新表 students 中 name 列的值的許可權。

l  表許可權，和一個具體表中的所有資料相關。例如，可以使用 SELECT 語句查詢表 students 的所有資料的許可權。

l  資料庫許可權，和一個具體的資料庫中的所有表相關。例如，可以在已有的資料庫 mytest 中建立新表的許可權。

l  使用者許可權，和 MySQL 中所有的資料庫相關。例如，可以刪除已有的資料庫或者建立一個新的資料庫的許可權。

 

對應地，在 GRANT 語句中可用於指定許可權級別的值有以下幾類格式：

l  * ：表示當前資料庫中的所有表。

l  *.* ：表示所有資料庫中的所有表。

l  db_name.* ：表示某個資料庫中的所有表， db_name 指定資料庫名。

l  db_name.tbl_name ：表示某個資料庫中的某個表或檢視， db_name 指定資料庫名， tbl_name 指定表名或檢視名。

l  db_name.routine_name ：表示某個資料庫中的某個儲存過程或函式， routine_name 指定儲存過程名或函式名。

TO 子句： 如果許可權被授予給一個不存在的使用者，MySQL 會自動執行一條 CREATE USER 語句來建立這個使用者，但同時必須為該使用者設定密碼 。

例子

1 、授權使用者許可權

mysql>  select Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from user;

 

2 、授權資料庫許可權

mysql> grant select on testdb.* to test6;

3 、授權表許可權

mysql> grant select on testdb.tb1 to test3@'%';

 

4 、授權欄位許可權

mysql> grant select (id,vname) on testdb.tb1 to test5@'%';

mysql> grant select (id) on testdb.tb1 to test2@'%';

5 、授權過程（函式）許可權

 

回收許可權

在 MySQL 中，可以使用 REVOKE 語句刪除某個使用者的某些許可權（此使用者不會被刪除），在一定程度上可以保證系統的安全性

使用 REVOKE 語句刪除許可權的語法格式有兩種形式，如下所示：

1 ）第一種

刪除使用者 某些特定的許可權 ，語法格式如下：

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

REVOKE 語句中的引數與 GRANT 語句的引數意思相同。其中：

priv_type 參數列示許可權的型別；

column_list 參數列示許可權作用於哪些列上，沒有該引數時作用於整個表上；

user 引數由使用者名稱和主機名構成，格式為 “username'@'hostname'” 。

例子：

 

mysql> revoke SELECT (id) ON `testdb`.`tb1` from 'test2'@'%';

 

2 ）第二種

刪除特定 使用者的所有許可權 ，語法格式如下：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

例子：

 

mysql> revoke all privileges on *.* from 'test5'@'%';

刪除使用者許可權需要注意以下幾點：

REVOKE 語法和 GRANT 語句的語法格式相似，但具有相反的效果。

要使用 REVOKE 語句，必須擁有 MySQL 資料庫的全域性 CREATE USER 許可權或 UPDATE 許可權。 USAGE 表示沒有任何許可權。

 

三、密碼修改

在 MySQL 中，只有 root 使用者可以透過更新 MySQL 資料庫來更改密碼 ，主要有以下三種方式。

1 、 Set 語句

基本語法：

SET PASSWORD FOR 'username'@'hostname' = PASSWORD ('newpwd');

注意：新密碼必須使用 PASSWORD() 函式來加密，如果不使用 PASSWORD() 加密，也會執行成功，但是使用者會無法登入。

如果是普通使用者修改密碼，可省略 FOR 子句來更改自己的密碼。語法格式如下：

SET PASSWORD = PASSWORD('newpwd');

例子：

mysql> set password for test4@'localhost'=password('test4');

 

2 、 Update 語句

使用 root 使用者登入 MySQL 伺服器後，可以使用 UPDATE 語句修改 MySQL 資料庫的 user 表的 authentication_string 欄位，從而修改普通使用者的密碼。 UPDATA 語句的語法如下：

UPDATE MySQL.user SET authentication_string = PASSWORD("newpwd") WHERE User = "username" AND Host = "hostname";

注意，執行 UPDATE 語句後，需要執行 FLUSH PRIVILEGES 語句重新載入使用者許可權。

例子：

mysql> update user set authentication_string=password('test4new') where user='test4' and host='localhost';

 

 

 

3 、 Grant 語句

可以在全域性級別使用 GRANT USAGE （表示不變更許可權）   語句指定某個賬戶的密碼而不影響賬戶當前的許可權。需要注意的是，使用 GRANT 語句修改密碼，必須擁有 GRANT 許可權。一般情況下最好使用該方法來指定或修改密碼。語法格式如下：

GRANT USAGE ON *.* TO 'user'@’hostname’ IDENTIFIED BY 'newpwd';

例子：
mysql> grant usage on *.* to test4@localhost identified by 'test4';

 

4 、 mysqladmin 工具

root 使用者可以使用 mysqladmin 命令來修改密碼， mysqladmin 的語法格式如下：

mysqladmin -u username -h hostname -p password "newpwd"

語法引數說明如下：

usermame 指需要修改密碼的使用者名稱稱，在這裡指定為 root 使用者；

hostname 指需要修改密碼的使用者主機名，該引數可以不寫，預設是 localhost ；

password 為關鍵字，而不是指舊密碼；

newpwd 為新設定的密碼，必須用雙引號括起來。如果使用單引號會引發錯誤，可能會造成修改後的密碼不是你想要的。
執行完上面的語句，root 使用者的密碼將被修改為 “newpwd” 。

例子：

[root@lxgmc2 ~]# mysqladmin -u root -p password "rootnew";

 

5 、 root 忘記密碼重置

/etc/my.cnf 增加配置 skip-grant-tables=1 ，啟用無密碼登入

重啟mysql 服務並登入

service mysqld restart

透過上述方法(set 、 update 、 grant) 修改 root 密碼，修改完成後關閉無密碼登入，重啟 mysql 。