MySQL用戶與權限管理介紹

172次閱讀

共計 13622 個字符，預計需要花費 35 分鐘才能閱讀完成。

這篇文章主要介紹“MySQL 用戶與權限管理介紹”，在日常操作中，相信很多人在 MySQL 用戶與權限管理介紹問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL 用戶與權限管理介紹”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

MySQL 權限系統(tǒng)的主要功能是證實連接到一臺給定主機的用戶，并且賦予該用戶在數(shù)據(jù)庫上的相關 DML、DQL 權限。MySQL 存取控制包含 2 個階段，一是服務器檢查是否允許連接；二是假定能連接，服務器檢查發(fā)出的每個請求?？词欠裼凶銐虻臋嘞迣嵤┧１疚闹饕枋?MySQL 權限系統(tǒng)相關的用戶創(chuàng)建、授權、撤銷權限等等。

1、獲取有關權限管理的幫助

root@localhost[(none)] help Account Management
For more information, type help , where is one of the following
topics:
You asked for help about help category: Account Management
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD

2、創(chuàng)建 mysql 數(shù)據(jù)庫用戶

– 創(chuàng)建用戶的語法
root@localhost[(none)] help create user;
Name: CREATE USER
Description:
Syntax:
CREATE USER user_specification [, user_specification] …

user_specification:
user
[
| IDENTIFIED WITH auth_plugin [AS auth_string]
IDENTIFIED BY [PASSWORD] password
]

create user 命令會創(chuàng)建一個新帳戶，同時也可以為其指定密碼。該命令將添加一條記錄到 user 表。
該命令僅僅授予 usage 權限。需要再使用 grant 命令進行進一步授權。也可以使用 grant 命令直接來創(chuàng)建賬戶見后續(xù)的相關演示。
下面是 mysql 官方手冊對 usage 的解釋。
The USAGE privilege specifier stands for“no privileges.”It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

– 當前演示環(huán)境
root@localhost[(none)] show variables like version
+—————+————+
| Variable_name | Value |
+—————+————+
| version | 5.5.39-log |
+—————+————+

– 創(chuàng)建新用戶 (未指定密碼)
root@localhost[(none)] create user fred @ localhost
Query OK, 0 rows affected (0.00 sec)

– 指定密碼創(chuàng)建新用戶,% 表示任意，即 frank 可以從任意主機訪問數(shù)據(jù)庫
root@localhost[(none)] create user frank @ % identified by frank
Query OK, 0 rows affected (0.00 sec)

3、使用 grant 授予權限

–grant 命令語法
root@localhost[mysql] help grant
Name: GRANT
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] …
ON [object_type] priv_level
TO user_specification [, user_specification] …
[REQUIRE {NONE | ssl_option [[AND] ssl_option] …}]
[WITH with_option …]

GRANT PROXY ON user_specification
TO user_specification [, user_specification] …
[WITH GRANT OPTION]

object_type:
TABLE
| FUNCTION
| PROCEDURE

user_specification:
user
[
| IDENTIFIED WITH auth_plugin [AS auth_string]
IDENTIFIED BY [PASSWORD] password
]

如何授權
a、需要指定授予哪些權限
b、權限應用在那些對象上 (全局，特定對象等)
c、授予給哪個帳戶
d、可以指定密碼 (可選項, 用此方式會自動創(chuàng)建用戶)

授權權限的范圍：
ON *.*
ON db_name.*
ON db_name.table_name
ON db_name.table_name.column_name
ON db_name.routine_name

– 權限一覽表，我們直接查詢 root 賬戶所有的權限，如下
–mysql 的權限相對于 Oracle 而言，相對簡單，而且也沒有涉及到角色方面的定義與配置
root@localhost[(none)] select * from mysql.user where user= root and host= localhost \G
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
1 row in set (0.00 sec)

– 說明，本文中描述的 mysql 提示符為 user@hostname[(dbname)]，不同的帳戶，不同的主機登錄會顯示不同。
– 其次，不同的提示符下所代表的用戶身份及權限。
– 查看當前的連接用戶
root@localhost[(none)] select current_user();
+—————-+
| current_user() |
+—————-+
| root@localhost |
+—————-+

