MySQL權限系統的主要功能是證實連接到一臺給定主機的用戶，并且賦予該用戶在數據庫上的相關DML,DQL權限。MySQL存取控制包含2個階段，一是服務器檢查是否允許你連接;二是假定你能連接，服務器檢查你發出的每個請求。看你是否有足夠的權限實施它。本文主要描述MySQL權限系統相關的用戶創建、授權、撤銷權限等等。

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

root@localhost[(none)]>helpAccountManagement

Formoreinformation,type'help',whereisoneofthefollowing

topics:

Youaskedforhelpabouthelpcategory:"AccountManagement"

CREATEUSER

DROPUSER

GRANT

RENAMEUSER

REVOKE

SETPASSWORD

2、創建mysql數據庫用戶

--創建用戶的語法

root@localhost[(none)]>helpcreateuser;

Name:'CREATEUSER'

Description:

Syntax:

CREATEUSERuser_specification[,user_specification]...

user_specification:

user

[

|IDENTIFIEDWITHauth_plugin[AS'auth_string']

IDENTIFIEDBY[PASSWORD]'password'

]

createuser命令會創建一個新帳戶，同時也可以為其指定密碼。該命令將添加一條記錄到user表。

該命令僅僅授予usage權限。需要再使用grant命令進行進一步授權。也可以使用grant命令直接來創建賬戶見后續的相關演示。

下面是mysql官方手冊對usage的解釋。

TheUSAGEprivilegespecifierstandsfor“noprivileges.”Itisusedatthegloballevelwith

GRANTtomodifyaccountattributessuchasresourcelimitsorSSLcharacteristicswithoutaffecting

existingaccountprivileges.

--當前演示環境

root@localhost[(none)]>showvariableslike'version';

+---------------+------------+

|Variable_name|Value|

+---------------+------------+

|version|5.5.39-log|

+---------------+------------+

--創建新用戶(未指定密碼)

root@localhost[(none)]>createuser'fred'@'localhost';

QueryOK,0rowsaffected(0.00sec)

--指定密碼創建新用戶,%表示任意，即frank可以從任意主機訪問數據庫

root@localhost[(none)]>createuser'frank'@'%'identifiedby'frank';

QueryOK,0rowsaffected(0.00sec)

--查看剛剛添加的賬戶

root@localhost[(none)]>selecthost,user,passwordfrommysql.userwhereuserlike'fr%';

+-----------+-------+-------------------------------------------+

|host|user|password|

+-----------+-------+-------------------------------------------+

|%|frank|*63DAA25989C7E01EB96570FA4DBE154711BEB361|

|localhost|fred||

+-----------+-------+-------------------------------------------+

3、使用grant授予權限

--grant命令語法

root@localhost[mysql]>helpgrant

Name:'GRANT'

Description:

Syntax:

GRANT

priv_type[(column_list)]

[,priv_type[(column_list)]]...

ON[object_type]priv_level

TOuser_specification[,user_specification]...

[REQUIRE{NONE|ssl_option[[AND]ssl_option]...}]

[WITHwith_option...]

GRANTPROXYONuser_specification

TOuser_specification[,user_specification]...

[WITHGRANTOPTION]

object_type:

TABLE

|FUNCTION

|PROCEDURE

priv_level:

*

|*.*

|db_name.*

|db_name.tbl_name

|tbl_name

|db_name.routine_name

user_specification:

user

[

|IDENTIFIEDWITHauth_plugin[AS'auth_string']

IDENTIFIEDBY[PASSWORD]'password'

]

如何授權

a、需要指定授予哪些權限

b、權限應用在那些對象上(全局，特定對象等)

c、授予給哪個帳戶

d、可以指定密碼(可選項,用此方式會自動創建用戶)

授權權限的范圍：

ON　*.*

ONdb_name.*

ONdb_name.table_name

ONdb_name.table_name.column_name

ONdb_name.routine_name

--權限一覽表，我們直接查詢root賬戶所有的權限，如下

--mysql的權限相對于oracle而言，相對簡單，而且也沒有涉及到角色方面的定義與配置

root@localhost[(none)]>select*frommysql.userwhereuser='root'andhost='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:

