授權管理 · MySQL DBA

**MySQL 創建用戶與授權方法** [TOC] #### 一、創建用戶: 命令: `CREATE USER 'username'@'host' IDENTIFIED BY 'password'; ` 說明: * username - 你將創建的用戶名; * host - 指定該用戶在哪個主機上可以登陸,如果是本地用戶可用localhost, 如果想讓該用戶可以從任意遠程主機登陸,可以使用通配符%. * password - 該用戶的登陸密碼,密碼可以為空,如果為空,則用戶可以不需要密碼登陸服務器. 例子: ~~~ CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; CREATE USER 'pig'@'192.168.1.101' IDENDIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY ''; CREATE USER 'pig'@'%'; ~~~ #### 二、授權: 命令:`GRANT privileges ON databasename.tablename TO 'username'@'host' ` 說明: * privileges - 用戶的操作權限,如SELECT , INSERT , UPDATE 等(詳細列表見該文最后面).如果要授予所的權限則使用ALL.; * databasename - 數據庫名； * tablename-表名,如果要授予該用戶對所有數據庫和表的相應操作權限則可用*表示, 如*.*. 例子: ~~~ GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; GRANT ALL ON *.* TO 'pig'@'%'; ~~~ 注意: A. 用以上命令授權的用戶不能給其它用戶授權,如果想讓該用戶可以授權,用以下命令: `GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION; ` B. 設置權限時必須給出以下信息： 1. 要授予的權限 2. 被授予訪問權限的數據庫或表 3. 用戶名 grant和revoke可以在幾個層次上控制訪問權限 1. 整個服務器，使用 grant ALL 和revoke ALL 2. 整個數據庫，使用 on database.* 3. 特定的表，使用 on database.table 4. 特定的列 5. 特定的存儲過程命令中的 host 列的值的意義 |值|含義| |---|---| | % | 匹配所有主機| |localhost |localhost不會被解析成IP地址，直接通過UNIXsocket連接| |127.0.0.1 |會通過TCP/IP協議連接，并且只能在本機訪問；| |::1 | ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1| grant 普通數據用戶，查詢、插入、更新、刪除數據庫中所有表數據的權利。 ~~~ grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%' grant update on testdb.* to common_user@'%' grant delete on testdb.* to common_user@'%' ~~~ 或者，用一條 MySQL 命令來替代： ~~~ grant select, insert, update, delete on testdb.* to common_user@’%’ ~~~ #### 三、設置與更改用戶密碼命令:`SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');` 如果是當前登陸用戶用 ~~~ SET PASSWORD = PASSWORD("newpassword"); ~~~ 例子: `SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456"); ` #### 四、撤銷用戶權限命令: `REVOKE privilege ON databasename.tablename FROM 'username'@'host'; ` 說明: privilege, databasename, tablename - 同授權部分. 例子: `REVOKE SELECT ON *.* FROM 'pig'@'%'; ` 注意: 假如你在給用戶'pig'@'%'授權的時候是這樣的(或類似的):GRANT SELECT ON test.user TO 'pig'@'%', 則在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤銷該用戶對test數據庫中user表的SELECT 操作.相反,如果授權使用的是GRANT SELECT ON *.* TO 'pig'@'%';則REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤銷該用戶對test數據庫中user表的Select 權限. 具體信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看. #### 五、刪除用戶命令: `DROP USER 'username'@'host'; ` 附表:MySQL中的操作權限 |操作名稱|說明| |----|----| |ALTER| Allows use of ALTER TABLE.| |ALTER ROUTINE| Alters or drops stored routines.| |CREATE| Allows use of CREATE TABLE.| |CREATE ROUTINE |Creates stored routines.| |CREATE TEMPORARY TABLE| Allows use of CREATE TEMPORARY TABLE.| |CREATE USER| Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.| |CREATE VIEW| Allows use of CREATE VIEW.| |DELETE| Allows use of DELETE.| |DROP| Allows use of DROP TABLE.| |EXECUTE| Allows the user to run stored routines.| |FILE| Allows use of SELECT... INTO OUTFILE and LOAD DATA INFILE.| |INDEX| Allows use of CREATE INDEX and DROP INDEX.| |INSERT |Allows use of INSERT.| |LOCK TABLES| Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.| |PROCESS |Allows use of SHOW FULL PROCESSLIST.| |RELOAD |Allows use of FLUSH.| | REPLICATION | Allows the user to ask where slave or master CLIENT servers are. | |REPLICATION SLAVE| Needed for replication slaves.| |SELECT| Allows use of SELECT.| |SHOW DATABASES| Allows use of SHOW DATABASES.| |SHOW VIEW| Allows use of SHOW CREATE VIEW.| |SHUTDOWN| Allows use of mysqladmin shutdown.| |SUPER| Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.| |UPDATE| Allows use of UPDATE.| |USAGE| Allows connection without any specific privileges.| * * * * * 參考資料：http://www.jb51.net/article/31850.htm http://www.cnblogs.com/fslnet/p/3143344.html