mysql权限管理

在介绍权限管理前，我们要先理解，mysql权限的机制：

系统用的权限表都在”mysql”这个数据库中，这个数据库在mysql被安装是就会创建，其中有user、db、host，还有后缀为priv的表。

权限范围以此递减：user->db->tables_priv->columns_priv。

例如在user中有select权限，即在所有数据库，表中都有select权限，使用如下命令查看权限:

SELECT * FROM `user` where user = 'root' \G;

一、创建账户

有2种方式创建账户，使用grant语法创建或者直接修改授权表，推荐使用grant，因为这样不容易出错；

创建一个ben用户，权限是可以修改所有数据库

grant all privileges on *.* to ben@localhost

这里的第一个*就所有数据库，第二个*就是所有表 ben是账户名，localhost是连接的ip

然后我们查看user表，发现ben拥有除了grant以外的所有权限，我们也可以给他加上grant

grant all privileges on *.* to ben@localhost with grant option；

然后我们可以设置密码为123：

grant all privileges on *.* to ben@localhost identified by '123'  with grant option；

我们可以创建用户，可以从任意ip访问，并且只有select权限：

grant select on *.* to 'ben'@'%' identified by '123' ；

只运行以192.168.123.开头的host进行连接:

grant select on *.* to 'ben'@'192.168.123.%' identified by '123' ；

任何用户从192.168.123.1进行连接

grant select on *.* to ''@'192.168.123.1' identified by '123' ；

如果一个用户满足多个user表中的连接权限，那么会根据host进行排序，匹配的等一行；

二、查看账户权限

mysql> show grants for ben@localhost;

+-------------------------------------------------------------------------+
| Grants for ben@localhost |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ben'@'localhost' IDENTIFIED BY PASSWORD <secret> |
| GRANT SELECT ON `test`.* TO 'ben'@'localhost' |
+-------------------------------------------------------------------------+

mysql 5.0之后，也可以通过information_schema查看权限

mysql> use information_schema;

mysql> select * from SCHEMA_PRIVILEGES where grantee="'ben'@'localhost'"; 
+-------------------+---------------+--------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------+---------------+--------------+----------------+--------------+
| 'ben'@'localhost' | def | test | SELECT | NO |
+-------------------+---------------+--------------+----------------+--------------+

三、更改账号权限

使用grant 新增权限，使用revoke回收权限;

grant上面介绍过来，现在介绍一下revoke，删除delete权限从ben@localhost:

revoke delete on *.*  from  ben@localhost;

值得注意的是usage不能被回收，也就是说revoke不能用来删除用户;

四、修改密码

1、使用mysqladmin

mysqladmin -u ben -p 123 password 'newpassword'

2、执行set password

mysql>set password for 'ben@localhost' = password('123');

3、执行set password修改自己密码

mysql> set password = password('123');

4、直接改表

mysql>insert into user (Host, User, Password) values ('localhost', 'ben', '123');

mysql>flush privileges;

5、我们还可以直接用md5加密后的密码直接进行设置

grant usage on *.* to ben@localhost identifed by password '59BC28EFBBFE2A5CA4C723FBB8760D7FBD178761';

注意前面没有* ，或者

mysql>set password = '59BC28EFBBFE2A5CA4C723FBB8760D7FBD178761'

五、账号资源限制

mysql还可以配置每个账号实际具有的资源限制：

1、单账号每小时执行查询次数(MAX_QUERIES_PRE_HUOR num)；

2、单账号每小时执行更新次数(MAX_UPDATES_PRE_HUOR num)；

3、单账号每小时连接服务器次数(MAX_CONNECTIONS_PRE_HUOR num)；

4、单账号并发连接次数(MAX_USER_CONNECTIONS_PER_HOUR num)；

设置的方法为[option]为上面括号中内容：

grant all privileges on *.* to ben@localhost with [option]

一小时10次；

grant all privileges on *.* to ben@localhost with MAX_QUERIES_PRE_HUOR 10;

六、删除账号

drop user ben@localhost;