MySQL（管理）01 -- 用户User和权限Privileges＜B.用户管理权限设置＞
2.用户管理和权限设置
用户管理分为几个方面，查看用户、创建用户、对用户授权、修改和删除用户、设置与更改用户密码、撤销用户权限等。
2.1.查看用户
查看用户并没有直接的SQL语句，而是进入 mysql数据库的user表（这个mysql库和user表都是一开始就有的），直接用 select * from user；来查看有什么用户
由于在linux下比较难看，因为格式比较乱，所以我用了 SQLyog这个软件登陆数据库来操作。
代码如下：
[sql] view plain copy
- use mysql；
- select * from user;
效果如上图，最后用数据库软件，因为在linux下界面看不清效果，太乱了。
从上图可以看到，会列出数据库所有的用户及权限
2.2.创建用户
创建账号有几种方法。
- 1.使用grant直接对账号授权，账号不存在则会创建；（MySQL8，已无法进行此操作）
- 2.向mysql.user表中插入记录；
- 3.使用create user命令。（最推荐的方法：先创建，然后grant授权）
后两种方法创建的用户初始时没有任何权限(只有usage登录数据库的权限)，并且修改权限后要使用 FLUSH PRIVILEGES 语句或执行 mysqladmin flush-privileges 或 mysqladmin reload 命令刷新权限表到内存中，而第一种方法简便的多，创建用户后会自动刷新权限表。
grant和revoke语法：
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [IDENTIFIED [BY [PASSWORD] 'password'][WITH with_option [with_option]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
| MAX_STATEMENT_TIME time
grant可以在库、表、函数、存储过程、特定列上授权，且一次性可以为多个用户授予多个对象的权限。其中 with grant option 表示拥有该权限后的用户可以给别的用户授予自身所拥有的权限。
revoke表示收回权限，注意revoke无法收回usage权限。
其中user的表示方法是 '用户名'@'主机名' ，主机名部分可以是主机名，可以是IP地址，可以是localhost，可以是通配符组成的主机名(空的host值也表示所有host，等价于'user_name'@'%')。如下示例：
对于网段地址，可以指定掩码来表示，如192.168.100.1/255.255.255.0，不能使用cidr格式的掩码记录方式，也不能指定非8、16、24、32位的掩码，如192.168.100.1/255.255.255.240是不允许的。
如果在user表中的用户有交叉部分，如root既可以从localhost登录，也可以从127.0.0.1登录，还可以从本机IP192.168.100.61登录，还可以从网段地址192.168.100.%登录，那么到底会从哪个登录？
在读取权限表user到内存中的时候，首先会根据host列的具体性进行排序，然后再根据user列进行具体性排序(即理解为order by host,user)，然后从上到下扫描，首次扫描到符合的记录就使用该记录登录。具体性的意思是越具体的host优先级越高，通配符范围越宽的host优先级越低。例如[email protected]的具体性比[email protected]'%'的具体性高，后者又比'%'@'%'的具体性高。
CREATE USER 'user_name'@'host' IDENTIFIED BY 'password'；
user_name：要创建用户的名字。
host：表示要这个新创建的用户允许从哪台机登陆，如果只允许从本机登陆，则 填 ‘localhost’ ，如果允许从远程登陆，则填 ‘%’
password：新创建用户的登陆数据库密码，如果没密码可以不写。
例：
CREATE USER ‘aaa’@‘localhost’ IDENTIFED BY ‘123456’； //表示创建的新用户，名为aaa，这个新用户密码为123456，只允许本机登陆
CREATE USER 'bbb'@'%' IDENTIFED BY '123456'；//表示新创建的用户，名为bbb，这个用户密码为123456，可以从其他电脑远程登陆mysql所在服务器
CREATE USER ‘ccc’@‘%’ ；//表示新创建的用户ccc，没有密码，可以从其他电脑远程登陆mysql服务器
我用 CREATE USER 'aaa'@‘%’；创建新用户，再用 select * from user；查看用户列表：
可以看出 有 aaa用户了。但是可以看到 aaa 的权限很多都是 N，表示没有权限，因为还要为用户授予权限。
2.3.授权用户
命令：GRANT privileges ON databasename.tablename TO ‘username’@‘host’
privileges：表示要授予什么权力，例如可以有 select ， insert ，delete，update等，如果要授予全部权力，则填 ALL
databasename.tablename：表示用户的权限能用在哪个库的哪个表中，如果想要用户的权限很作用于所有的数据库所有的表，则填 *.*，*是一个通配符，表示全部。
’username‘@‘host’：表示授权给哪个用户。
例：
GRANT select，insert ON zje.zje TO ‘aaa’@‘%’； //表示给用户aaa授权，让aaa能给zje库中的zje表 实行 insert 和 select。
GRANT ALL ON *.* TO ‘aaa’@‘%’；//表示给用户aaa授权，让aaa能给所有库所有表实行所有的权力。
用GRANT ALL ON *.* TO ‘aaa’@‘%’ ；再看用户列表，可以发现权限都变成 Y了。
注意：
用以上命令授权的用户不能给其他用户授权，如果想这个用户能够给其他用户授权，就要在后面加上 WITH GRANT OPTION
如： GRANT ALL ON *.* TO ’aaa‘@'%' WITH GRANT OPTION；
2.4.删除用户
直接使用drop user命令或者从mysql.user表中删除对应记录。
命令：DROP USER ‘user_name’@‘host’
例：
DROP USER 'aaa'@‘%’；//表示删除用户aaa；
注意，删除表中用户记录的时候不会从现有用户中回收对该表的权限，当下次再创建同名表的时候，会自动为用户授予该表的权限造成权限外流。
因此，建议使用drop user语句来删除用户。
2.5.设置与更改用户密码
SET PASSWORD FOR ‘username’@‘host’ = PASSWORD(‘newpassword’)；
比如：
(1)grant all on *.* to 'root'@'localhost' identified by '123456' with grant option;
(2)grant usage on *.* to 'root'@'localhost' identified by '123456' with grant option;
使用usage权限表示在不影响现有权限的情况下使用grant来修改密码。
(3)set password [for 'root'@'localhost'] =password('123456');
password函数中必须加引号，不写user时是为当前用户修改。
如果是设置当前用户的密码：
SET PASSWORD = PASSWORD('newpassword')；
如： SET PASSWORD = PASSWORD(‘123456’)；
2.6.撤销用户权限：
命令：REVOKE privileges ON database.tablename FROM ‘username’@‘host’；
例如： REVOKE SELECT ON *.* FROM ‘zje’@‘%’；
但注意：
若授予权利是这样写： GRANT SELECT ON *.* TO ‘zje’@‘%’；
则用 REVOKE SELECT ON zje.aaa TO ‘zje’@‘%’；是不能撤销用户zje 对 zje.aaa 中的SELECT 权利的。
反过来 GRANT SELECT ON zje.aaa TO ‘zje’@‘%’；授予权力
用 REVOKE SELECT ON *.* FROM ‘zje’@‘%’；也是不能用来撤销用户zje 对zje库的aaa表的SELECT 权利的
（附）
A.记录创建用户的时间
MySQL中user的元数据信息都存放在mysql.user表中，但是在这个表中的信息分类很少，常用的就只有用户类列和权限类列，没有用户的创建时间。
可以通过新增一列来记录用户的创建时间。
alter table mysql.user add column create_time timestamp default current_timestamp;
这样以后新建用户都会记录创建时间。但是显然，对于已有的用户是没有记录时间的，它们的值都为'0000-00-00 00:00:00'。
MariaDB [mysql]> select host,user,create_time from mysql.user;
+---------------------+-----------+---------------------+
| host | user | create_time |
+---------------------+-----------+---------------------+
| localhost | root | 2018-04-21 05:58:19 |
| 127.0.0.1 | root | 2018-04-21 05:58:19 |
| ::1 | root | 2018-04-21 05:58:19 |
| localhost | | 2018-04-21 05:58:19 |
| 192.168.100.% | root | 2018-04-21 05:58:19 |
| 192.168.100.1 | long | 2018-04-21 05:58:19 |
| 127.0.0.1 | longshuai | 2018-04-21 05:58:19 |
| 192.168.100.1 | longshuai | 0000-00-00 00:00:00 |
+---------------------+-----------+---------------------+
B.查看用户权限
可以使用show grants语句查看某个user的权限信息。
例如：
1
mysql> show grants for 'root'@'localhost';
Grants for [email protected]
-----------------------------------------------------------------------------------------------------------------
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
2
mysql>SHOW GRANTS FOR 'long'@'192.168.100.1';
Grants for [email protected]
-----------------------------------------------------------------------------------------------------------
GRANT USAGE ON *.* TO 'long'@'192.168.100.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
GRANT SELECT ON `test`.* TO 'long'@'192.168.100.1'
C.revoke命令的严格性
revoke命令回收权限时必须要明确指定回收的数据库对象以及用户名，其中usage权限无法回收。特别要说明的是revoke all，当你以为它会回收所有权限的时候，它可能一点权限都没有回收。也就是说revoke命令的书写非常严格。
用户 'long'@'192.168.100.1' 在 *.* 上具有usage权限，在test.*上具有select权限。
MariaDB [mysql]> SHOW GRANTS FOR 'long'@'192.168.100.1';
Grants for [email protected]
-----------------------------------------------------------------------------------------------------------
GRANT USAGE ON *.* TO 'long'@'192.168.100.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
GRANT SELECT ON `test`.* TO 'long'@'192.168.100.1'
对该用户在 *.* 上进行revoke all，再次查看权限，发现权限根本一点变化都没有。因为usage权限无法回收，而select权限是在test.*上而非*.*上。
REVOKE ALL ON *.* FROM 'long'@'192.168.100.1';
要回收test.*上的select权限，必须在revoke中指定test.*，而不能是 *.* 。以下两个语句都能回收。
revoke select on test.* from 'long'@'192.168.100.1';
revoke all on test.* from 'long'@'192.168.100.1';
转载链接：