$10 用户和权限管理

本节记录数据库用户和权限管理

查看用户

1. SELECT user FROM mysql.user

   root@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)

新建用户

1. GRANT authority ON database.table TO user@host IDENTIFIED BY password 新版本创建用户和赋予权限的方式分开了,不能使用本条语句

   root@localhost) [book]> grant select, update on book.book
      -> to wangming@'localhost' identified by '123';
   Query OK, 0 rows affected (0.03 sec)

2. CREATE USER user@host IDENTIFIED BY password

   (root@localhost) [book]> create user wangming@localhost identified by '123';
   Query OK, 0 rows affected (0.03 sec)

修改口令

1. SET PASSWOED FOR user@host=PASSWORD('password') 新版本password熟悉已经取消,不能使用本条语句

   (root@localhost) [book]> set password for wangming@localhost=password('hello');
   Query OK, 0 rows affected (0.03 sec)

2. ALTER USER user@host IDENTIFIED BY password

   (root@localhost) [book]> alter user wangming@localhost identified by 'hello';
   Query OK, 0 rows affected (0.01 sec)

删除用户

1. DROP USER user@host

   (root@localhost) [book]> drop user wangming@localhost;
   Query OK, 0 rows affected (0.01 sec)

---

赋予权限

1. GRANT authority ON database.table TO user@host

   (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)

收回权限

1. REVOKE authority ON database.table FROM user@host

   (root@localhost) [book]> revoke update on book.book
       -> from wangming@localhost;
   Query OK, 0 rows affected (0.01 sec)

查看权限

1. SELECT * FROM mysql.user!--8-->