MySQL用户管理

---

数据库是用来对数据操作的，而数据又是特别重要的，权限管理就显得尤为重要了。

一、用户

类似于Linux的用户管理，MySQL也有超级用户和普通用户之分。

MySQL用户权限管理主要有以下作用：

可以限制用户访问哪些库、哪些表
可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
可以限制用户登录的IP或域名
可以限制用户自己的权限是否可以授权给别的用户

1.1 用户信息

MySQL中的用户，都存储在系统数据库mysql的user表中：

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
...

mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *1FB33BD1FB01E7FC579C55B0CCED0B8DD9AE9368 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.01 sec)

字段解释：

• host： 表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆
• user： 用户名
• authentication_string： 用户密码通过password函数加密后的
• *_priv： 用户拥有的权限

1.2 创建用户

语法：create user '用户名'@'登陆主机/ip' identified by '密码'; 这个密码要比较复杂才行
%通配符表示任何主机都可以连接（可以远程连接），localhost表示本地连接。

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *1FB33BD1FB01E7FC579C55B0CCED0B8DD9AE9368 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| sqin          | %         | *680AC37705F53AFAB5DCA4AD8D81E2DCA383FA83 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

1.3 删除用户

语法：drop user '用户名'@'主机名'

直接给个用户名，它删除的主机名默认是%（任何地方均可以登录），而其他的不能删除

mysql> drop user sqin;
Query OK, 0 rows affected (0.00 sec)

例如，设置为本地登录的用户：

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *1FB33BD1FB01E7FC579C55B0CCED0B8DD9AE9368 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| sqin          | localhost | *680AC37705F53AFAB5DCA4AD8D81E2DCA383FA83 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.01 sec)

mysql> 
mysql> drop user sqin
    -> ;
ERROR 1396 (HY000): Operation DROP USER failed for 'sqin'@'%'

1.4 修改用户密码

• 自己改自己密码

-- 不管是root还是普通用户，都适用
set password=password('新的密码');

• root用户修改指定用户的密码

set password for '用户名'@'主机名'=password('新的密码')；

二、权限

MySQL数据库提供的权限列表：

2.1 给用户授权

刚创建的用户没有任何权限。需要给用户授权。
语法：grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']

• 权限列表，多个权限用逗号分开。 常用权限：all privileges(所有权限)、create、drop、select、insert、delete、update
• *.* ：代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
• 库.* ：表示某个数据库中的所有数据对象(表，视图，存储过程等)
• identified by可选。 如果用户存在，赋予权限的同时修改密码，如果该用户不存在，就是创建用户

// 授予管理员权限
grant all privileges on *.* to 'xxx'@'主机' with grant option;

// 授予zhangsan在test数据库中创建、修改、删除表的权限以及创建视图的权限
grant create、alter、drop、create view on test.* to zhangsan;

授予列权限

// 授予用户zhangsan可以对test.shop表的id和name列进行更新
grant update(id,name) on test.shop to zhangsan@'localhost';

// 查看自己的权限：
show grants;

// 特定用户现有查看权限
show grants for 'whb'@'%';

如果发现赋权限后，没有生效，执行如下指令：

flush privileges;

2.2 回收权限

语法：
revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'；