MySQL学习笔记17

MySQL权限管理grant：

权限说明：

Table 6.2 Permissible Privileges for GRANT and REVOKE

Privilege	Grant Table Column	Context
ALL [PRIVILEGES]	Synonym for “all privileges”	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server administration

说明：

USAGE：无权限，只有登录数据库，只可以使用test和test_*数据库。

ALL： 所有权限。

以下权限为指定权限。

select/update/delete/supper/replication slave/reload ...

with grant option: 选项表示允许把自己的权限授予其他用户或者从其他用户收回自己的权限。

默认情况下，分配权限时如果没有指定with grant option，代表这个用户不能下发权限给其他用户，但是这个权限不能超过自己的权限。

权限的保存位置：（了解）：

mysql.user                所有mysql用户的账号和密码，以及用户对全库全表权限（*.*）
mysql.db                非mysql库的授权都保存在此(db.*)
mysql.table_priv        某库某表的授权(db.table)
mysql.columns_priv        某库某表某列的授权(db.table.col1)
mysql.procs_priv        某库存储过程的

给用户授权：

基本语法：

mysql> grant 权限1,权限2 on 库.表 to 用户@主机
mysql> grant 权限(列1,列2,...) on 库.表 to 用户@主机

库.表表示方法：*.*代表所有数据库的所有数据表，db_itheima.*代表db_itheima数据库中的所有数据表，db_itheima.tb_admin，代表db_itheima数据库中的tb_admin表

*：通配符。

案例：给tom账号分配db_db3库的查询（select）权限：

mysql> grant select on db_db3.* to 'tom'@'localhost';
Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> show grants;
+---------------------------------------------------------------------+
| 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)mysql> show grants for 'tom'@'localhost';
+-------------------------------------------------+
| Grants for tom@localhost                        |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'         |
| GRANT SELECT ON `db_db3`.* TO 'tom'@'localhost' |
+-------------------------------------------------+
2 rows in set (0.00 sec)

案例：给tom账号分配db_db3数据表的权限（要求只能更改age权限。）

该案例是具体到某个列。

mysql> grant update(age) on db_db3.tb_student to 'tom'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'tom'@'localhost';
+------------------------------------------------------------------+
| Grants for tom@localhost                                         |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'                          |
| GRANT SELECT ON `db_db3`.* TO 'tom'@'localhost'                  |
| GRANT UPDATE (age) ON `db_db3`.`tb_student` TO 'tom'@'localhost' |
+------------------------------------------------------------------+
3 rows in set (0.00 sec)

案例：添加一个root@%账号，然后分配所有权限。

mysql> create user 'root'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'root'@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

然后我们可以使用navicat来连接到这台数据库服务器，检查其中的数据表。

 

 权限匹配有一个就近匹配的原则。哪个最符合我的规则，就选择哪个规则。

使用navicate软件添加表内容。可视化来操作数据库。使用数据库能帮我们做很多事情。

查询用户权限：

mysql> show grants;
+---------------------------------------------------------------------+
| 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)mysql>
mysql> show grants for 'root'@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

show grants是查询自己的权限；

show grants for ‘root’@'%' 查询某个用户的权限。

mysql> show grants for 'harry'@'192.168.17.125';
+------------------------------------------------+
| Grants for harry@192.168.17.125                |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'harry'@'192.168.17.125' |
+------------------------------------------------+
1 row in set (0.00 sec)mysql> show grants for 'tom'@'localhost';
+------------------------------------------------------------------+
| Grants for tom@localhost                                         |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'                          |
| GRANT SELECT ON `db_db3`.* TO 'tom'@'localhost'                  |
| GRANT UPDATE (age) ON `db_db3`.`tb_student` TO 'tom'@'localhost' |
+------------------------------------------------------------------+
3 rows in set (0.00 sec)

查看tom这个账号的权限，看权限不能只看第一行，要看三行。

with grant option选项：

with grant option选项作用：代表此账号可以为其他用户下发权限，但是下发的权限不能超过自身权限。

如果grant授权时没有with grant option选项，则其无法为其他用户授权。

mysql> grant all on *.* to 'amy'@'10.1.1.%' identified by '123' with grant option;
mysql> grant all on *.* to 'harry'@'10.1.1.%' identified by '123'; 

如以上命令所示：
amy拥有下发权限的功能，而harry不具备下发权限的功能。

创建用户的时候，要考虑这个用户以后是否要给别的用户创建权限。创建二级和三级管理员。

使用grant创建用户：

说明：5.7以后不推荐，未来会弃用这个功能。觉得这个方式不太安全。

基本语法：

mysql> grant 权限 on 数据库.数据表 to '新用户名称'@'授权主机名称或IP地址' identified by '用户的密码';

案例：创建一个root账号，主机为%, 授权所有权限，密码为123；

mysql> grant all privileges on *.* to 'root'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
mysql> show grants for 'root'@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

这一个语句做了三件事：创建用户，设置密码，设置权限。

grant语句也可以用来重置密码和创建用户。

revoke回收权限：

基本语法：

revoke 权限 on 库.表 from 用户;

撤消指定的权限
mysql> revoke update on db01.tt1 from 'tom'@'10.1.1.1';
撤消所有的权限
mysql> revoke select on db01.* from 'tom'@'10.1.1.1';

案例：从tom账号中回收select权限。

mysql> show grants for 'tom'@'localhost';
+------------------------------------------------------------------+
| Grants for tom@localhost                                         |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'                          |
| GRANT SELECT ON `db_db3`.* TO 'tom'@'localhost'                  |
| GRANT UPDATE (age) ON `db_db3`.`tb_student` TO 'tom'@'localhost' |
+------------------------------------------------------------------+
3 rows in set (0.00 sec)mysql>
mysql> revoke select on db_db3.* from 'tom'@'localhost';
Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'tom'@'localhost';
+------------------------------------------------------------------+
| Grants for tom@localhost                                         |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'                          |
| GRANT UPDATE (age) ON `db_db3`.`tb_student` TO 'tom'@'localhost' |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

案例：从tom账号中回收update权限。

mysql> revoke update(age) on db_db3.tb_student from 'tom'@'localhost';
Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'tom'@'localhost';
+-----------------------------------------+
| Grants for tom@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)