涉及MySQL用戶及權限管理的簡單介紹

本文主要給大家介紹MySQL用戶及權限管理，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下MySQL用戶及權限管理吧。 

MySQL用戶及權限管理

MySQL 默認管理員用戶為root，但是這個用戶權限太大，一般只在管理數據庫時候才用。為了MySQL的安全性，在實際應用中用戶權限要最小化，應該只分配使用到的權限。

1 mysql授權表

MySQL云服務器通過MySQL權限表來控制用戶對數據庫的訪問，MySQL權限表存放在mysql數據庫里，由mysql_install_db腳本初始化。這些MySQL權限表分別user，db，table_priv，columns_priv和host。下面分別介紹一下這些表的結構和內容：

• user權限表：記錄允許連接到云服務器的用戶帳號信息，里面的權限是全局級的。
• db權限表：記錄各個帳號在各個數據庫上的操作權限。
• table_priv權限表：記錄數據表級的操作權限。在這里指定的一個權限適用于一個表的所有列。
• columns_priv權限表：記錄數據列級的操作權限；這里指定的權限適用于一個表的特定列；
• host權限表：配合db權限表對給定主機上數據庫級操作權限作更細致的控制。這個權限表不受GRANT和REVOKE語句的影響。

2 用戶及密碼管理

2.1 創建用戶

命令:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• 新用戶創建完成暫時無法登陸，因為我們還沒有為這個用戶分配相應權限，

說明：

• username：你將創建的用戶名
• host：指定該用戶在哪些主機上可以登陸，本地用戶用localhost，如果想讓該用戶可以從任意遠程主機登陸，可以使用通配符%
• password：該用戶的登陸密碼，密碼可以為空，如果為空則該用戶可以不需要密碼登陸云服務器
• 不管是授權，還是撤銷授權，都要指定響應的host（即 @ 符號后面的內容），因為以上及格命令實際上都是在操作mysql 數據庫中的user表;

例：

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101' IDENDIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

2.2 密碼設置

設置root密碼：

設置密碼：password1

mysqladmin -uroot -password password1

修改root密碼：

由password1修改為password2

mysqladmin -uroot -p password1 password password2

管理員設置其它用戶密碼

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

設置當前登陸用戶（自己）密碼:

SET PASSWORD = PASSWORD("newpassword");

2.3 查看當前用戶

SELECT USER();

2.4 刪除用戶

DROP USER 'username'@'host';

2.5 查看mysql所有用戶

查看mysql的所有用戶及權限、加密密碼、最后修改密碼時間；

• MYSQL-5.6

select host,user,password from mysql.user;

• MYSQL-5.7

SELECT user,host,authentication_string,password_last_changed FROM mysql.user;

2.6 數據表結構:

DESCRIBE table1;

3 權限

3.1 授權GRANT

GRANT 語法:

GRANT privileges (columns)
ON what
TO user IDENTIFIED BY "password"
WITH GRANT OPTION

權限列表:

• CREATE：創建數據庫和表。
• INSERT：插入；向表中插入新行。
• DELETE：刪除表中已有的記錄。
• DROP：拋棄(刪除)數據庫和表。
• ALTER：修改表和索引。
• INDEX：創建或拋棄索引。
• REFERENCE：未用。
• SELECT：檢索表中的記錄。
• UPDATE：修改現存表記錄。
• FILE：讀或寫云服務器上的文件。
• PROCESS：查看云服務器中執行的線程信息或殺死線程。
• RELOAD：重載授權表或清空日志、主機緩存或表緩存。
• SHUTDOWN：關閉云服務器。
• ALL：所有權限，ALL PRIVILEGES同義詞。
• USAGE：特殊的 "無權限" 權限。
• 用戶賬戶包括 "username" 和 "host" 兩部分，后者表示該用戶被允許從何地接入。tom@'%' 表示任何地址，默認可以省略。還可以是 "tom@192.168.1.%"、"tom@%.abc.com" 等。數據庫格式為 db@table，可以是 "test." 或 ".*"，前者表示 test 數據庫的所有表，后者表示所有數據庫的所有表。