1rowinset(0.00sec)

--說明，本文中描述的mysql提示符為user@hostname[(dbname)]，不同的帳戶，不同的主機登錄會顯示不同。

--其次，不同的提示符下所代表的用戶身份及權限。

--查看當前的連接用戶

root@localhost[(none)]>selectcurrent_user();

+----------------+

|current_user()|

+----------------+

|root@localhost|

+----------------+

--查看當前帳戶的權限

root@localhost[(none)]>showgrants;--該賬戶用于最高權限，帶有WITHGRANTOPTION

+---------------------------------------------------------------------+

|Grantsforroot@localhost|

+---------------------------------------------------------------------+

|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'WITHGRANTOPTION|

|GRANTPROXYON''@''TO'root'@'localhost'WITHGRANTOPTION|

+---------------------------------------------------------------------+

suse11b:~#mysql-ufred-p

Enterpassword:

fred@localhost[(none)]>showgrants;

+------------------------------------------+

|Grantsforfred@localhost|

+------------------------------------------+

|GRANTUSAGEON*.*TO'fred'@'localhost'|

+------------------------------------------+

--下面使用root賬戶給fred賦予權限allprivileges

root@localhost[(none)]>grantallprivilegeson*.*to'fred'@'localhost';

QueryOK,0rowsaffected(0.01sec)

root@localhost[(none)]>flushprivileges;

QueryOK,0rowsaffected(0.00sec)

fred@localhost[(none)]>showgrants;

+---------------------------------------------------+

|Grantsforfred@localhost|

+---------------------------------------------------+

|GRANTALLPRIVILEGESON*.*TO'fred'@'localhost'|

+---------------------------------------------------+

fred@localhost[(none)]>usetempdb

fred@localhost[tempdb]>createtabletb_isam(idint,valuevarchar(20))engine=myisam;

QueryOK,0rowsaffected(0.10sec)

fred@localhost[tempdb]>insertintotb_isamvalues(1,'jack'),(2,'robin');

QueryOK,2rowsaffected(0.00sec)

Records:2Duplicates:0Warnings:0

fred@localhost[tempdb]>commit;

--下面的授權收到了錯誤提示，不能授權

fred@localhost[tempdb]>grantselectontempdb.*to'frank'@'%';

ERROR1044(42000):Accessdeniedforuser'fred'@'localhost'todatabase'tempdb'

--下面從rootsession來給之前創建的frank授權

--授予frank在數據庫tempdb上所有對象的select權限

root@localhost[(none)]>grantselectontempdb.*to'frank'@'%';

QueryOK,0rowsaffected(0.00sec)

--更新cache中的權限

root@localhost[(none)]>flushprivileges;

QueryOK,0rowsaffected(0.00sec)

--從另外的主機使用frank賬戶登錄

suse11a:~#mysql-ufrank-p-h172.16.6.89

Enterpassword:

--此時frank，此時已經可以訪問了tempdb上的表tb_isam

frank@172.16.6.89[(none)]>select*fromtempdb.tb_isam;

+------+-------+

|id|value|

+------+-------+

|1|jack|

|2|robin|

+------+-------+

frank@172.16.6.89[(none)]>showgrants;

+------------------------------------------------------------------------------------------------------+

|Grantsforfrank@%|

+------------------------------------------------------------------------------------------------------+

|GRANTUSAGEON*.*TO'frank'@'%'IDENTIFIEDBYPASSWORD'*63DAA25989C7E01EB96570FA4DBE154711BEB361'|

|GRANTSELECTON`tempdb`.*TO'frank'@'%'--可以看到多出了select權限|

+------------------------------------------------------------------------------------------------------+

--下面是一個授予最大權限的例子，授予的同時會自動創建用戶，由于我們沒有設置密碼，所以password列查詢結果為空

root@localhost[(none)]>grantallprivilegeson*.*to'jack'@'localhost';

QueryOK,0rowsaffected(0.00sec)--第一個*號代表任意數據庫，第二個*號代表數據庫上的任意對象

root@localhost[(none)]>selectuser,host,Passwordfrommysql.userwhereuser='jack';