– 查看當前帳戶的權限
root@localhost[(none)] show grants; – 該賬戶用于最高權限，帶有 WITH GRANT OPTION
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO root @ localhost WITH GRANT OPTION |
| GRANT PROXY ON @ TO root @ localhost WITH GRANT OPTION |
+———————————————————————+

SUSE11b:~ # mysql -ufred -p
Enter password:

fred@localhost[(none)] show grants;
+——————————————+
| Grants for fred@localhost |
+——————————————+
| GRANT USAGE ON *.* TO fred @ localhost |
+——————————————+

– 下面使用 root 賬戶給 fred 賦予權限 all privileges
root@localhost[(none)] grant all privileges on *.* to fred @ localhost
Query OK, 0 rows affected (0.01 sec)

root@localhost[(none)] flush privileges;
Query OK, 0 rows affected (0.00 sec)

fred@localhost[(none)] show grants;
+—————————————————+
| Grants for fred@localhost |
+—————————————————+
| GRANT ALL PRIVILEGES ON *.* TO fred @ localhost |
+—————————————————+

fred@localhost[(none)] use tempdb

fred@localhost[tempdb] create table tb_isam(id int,value varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.10 sec)

fred@localhost[tempdb] insert into tb_isam values (1, jack),(2, robin
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

fred@localhost[tempdb] commit;

– 下面的授權收到了錯誤提示，不能授權
fred@localhost[tempdb] grant select on tempdb.* to frank @ %
ERROR 1044 (42000): Access denied for user fred @ localhost to database tempdb

– 下面從 root session 來給之前創(chuàng)建的 frank 授權
– 授予 frank 在數(shù)據(jù)庫 tempdb 上所有對象的 select 權限
root@localhost[(none)] grant select on tempdb.* to frank @ %
Query OK, 0 rows affected (0.00 sec)

– 更新 cache 中的權限
root@localhost[(none)] flush privileges;
Query OK, 0 rows affected (0.00 sec)

– 從另外的主機使用 frank 賬戶登錄
suse11a:~ # mysql -ufrank -p -h272.16.6.89
Enter password:

– 此時 frank，此時已經(jīng)可以訪問了 tempdb 上的表 tb_isam
frank@172.16.6.89[(none)] select * from tempdb.tb_isam;
+——+——-+
| id | value |
+——+——-+
| 1 | jack |
| 2 | robin |
+——+——-+

frank@172.16.6.89[(none)] show grants;
+——————————————————————————————————+
| Grants for frank@% |
+——————————————————————————————————+
| GRANT USAGE ON *.* TO frank @ % IDENTIFIED BY PASSWORD *63DAA25989C7E01EB96570FA4DBE154711BEB361 |
| GRANT SELECT ON `tempdb`.* TO frank @ % – 可以看到多出了 select 權限 |
+——————————————————————————————————+

– 下面是一個授予最大權限的例子，授予的同時會自動創(chuàng)建用戶，由于我們沒有設置密碼，所以 password 列查詢結果為空
root@localhost[(none)] grant all privileges on *.* to jack @ localhost
Query OK, 0 rows affected (0.00 sec) – 第一個 * 號代表任意數(shù)據(jù)庫，第二個 * 號代表數(shù)據(jù)庫上的任意對象

suse11b:~ # mysql -ujack -p -h localhost
Enter password:

jack@localhost[(none)] show grants for current_user; – 該方式等同于 show grants，查看自身權限
+—————————————————+
| Grants for jack@localhost |
+—————————————————+
| GRANT ALL PRIVILEGES ON *.* TO jack @ localhost |
+—————————————————+

– 在當前 session 下查看其它用戶的權限，注，當前 session 登陸的用戶也需要有權限才能查看其它用戶權限
jack@localhost[(none)] show grants for frank @ %
+——————————————————————————————————+
| Grants for frank@% |
+——————————————————————————————————+
| GRANT USAGE ON *.* TO frank @ % IDENTIFIED BY PASSWORD *63DAA25989C7E01EB96570FA4DBE154711BEB361 |
| GRANT SELECT ON `tempdb`.* TO frank @ % |
+——————————————————————————————————+

– 下面演示基于對象列級別的授權
– 首先 revoke 之前的 select 權限
root@localhost[(none)] revoke select on tempdb.* from frank @ %
Query OK, 0 rows affected (0.00 sec)

fred@localhost[tempdb] create table tb_user as select * from mysql.user;
Query OK, 9 rows affected (0.15 sec)
Records: 9 Duplicates: 0 Warnings: 0

fred@localhost[tempdb] grant select(user,host),update(host) on tempdb.tb_user to frank @ %
ERROR 1142 (42000): GRANT command denied to user fred @ localhost for table tb_user – 授權失敗

– 下面使用 root 來授權
root@localhost[(none)] grant select(user,host),update(host) on tempdb.tb_user to frank @ %
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)] flush privileges;
Query OK, 0 rows affected (0.00 sec)

