本节记录数据库用户和权限管理
查看用户
- SELECT user FROM mysql.user
1
2
3
4
5
6
7
8
9
10
11
12
13root@localhost) [book]> select user from mysql.user;
+------------------+
| user |
+------------------+
| minutesheep |
| minutesheep |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
| zhangsan |
+------------------+
7 rows in set (0.00 sec)
新建用户
GRANT authority ON database.table TO user@host IDENTIFIED BY password 新版本创建用户和赋予权限的方式分开了,不能使用本条语句
1
2
3root@localhost) [book]> grant select, update on book.book
-> to wangming@'localhost' identified by '123';
Query OK, 0 rows affected (0.03 sec)CREATE USER user@host IDENTIFIED BY password
1
2(root@localhost) [book]> create user wangming@localhost identified by '123';
Query OK, 0 rows affected (0.03 sec)
修改口令
SET PASSWOED FOR user@host=PASSWORD('password') 新版本password熟悉已经取消,不能使用本条语句
1
2(root@localhost) [book]> set password for wangming@localhost=password('hello');
Query OK, 0 rows affected (0.03 sec)ALTER USER user@host IDENTIFIED BY password
1
2(root@localhost) [book]> alter user wangming@localhost identified by 'hello';
Query OK, 0 rows affected (0.01 sec)
删除用户
- DROP USER user@host
1
2(root@localhost) [book]> drop user wangming@localhost;
Query OK, 0 rows affected (0.01 sec)
赋予权限
- GRANT authority ON database.table TO user@host
1
2
3(root@localhost) [book]> grant select, update on book.book
-> to wangming@localhost with grant option; WITH GRANT OPTION允许这个用户将权限赋予他人
Query OK, 0 rows affected (0.00 sec)
收回权限
- REVOKE authority ON database.table FROM user@host
1
2
3(root@localhost) [book]> revoke update on book.book
-> from wangming@localhost;
Query OK, 0 rows affected (0.01 sec)
查看权限
- SELECT * FROM mysql.user!--8-->