SQL - 07.User Management

[Admin1]

User Management

MySQL支援的Privilegeswww&slime+com-tw

• ALL或ALL PRIVILEGES代表除了GRANT OPTION以外所有的權限。
• USAGE代表沒有任何權限，只可以單純的連線到server。

不同權限可以在不同的層級使用：www&slime+com-tw

• global層級：所有的權限。
• database層級：CREATE TEMPORARY TABLES, LOCK TABLES, ALTER, CREATE, CREATE VIEW, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, SHOW VIEW, UPDATE。
• table層級：SELECT, INSERT, DELETE, UPDATE, INDEX, ALTER, CREATE, DROP, GRANT OPTION。
• column層級：SELECT, INSERT, UPDATE。
• routine層級：ALTER ROUTINE, EXECUTE, GRANT OPTION。

GRANT TABLES：www&slime+com-tw

1. host
2. db
3. tabless_priv
4. columns_priv
5. procs_priv

建立與刪除使用者：www&slime+com-tw

• CREATE USER 'slime'@'localhost' INDENTIFIED BY 'abc123';
• DROP USER 'slime'@'localhost';
• RENAME USER 'slime'@'localhost' TO 'slime2'@'www.example.com';

建立匿名使用者：

• CREATE USER ''@'localhost';
  註：在username中使用空字串代表建立匿名使用者，它可以match到所有的username(類似%的作用)。

在host part可以使用的格式：www&slime+com-tw

• localhost
• hostname(www.example.com)
• ip(192.168.1.0)
• pattern(%.example.com)
• ip/netmask(192.168.1.0/255.255.255.0)

註：

1. netmask只能使用8, 16, 24, 32 bit。
2. 若是省略host part則視同為%，例如'slime' 等同於'slime'@'%'。

Grant and Revoke Privileges

在ON子句中可以使用：www&slime+com-tw

• ON *.*
• ON db_name.*
• ON db_name.table_name
• ON db_name.routine_name

若是table, procedure, function使用相同的名稱，則在沒有特別指定的情況下視同指定給table。若是要明確的指定給procedure或function，則可以使用：

• ON TABLE db_name.table_name
• ON FUNCTION db_name.func_name
• ON PROCEDURE db_name.proc_name

指定column level的權限：
GRANT SELECT (ID, Name, CountryCode), UPDATE (name, CountryCode)
ON world.City to 'slime'@'localhost'
IDENTIFIED BY 'abc123';

註：啟用NO_AUTO_CREATE_USER SQL mode時，若GRANT指令中不包含IDENTIFIED BY，則無法建立新使用者(變更舊使用者的權限則不受影響)。

取消所有的權限，但不包含GRANT OPTION：
REVOKE ALL ON *.* FROM 'slime'@'localhost';

取消GRANT OPTION權限：
REVOKE GRANT OPTION ON *.* FROM 'slime'@'localhost';

註：REVOKE指令只會取消使用者的權限但不會刪除使用者的帳號。

變更密碼www&slime+com-tw

• SET PASSWORD FOR 'slime'@'localhost' = PASSWORD('abc123');
• GRANT USAGE ON *.* TO 'slime'@'localhost' IDENTIFIED BY 'abc123';

註：
SET PASSWORD = PASSWORD('abc123');
則是變更使用者自己本身的密碼。

變更權限後何時生效(假設已執行FLUSH PRIVILEGES)：

• table與column privileges在變更後即刻生效。
• database privileges在下次變更defaut database時生效。
• global privileges與password會在下次連線到server時生效。

Resource Limitswww&slime+com-tw

1. connections/h => MAX_CONNECTIONS_PER_HOUR
2. queries/h => MAX_QUERIES_PER_HOUR
3. updates/h => MAX_UPDATES_PER_HOUR
4. 同時最大連線數 => MAX_USER_CONNECTIONS

例如：
GRANT USAGE ON *.* TO 'slime'@'localhost'
WITH MAX_CONNECTIONS_PER_HOUR 20;

若要解除限制只要將其值設為0即可。但MAX_USER_CONNECTIONS設為0以後，client還是有可能會受到max_user_connections這個系統變數的限制。

執行各項權限管理操作所需的權限：www&slime+com-tw


Client Access Control

1. 第一階段：檢查user table以決定client是否有權連線至server，此時也會針對Resource Limit (connections/h, 同時最大連線數)進行驗證。
2. 第二階段：Resource Limit(queries/h, updates/h)的檢驗會先執行，然後再以grant table針對client提交的statement進行權限驗證。

Disabling Client Access Control
使用- -skip-grant-tables：www&slime+com-tw

• client可以從任何host、以任何username且不需提供密碼即可連線至Server的情況下，擁有所有的權限。
• 由於server啟動時並沒有載入grant table，因此所有對於in-memory grant tables的操作皆無法執行，例如CREATE USER、DROP USER、GRANT、REVOKE等。但只要使用FLUSH PRIVILEGES指令讓server載入grant table即可解決此問題。

參考資料：www^slime^com^tw
Paul Dubois, Stefan Hinz, Carsten Pedersen(2005), “MySQL 5.0 Certification Study Guide.”