– 下面檢查一下 frank 所擁有的權限
root@localhost[(none)] show grants for frank
+——————————————————————————————————+
| Grants for frank@% |
+——————————————————————————————————+
| GRANT USAGE ON *.* TO frank @ % IDENTIFIED BY PASSWORD *63DAA25989C7E01EB96570FA4DBE154711BEB361 |
| GRANT SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` TO frank @ % |
+——————————————————————————————————+

frank@172.16.6.89[(none)] select * from tempdb.tb_user; – 訪問時不支持通配符，必須指定列名
ERROR 1142 (42000): SELECT command denied to user frank @ suse11a.site for table tb_user

frank@172.16.6.89[(none)] select host,user from tempdb.tb_user where user= frank
+——+——-+
| host | user |
+——+——-+
| % | frank |
+——+——-+

– 需要注意的是，如果你的對象創(chuàng)建在 test 相關數(shù)據(jù)庫下，權限限制可能會失效。
– 下面這個查詢用于查看 db 的授權表
root@localhost[(none)] select host,db,user from mysql.db;
+——+———+——+
| host | db | user |
+——+———+——+
| % | test | |
| % | test\_% | |
+——+———+——+

– 根據(jù)前面的權限授予, 列 host 可以被更新，而列 user 不行，如下面的 2 條 SQL 語句執(zhí)行的結果
frank@172.16.6.89[(none)] update tempdb.tb_user set host= localhost where user= frank
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0

frank@172.16.6.89[(none)] update tempdb.tb_user set user= jason where user= jack
ERROR 1143 (42000): UPDATE command denied to user frank @ suse11a.site for column user in table tb_user

– 關于 WITH GRANT OPTION
root@localhost[(none)] show grants; – 注意 root 下有 WITH GRANT OPTION
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO root @ localhost WITH GRANT OPTION |
| GRANT PROXY ON @ TO root @ localhost WITH GRANT OPTION |
+———————————————————————+

root@localhost[(none)] show grants for jack @ localhost – 注意 jack 下沒有 WITH GRANT OPTION
+—————————————————+ – 這就是前面為什么用戶自身創(chuàng)建的對象而無法授權的問題
| Grants for jack@localhost |
+—————————————————+
| GRANT ALL PRIVILEGES ON *.* TO jack @ localhost |
+—————————————————+

4、撤銷權限

撤銷權限使用的是 revoke 關鍵字，撤銷與授權的權限方式基本類似，
其次有哪些權限可以授予，相應地就有哪些權限可以撤銷，原來的 to 子句呢則變成了 from 子句。
如下面的示例
mysql revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from frank @ %
mysql revoke all privileges, grant option from frank @ %

root@localhost[(none)] revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from frank @ %
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)] revoke all privileges, grant option from frank @ %
Query OK, 0 rows affected (0.01 sec)

root@localhost[(none)] flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)] show grants for frank – 查看 revoke 之后僅擁有最基本權限
+——————————————————————————————————+
| Grants for frank@% |
+——————————————————————————————————+
| GRANT USAGE ON *.* TO frank @ % IDENTIFIED BY PASSWORD *63DAA25989C7E01EB96570FA4DBE154711BEB361 |
+——————————————————————————————————+

5、刪除及重命名賬戶