+------+-----------+----------+

|user|host|Password|

+------+-----------+----------+

|jack|localhost||

+------+-----------+----------+

suse11b:~#mysql-ujack-p-hlocalhost

Enterpassword:

jack@localhost[(none)]>showgrantsforcurrent_user;--該方式等同于showgrants，查看自身權限

+---------------------------------------------------+

|Grantsforjack@localhost|

+---------------------------------------------------+

|GRANTALLPRIVILEGESON*.*TO'jack'@'localhost'|

+---------------------------------------------------+

--在當前session下查看其它用戶的權限，注，當前session登陸的用戶也需要有權限才能查看其它用戶權限

jack@localhost[(none)]>showgrantsfor'frank'@'%';

+------------------------------------------------------------------------------------------------------+

|Grantsforfrank@%|

+------------------------------------------------------------------------------------------------------+

|GRANTUSAGEON*.*TO'frank'@'%'IDENTIFIEDBYPASSWORD'*63DAA25989C7E01EB96570FA4DBE154711BEB361'|

|GRANTSELECTON`tempdb`.*TO'frank'@'%'|

+------------------------------------------------------------------------------------------------------+

--下面演示基于對象列級別的授權

--首先revoke之前的select權限

root@localhost[(none)]>revokeselectontempdb.*from'frank'@'%';

QueryOK,0rowsaffected(0.00sec)

fred@localhost[tempdb]>createtabletb_userasselect*frommysql.user;

QueryOK,9rowsaffected(0.15sec)

Records:9Duplicates:0Warnings:0

fred@localhost[tempdb]>grantselect(user,host),update(host)ontempdb.tb_userto'frank'@'%';

ERROR1142(42000):GRANTcommanddeniedtouser'fred'@'localhost'fortable'tb_user'--授權失敗

--下面使用root來授權

root@localhost[(none)]>grantselect(user,host),update(host)ontempdb.tb_userto'frank'@'%';

QueryOK,0rowsaffected(0.00sec)

root@localhost[(none)]>flushprivileges;

QueryOK,0rowsaffected(0.00sec)

--下面檢查一下frank所擁有的權限

root@localhost[(none)]>showgrantsfor'frank';

+------------------------------------------------------------------------------------------------------+

|Grantsforfrank@%|

+------------------------------------------------------------------------------------------------------+

|GRANTUSAGEON*.*TO'frank'@'%'IDENTIFIEDBYPASSWORD'*63DAA25989C7E01EB96570FA4DBE154711BEB361'|

|GRANTSELECT(user,host),UPDATE(host)ON`tempdb`.`tb_user`TO'frank'@'%'|

+------------------------------------------------------------------------------------------------------+

--下面使用frank身份來驗證所授予的權限

frank@172.16.6.89[(none)]>desctempdb.tb_user;

+-------+----------+------+-----+---------+-------+

|Field|Type|Null|Key|Default|Extra|

+-------+----------+------+-----+---------+-------+

|Host|char(60)|NO||||

|User|char(16)|NO||||

+-------+----------+------+-----+---------+-------+

frank@172.16.6.89[(none)]>select*fromtempdb.tb_user;--訪問時不支持通配符，必須指定列名

ERROR1142(42000):SELECTcommanddeniedtouser'frank'@'suse11a.site'fortable'tb_user'

frank@172.16.6.89[(none)]>selecthost,userfromtempdb.tb_userwhereuser='frank';

+------+-------+

|host|user|

+------+-------+

|%|frank|

+------+-------+

--需要注意的是，如果你的對象創建在test相關數據庫下，權限限制可能會失效。

--下面這個查詢用于查看db的授權表

root@localhost[(none)]>selecthost,db,userfrommysql.db;

+------+---------+------+

|host|db|user|

+------+---------+------+

|%|test||

|%|test/_%||

+------+---------+------+

--根據前面的權限授予,列host可以被更新，而列user不行，如下面的2條SQL語句執行的結果

frank@172.16.6.89[(none)]>updatetempdb.tb_usersethost='localhost'whereuser='frank';

QueryOK,1rowaffected(0.12sec)

