MySQL使用者和資料許可權管理詳解

1、管理使用者

1.1、新增使用者

可以使用CREATE USER語句新增一個或多個使用者，並設定相應的密碼

語法格式：

CREATE USER 使用者名稱 [IDENTIFIED BY [PASSWORD]'密碼']

CREATE USER用於建立新的MySQL賬戶。CREATE USER會在系統本身的mysql資料庫的user表中新增一個新記錄。要使用CREATE USER，必須擁有mysql資料庫的全域性CREATE USER許可權或INSERT許可權。如果賬戶已經存在，則出現錯誤（報錯）。

例：新增一個新使用者usr1，密碼為123456 

CREATE USER usr1@localhost IDENTIFIED BY'123456';

在使用者名稱的後面宣告了關鍵字localhost。該關鍵字指定使用者建立所使用的MySQL伺服器來自於主機。如果一個使用者名稱和主機名中包含特殊符號_或萬用字元%，則需要用單引號將其括起，%表示一組主機。

1.2、刪除使用者

語法格式：

DROP USER 使用者名稱

要使用該語句，必須有mysql資料庫的全域性CREATE USER許可權或DELETE許可權。DROP USER語句用於刪除一個或多個MySQL賬戶，並取消其許可權。

例：刪除使用者usr1 

DROP USER usr1@localhost;

1.3、修改使用者名稱

語法格式：

RENAME USER 舊使用者名稱 TO 新使用者名稱

要使用該語句，必須有全域性CREATE USER許可權或mysql資料庫的UPDATE許可權。如果舊賬戶不存在或者新賬戶已存在，則會出現錯誤（報錯）。該語句用於對原有MySQL賬戶進行重新命名，可以一次對多個使用者更名。

例：將使用者usr1和usr2的名字分別修改為user1和user2 

RENAME USER
usr1@localhost TO user1@localhost,
usr2@localhost TO user2@localhost;

1.4、修改密碼

語法格式：

SET PASSWORD [ FOR 使用者名稱 ]=PASSWORD（'新密碼'）

如果不加FOR使用者名稱，表示修改當前使用者的密碼。加了FOR使用者名稱則是修改當前主機上的特定使用者的密碼。使用者名稱的值必須以user_name@host_name的格式給定。

例：將使用者user1的密碼修改為king 

SET PASSWORD FOR user1@localhost=PASSWORD('king');

2、授予許可權和回收許可權

2.1、授予許可權

新的SQL使用者不允許存取屬於其他SQL使用者的表，也不能立即建立自己的表，它必須被授權，可以授予的許可權有以下幾組：

1、列許可權：和表中的一個具體列相關。例如，使用UPDATE語句更新表Book書號列值的許可權。

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

3、資料庫許可權：和一個具體的資料庫中所有表相關。例如，在已有的Bookstore資料庫中建立新表的許可權。

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

給某使用者授予許可權可以使用GRANT語句。使用SHOW GRANTS語句可以檢視當前賬戶擁有的許可權。

語法格式：