• 子句 WITH GRANT OPTION表示該用戶可以為其他用戶分配權限。

• grant, revoke 用戶權限后，該用戶只有重新連接 MySQL 數據庫，權限才能生效。

只分配權限：和CREATE USER一起使用；

GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host';

說明:

• all：要授予用戶的操作權限，如SELECT，INSERT，UPDATE等，如果要授予所的權限則使用ALL；
• databasename：數據庫名；
• tablename：表名，如果要授予該用戶對所有數據庫和表的相應操作權限則可用表示，如.*
• username：用戶；
• host：IP范圍；

新建賬戶并分配權限：grant

GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host' IDENTIFIED BY 'password';

說明：

• 其它同上；
• password：密碼；

例子:

GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%';
GRANT SELECT,INSERT ON test.user TO 'pig'@'%';

新建賬戶并授予用戶管理其它用戶的權限

GRANT PRIVILEGES ON databasename.tablename TO 'username'@'localhost' WITH GRANT OPTION;

說明：

• 其它同上；
• WITH GRANT OPTION：授予用戶管理其它用戶的權限；
• WITH GRANT OPTION 一般配置的用戶的host配localhost，即只能本地登陸；

常用操作

• grant 查詢、插入、更新、刪除MySQL 數據表結構權限。

  grant select,insert,update,delete on databasename.tablename to 'username'@'host';

• grant 創建,卸載MySQL 數據表的權限。

  grant create,drop on databasename.tablename to 'username'@'host';

• grant 作用在表中的列上：

  grant select(id, se, rank) on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 外鍵權限。

  grant references on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 臨時表權限。

  grant create temporary tables on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 索引權限。

  grant index on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 視圖、查看視圖源代碼 權限。

  grant create view on databasename.tablename to 'username'@'host';
  grant show view on databasename.tablename to 'username'@'host';

• grant 操作 MySQL 存儲過程、函數 權限。

  grant create routine on databasename.tablename to 'username'@'host';
  grant alter routine on databasename.tablename to 'username'@'host';
  grant execute on databasename.tablename to 'username'@'host';
  grant execute on procedure testdb.pr_add to 'dba'@'localhost';
  grant execute on function testdb.fn_add to 'dba'@'localhost';

• grant 普通 DBA 管理某個 MySQL 數據庫的權限。

  grant all privileges on databasename.* to dba@'localhost';

• grant 高級 DBA 管理 MySQL 中所有數據庫的權限。

  grant all on *.* to dba@'localhost'

• 關鍵字 privileges 如果省略的話，后面需要刷新權限。

3.2 刷新權限

修改完權限以后 一定要刷新服務，或者重啟服務：

FLUSH PRIVILEGES;

3.3 查看用戶權限

SHOW GRANTS [FOR 'username'@'host'];

默認查看自己的權限，加for查看指定用戶的權限；

3.4 撤銷權限：revoke

如果此時發現剛剛給的權限太大了，如果我們只是想授予它在某個數據庫上的權限，那么需要切換到root 用戶撤銷剛才的權限，重新授權：

REVOKE ALL PRIVILEGES ON databasename.tablename FROM 'username'@'host';

說明:

• privilege, databasename, tablename：同授權部分

注意:
假如你在給用戶'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'@'%'; 查看。

4 摘要

4.1 grant和revoke可以在幾個層次上控制訪問權限

• 整個云服務器，使用 grant ALL 和revoke ALL
• 整個數據庫，使用on database.*
• 特定表，使用on database.table
• 特定的列
• 特定的存儲過程

4.2 user表中host列的值的意義

• %：匹配所有主機
• localhost： localhost不會被解析成IP地址，直接通過UNIXsocket連接
• 127.0.0.1： 會通過TCP/IP協議連接，并且只能在本機訪問；
• ::1 ::1：就是兼容支持ipv6的，表示同ipv4的127.0.0.1

4.3 也可以通過安全策略刪除無用的用戶

推薦剛裝完mysqld服務時在shell運行mysql_secure_installation命令

mysql_secure_installation

看完以上關于MySQL用戶及權限管理，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。