MySQL 用户与权限管理

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系统表