【MySQL】18. 用户管理-2 数据库的权限

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

2.1 给用户授权

刚创建的用户没有任何权限。需要给用户授权。
语法：

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']

说明：

• 权限列表，多个权限用逗号分开

grant select on …
grant select, delete, create on …
grant all [privileges] on … – 表示赋予该用户在该对象上的所有权限

• . : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
• 库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)
• identified by可选。 如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户

案例：

--使用root账号
--终端A
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bit_index          |
| mysql              |
| performance_schema |
| scott              |
| sys                |
| tt                 |
+--------------------+
7 rows in set (0.00 sec)

mysql> use scott;
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> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| exam            |
| salgrade        |
| stu             |
+-----------------+
5 rows in set (0.00 sec)

--给用户hx赋予scott数据库下所有文件的select权限
mysql> grant select on scott.* to 'hx'@'localhost';
Query OK, 0 rows affected (0.00 sec)

--使用hx账号
--终端B
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

--暂停等root用户给hx赋完权之后，再查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| scott              | --赋完权之后，就能看到新的表
+--------------------+
2 rows in set (0.00 sec)

mysql> use scott;
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> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| exam            |
| salgrade        |
| stu             |
+-----------------+
5 rows in set (0.00 sec)

mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | tom  |
|    3 | kity |
|    4 | nono |
+------+------+
4 rows in set (0.00 sec)

--没有删除权限
mysql> delete from stu;
ERROR 1142 (42000): DELETE command denied to user 'hx'@'localhost' for table 'stu'

备注：特定用户现有查看权限
mysql> show grants for 'hx'@'localhost';
+-----------------------------------------------+
| Grants for hx@localhost                       |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'hx'@'localhost'        |
| GRANT SELECT ON `scott`.* TO 'hx'@'localhost' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

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

flush privileges;

2.2 回收权限

语法：

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

示例：

-- 回收hx对test数据库的所有权限
--root身份，终端A
mysql> revoke all on test.* from 'hx'@'localhost';
Query OK, 0 rows affected (0.00 sec)

--hx身份，终端B
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| scott              |
+--------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)