GRANT 許可權1[(列名列表1)][,許可權2[(列名列表2)]...
ON [目標]{表名 | * | *.* | 庫名.*}
TO 使用者1 [IDENTIFIED BY [PASSWORD]'密碼1']
[,使用者2 [IDENTIFIED BY [PASSWORD]'密碼2']]...
[WITH 許可權限制1[許可權限制2]...]

2.1.1、授予表許可權

授予表許可權時，許可權可以是以下值：

1、SELECT：授予使用者使用SELECT語句存取特定表（或檢視）的權力。對於檢視，使用者必須對檢視中指定的每個表（或檢視）都有SELECT許可權。

2、INSERT：授予使用者使用INSERT語句向一個特定表中新增行的權力

3、DELETE：授予使用者使用DELETE語句向一個特定表中刪除行的權力

4、UPDATE：授予使用者使用UPDATE語句修改特定表中值的權力

5、REFERENCES：授予使用者建立一個外來鍵來參照特定的表的權力

6、CREATE：授予使用者使用特定的名字建立一個表的權力

7、ALTER：授予使用者使用ALTER TABLE語句修改表的權力

8、INDEX：授予使用者在表上定義索引的權力

9、DROP：授予使用者刪除表的權力

10、ALL或ALL PRIVILEGES：表示所有許可權

在授予表許可權時，ON關鍵字後面跟表名，指定授予許可權的為表名或檢視名

例：授予使用者user1在Book表上的SELECT許可權 

USE Bookstore;
GRANT SELECT
ON Book
TO user1@localhost;

這裡假設是在root使用者輸入了這些語句，這樣使用者user1就可以使用SELECT語句來查詢Book表，而不管是誰建立了該表。

若在TO子句中給存在的使用者指定密碼，則新密碼將原密碼覆蓋。如果許可權授予了一個不存在的使用者，MySQL會自動執行一條CREATE USER語句來建立這個使用者，但必須為該使用者指定密碼。

例：使用者liu和zhang不存在，授予他們在Book表上的SELECT和UPDATE許可權 

GRANT SELECT,UPDATE
ON Book
TO liu@localhost IDENTIFIED BY'123456',
zhang@localhost IDENTIFIED BY'123';

2.1.2、授予列許可權

對於列許可權，許可權的值只能取SELECT、INSERT和UPDATE。許可權的後面需要加上列名列表。

例：授予user1在Book表上的圖書編號列和書名列的UPDATE許可權。

GRANT UPDATE（圖書編號,書名）
ON Book
TO user1@localhost;

2.1.3、授予資料庫許可權

表許可權適用於一個特定的表，MySQL還支援針對整個資料庫的許可權。授予資料庫許可權時，許可權可以是以下值：

1、SELECT：授予使用者使用SELECT語句存取特定資料庫中所有表和檢視的權力

2、INSERT：授予使用者使用INSERT語句向特定資料庫所有表中新增行的權力

3、DELETE：授予使用者使用DELETE語句在特定資料庫所有表中刪除行的權力

4、UPDATE：授予使用者使用UPDATE語句更新特定資料庫所有表中值的權力

5、REFERENCES：授予使用者建立指向特定資料庫中的表外來鍵的權力

6、CREATE：授予使用者使用CREATE TABLE語句在特定資料庫中建立新表的權力

7、ALTER：授予使用者使用ALTER TABLE語句修改特定資料庫中所有表的結構的權力

8、INDEX：授予使用者在特定資料庫中的所有表上定義和刪除索引的權力

9、DROP：授予使用者刪除特定資料庫中所有表和檢視的權力

10、CREATE TEMPORARY TABLES：授予使用者在特定資料庫中建立臨時表的權力

11、CREATE VIEW：授予使用者在特定資料庫中建立新檢視的權力

12、SHOW VIEW：授予使用者檢視特定資料庫中已有檢視的檢視定義的權力

13、CREATE ROUTINE：授予使用者為特定資料庫建立儲存過程和儲存函數的權力

14、ALTER ROUTINE：授予使用者更新和刪除資料庫中已有儲存過程和儲存函數的權力

15、EXECUTE ROUTINE：授予使用者呼叫特定資料庫的儲存過程和儲存函數的權力

16、LOOK TABLES：授予使用者鎖定特定資料庫中已有表的權力

17、ALL或ALL PRIVILEGES：表示以上所有許可權

在GRANT語法格式中，授予資料庫許可權時ON關鍵字後面跟*和“庫名.*”。*表示當前資料庫中的所有表。“庫名.*”表示某個資料庫中的所有表。

例：授予user1在Bookstore資料庫中所有表的SELECT許可權 

GRANT SELECT
ON Bookstore.*
TO user1@localhost;

2.1.4、授予使用者許可權

最有效率的許可權就是使用者許可權，可以將授予資料庫的許可權直接授予使用者，使使用者獲得對伺服器上所有資料庫的該許可權。

MySQL授予使用者許可權時許可權還可以是以下值：

1、CREATE USER：授予使用者建立和刪除新使用者的許可權

2、SHOW DATABASES：授予使用者使用SHOW DATABASES語句檢視所有已有資料庫的定義的權力

在GRANT語法格式中，授予使用者許可權時ON子句中使用“*.*”，表示所有資料庫的所有表

例：授予user2對所有資料庫中所有表的CREATE、ALTERT和DROP許可權 

GRANT CREATE,ALTER,DROP
ON *.*
TO user2@localhost IDENTIFIED BY'123456';

例：授予user2建立新使用者的許可權

GRANT CREATE USER
ON *.*
TO user2@localhost;

2.2、許可權的轉移和限制

GRANT語句的最後可以使用WITH子句。如果指定許可權限制為GRANT OPTION，則表示TO子句中指定的所有使用者都有把自己所擁有的許可權授予其他使用者的權力，而不管其他使用者是否擁有該許可權。

 例：授予user3在Book表上的SELECT許可權，並允許其將該許可權授予其他使用者

GRANT SELECT
ON Bookstore.Book
TO user3@localhost IDENTIFIED BY'123456'
WITH GRANT OPTION;

使用了WITH GRANT OPTION子句後，如果user3在該表上還擁有其他許可權，他可以將其他許可權也授予其他使用者而不僅限於SELECT。

WITH子句後的許可權限制也可以對一個使用者授予使用限制，其中，MAX_QUERIES_PER_HOUR次數表示每小時可以查詢資料庫的次數。

MAX_CONNECTIONS_PER_HOUR次數表示每小時可以連線資料庫的次數。

MAX_UPDATES_PES_HOUR次數表示每小時可以修改資料庫的次數。

MAX_USER_CONNECTIONS次數表示同時連線MySQL的最大使用者數。

次數是一個數值，對於前3個許可權限制指定，次數如果為0則表示不起限制作用。

例：授予D每小時只能處理一條SELECT語句的許可權 

GRANT SELECT
ON Book
TO D@localhost
WITH MAX_QUERIES_PER_HOUR 1;

2.3、回收許可權

要從一個使用者回收許可權，但不從mysql資料庫的user表中刪除該使用者，可以使用REVOKE語句，該語句和GRANT語句格式相似，但具有相反的效果。要使用REVOKE語句，使用者必須擁有mysql資料庫的全域性CREATE USER許可權或UPDATE許可權。

語法格式：

REVOKE 許可權[（列名列表）]...
ON {表名 | * | *.* | 庫名.* }
FROM 使用者...  

或

REVOKE ALL PRIVILEGES,GRANT OPTION FROM 使用者

第一種格式用來回收使用者的某些特定許可權，第二種格式回收使用者的所有許可權

例：回收使用者user在Book表上的SELECT許可權

REVOKE SELECT
ON Book
FROM user@localhost;

到此這篇關於MySQL使用者和資料許可權管理詳解的文章就介紹到這了,更多相關MySQL許可權管理內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！