Rowsmatched:1Changed:1Warnings:0

frank@172.16.6.89[(none)]>updatetempdb.tb_usersetuser='jason'whereuser='jack';

ERROR1143(42000):UPDATEcommanddeniedtouser'frank'@'suse11a.site'forcolumn'user'intable'tb_user'

--關于WITHGRANTOPTION

root@localhost[(none)]>showgrants;--注意root下有WITHGRANTOPTION

+---------------------------------------------------------------------+

|Grantsforroot@localhost|

+---------------------------------------------------------------------+

|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'WITHGRANTOPTION|

|GRANTPROXYON''@''TO'root'@'localhost'WITHGRANTOPTION|

+---------------------------------------------------------------------+

root@localhost[(none)]>showgrantsfor'jack'@'localhost';--注意jack下沒有WITHGRANTOPTION

+---------------------------------------------------+--這就是前面為什么用戶自身創建的對象而無法授權的問題

|Grantsforjack@localhost|

+---------------------------------------------------+

|GRANTALLPRIVILEGESON*.*TO'jack'@'localhost'|

+---------------------------------------------------+

4、撤銷權限

撤銷權限使用的是revoke關鍵字，撤銷與授權的權限方式基本類似，

其次有哪些權限可以授予，相應地就有哪些權限可以撤銷，原來的to子句呢則變成了from子句。

如下面的示例

mysql>revokeSELECT(user,host),UPDATE(host)ON`tempdb`.`tb_user`from'frank'@'%';

mysql>revokeallprivileges,grantoptionfrom'frank'@'%';

root@localhost[(none)]>revokeSELECT(user,host),UPDATE(host)ON`tempdb`.`tb_user`from'frank'@'%';

QueryOK,0rowsaffected(0.00sec)

--Author:Leshami

--Blog:http://blog.csdn.net/leshami

root@localhost[(none)]>revokeallprivileges,grantoptionfrom'frank'@'%';

QueryOK,0rowsaffected(0.01sec)

root@localhost[(none)]>flushprivileges;

QueryOK,0rowsaffected(0.00sec)

root@localhost[(none)]>showgrantsfor'frank';--查看revoke之后僅擁有最基本權限

+------------------------------------------------------------------------------------------------------+

|Grantsforfrank@%|

+------------------------------------------------------------------------------------------------------+

|GRANTUSAGEON*.*TO'frank'@'%'IDENTIFIEDBYPASSWORD'*63DAA25989C7E01EB96570FA4DBE154711BEB361'|

+------------------------------------------------------------------------------------------------------+

5、刪除及重命名賬戶

使用dropuser命令刪除用戶

--查看當前系統中已存在的用戶

root@localhost[(none)]>selectuser,host,Passwordfrommysql.user;

+-------+-----------+-------------------------------------------+

|user|host|Password|

+-------+-----------+-------------------------------------------+

|root|localhost||

|root|suse11b||

|root|127.0.0.1||

|root|::1||

||localhost||

||suse11b||

|fred|localhost||

|frank|%|*63DAA25989C7E01EB96570FA4DBE154711BEB361|

|jack|localhost||

+-------+-----------+-------------------------------------------+

--使用dropuser命令刪除用戶

root@localhost[(none)]>dropuser'frank'@'%';

QueryOK,0rowsaffected(0.00sec)

root@localhost[(none)]>dropuser'fred'@'localhost';

QueryOK,0rowsaffected(0.00sec)

root@localhost[(none)]>selectuser,host,Passwordfrommysql.userwhereuserlike'fr%';

Emptyset(0.00sec)

--如何重命名帳戶，使用renameuser命令

root@localhost[(none)]>renameuser'jack'@'localhost'to'jason'@'localhost';

QueryOK,0rowsaffected(0.00sec)

root@localhost[(none)]>selectuser,host,Passwordfrommysql.userwhereuserlike'j%';

+-------+-----------+----------+

|user|host|Password|

+-------+-----------+----------+

|jason|localhost||

+-------+-----------+----------+

--對于用戶的刪除也可以直接從mysql.user進行刪除相應的記錄，但不推薦直接操作mysql系統表