MySQL 8.0授权认证
一.系统权限表
user
存放用户账户信息以及全局级别(所有数据库)权限,决定了来自哪些主机的哪些用户可以访问数据库实例,如果有全局权限则意味着对所有数据库都有此权限
db
存放数据库级别的权限,决定了来自哪些主机的哪些用户可以访问此数据库
tables_priv
存放表级别的权限,决定了来自哪些主机的哪些用户可以访问数据库的这个表
columns_priv
存放列级别的权限,决定了来自哪些主机的哪些用户可以访问数据库表的这个字段
procs_priv
存放存储过程和函数级别的权限
二.创建用户与授权
1.简单的例子
#创建用户
#重点：用户创建完成可以连接,但是没有任何权限
create user hans@localhost identified by'123456';
#授权
#这个就相当于给了一个超级管理员权限
grants all on*.* to 'hans'@'localhost';
#查看用户的权限
show grantsforhans@localhost;
#如果权限过大,那就回收权限
revoke all on*.* from 'hans'@'localhost';
2.关于远程用户
#这是配置远程用户和权限
可以用%来表示允许所有的IP进行连接 比如'hans'@'%'grants all on*.* to 'hans'@'192.168.1.%'可以用192.168.1.%来表示 192.168.1.0/24这个网段的IP来进行连接 比如 'hans'@'192.168.1.%
grants select on *.* to 'hans'@'192.168.1.%
可以用192.168.1.%来表示 192.168.1.0/24这个网段的IP来进行连接 比如 'hans'@'192.168.1.10%'grants insert,update,delete on*.* to 'hans'@'192.168.1.10%
#重点 这个带网段的这个权限会是叠加的
3.复杂的例子
案例1
#你的网段可能不是10.0.0.0/8,这不重要,重要的是你可以领会这个权限操作
(1)
来自企业内网10.0.0网段的应用程序app1希望能赋予MySQL数据库实例里的
A1数据库的增删改查权限,
A2数据库里B2表的查询权限,
A3数据库B3表上ID字段的查询权限,
这个数据库用户的创建语句是什么?
(2)
如果要回收该用户在A2数据库里B2表的查询权限,语句怎么写?
create user 'app1'@'10.0.0.%' identified by '123456';
grantselect,instert,update,delete on a1.* to 'app1'@'10.0.0.%';
grantselect on a2.b2 to 'app1'@'10.0.0.%';
grantselect(id) on a3.b3 to 'app1'@'10.0.0.%';
这个复杂的例子就是把MySQL权限认证操作写的更细致,控制的粒度控制到了字段(列)级别
那么如何验证上面的授权语句是否正确呢?
通过查询MySQL的系统权限表，就可以知道对这个用户的权限配置
我一步一步演示上面的这个复杂的案例
先完成(1)
创建3个数据库
mysql>create database a1;
Query OK,1 row affected (0.11sec)
mysql>create database a2;
Query OK,1 row affected (0.04sec)
mysql>create database a3;
Query OK,1 row affected (0.05 sec)
创建表
mysql>use a1;
Database changed
mysql> create table t1 (sid int,name varchar(10));
Query OK,0 rows affected (0.10sec)
mysql>use a2;
Database changed
mysql> create table b2 (sid int,name varchar(10));
Query OK,0 rows affected (0.06sec)
mysql>use a3;
Database changed
mysql> create table b3 (sid int,name varchar(10));
Query OK,0 rows affected (0.41 sec)
创建app1这个用户
mysql> create user 'app1'@'192.168.91.%' identified by '123456!';
Query OK,0 rows affected (0.08 sec)
授权
mysql> grant select,insert,update,delete on a1.* to 'app1'@'192.168.91.%';
Query OK,0 rows affected (0.08sec)
mysql> grant select on a2.b2 to 'app1'@'192.168.91.%';
Query OK,0 rows affected (0.09sec)
mysql> grant select(sid) on a3.b3 to 'app1'@'192.168.91.%';
Query OK,0 rows affected (0.04 sec)
验证
这个是数据库级别
mysql> select * from mysql.db where user='app1' and host='192.168.91.%'\G;*************************** 1. row ***************************Host:192.168.91.%Db: a1
User: app1
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N1 row in set (0.00sec)
ERROR:
No query specified
这个是表级别
mysql> select * from mysql.tables_priv where user='app1' and host='192.168.91.%'\G;*************************** 1. row ***************************Host:192.168.91.%Db: a2
User: app1
Table_name: b2
Grantor: root@localhost
Timestamp:0000-00-00 00:00:00Table_priv: Select
Column_priv:*************************** 2. row ***************************Host:192.168.91.%Db: a3
User: app1
Table_name: b3
Grantor: root@localhost
Timestamp:0000-00-00 00:00:00Table_priv:
Column_priv: Select2 rows in set (0.00sec)
ERROR:
No query specified
这个就是字段(列)级别
mysql> select * from mysql.columns_priv where user='app1' and host='192.168.91.%'\G;*************************** 1. row ***************************Host:192.168.91.%Db: a3
User: app1
Table_name: b3
Column_name: sid
Timestamp:0000-00-00 00:00:00Column_priv: Select1 row in set (0.00sec)
ERROR:
No query specified
换个语句再看下用户的权限
mysql> show grants for 'app1'@'192.168.91.%'\G;*************************** 1. row ***************************Grantsfor app1@192.168.91.%: GRANT USAGE ON *.* TO `app1`@`192.168.91.%`*************************** 2. row ***************************Grantsfor app1@192.168.91.%: GRANT SELECT, INSERT, UPDATE, DELETE ON `a1`.* TO `app1`@`192.168.91.%`*************************** 3. row ***************************Grantsfor app1@192.168.91.%: GRANT SELECT ON `a2`.`b2` TO `app1`@`192.168.91.%`*************************** 4. row ***************************Grantsfor app1@192.168.91.%: GRANT SELECT (`sid`) ON `a3`.`b3` TO `app1`@`192.168.91.%`4 rows in set (0.00sec)
ERROR:
No query specified
三 客户端验证
#我这里再找1台1同网段的虚拟机安装mysql客户端做测试,这里安装的是mariadb的客户端,远程连接数据库服务器
#这里数据库a1我就不做测试了,直接从a2开始
[root@localhost ~]# mysql -u app1 -h192.168.91.128 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection idis 19Server version:8.0.13 MySQL Community Server -GPL
Copyright (c)2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c'to clear the current input statement.
MySQL [(none)]>show databases;+--------------------+
| Database |
+--------------------+
| a1 |
| a2 |
| a3 |
| information_schema |
+--------------------+
4 rows in set (0.00sec)
MySQL [(none)]>use a2;
Reading table informationforcompletion of table and column namesYou can turn offthis feature to get a quicker startup with -A
Database changed
MySQL [a2]> insert into a2 (sid,name) values (1,'xjp');
ERROR1142 (42000): INSERT command denied to user 'app1'@'192.168.91.129' for table 'a2'#这里就说明了没有insert权限
#测试a3,我们需要用root用户插入数据,我们再用app1用户做测试
mysql>use a3;
Database changed
mysql>show tables;+--------------+
| Tables_in_a3 |
+--------------+
| b3 |
+--------------+
1 row in set (0.00sec)
mysql> insert into b3 (sid,name) values (1,'xjp');
Query OK,1 row affected (0.07sec)
mysql> insert into b3 (sid,name) values (2,'mzd');
Query OK,1 row affected (0.01sec)
mysql> select * fromb3;+------+------+
| sid | name |
+------+------+
| 1 | xjp |
| 2 | mzd |
+------+------+
2 rows in set (0.00 sec)
#OK,这里我们插入了2条数据,这里的需求是a3数据库b3表上sid字段的查询权限,那我们就做如下的测试
用客户端登录
MySQL [(none)]>use a3;
Reading table informationforcompletion of table and column namesYou can turn offthis feature to get a quicker startup with -A
Database changed
MySQL [a3]>show tables;+--------------+
| Tables_in_a3 |
+--------------+
| b3 |
+--------------+
1 row in set (0.00sec)
MySQL [a3]> select * fromb3;
ERROR1142 (42000): SELECT command denied to user 'app1'@'192.168.91.129' for table 'b3'MySQL [a3]> select name fromb3;ERROR1143 (42000): SELECT command denied to user 'app1'@'192.168.91.129' for column 'name' in table 'b3'看到了吧!不给查,被拒绝了,那我们只查sid再试一次
MySQL [a3]> select sid fromb3;+------+
| sid |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec
看到了吧!奇迹出现了!
(2)如果要回收该用户在a2数据库里b2表的查询权限,语句怎么写?
mysql> revoke select on a2.b2 from 'app1'@'192.168.91.%';
Query OK,0 rows affected (0.07 sec)
那我们再看下权限
mysql> show grants for 'app1'@'192.168.91.%'\G;*************************** 1. row ***************************Grantsfor app1@192.168.91.%: GRANT USAGE ON *.* TO `app1`@`192.168.91.%`*************************** 2. row ***************************Grantsfor app1@192.168.91.%: GRANT SELECT, INSERT, UPDATE, DELETE ON `a1`.* TO `app1`@`192.168.91.%`*************************** 3. row ***************************Grantsfor app1@192.168.91.%: GRANT SELECT (`sid`) ON `a3`.`b3` TO `app1`@`192.168.91.%`3 rows in set (0.00sec)
ERROR:
No query specified
我们发现a2.b2没有读的权限了
我们用客户端验证下
[root@localhost ~]# mysql -u app1 -h192.168.91.128 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection idis 21Server version:8.0.13 MySQL Community Server -GPL
Copyright (c)2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c'to clear the current input statement.
MySQL [(none)]>show databases;+--------------------+
| Database |
+--------------------+
| a1 |
| a3 |
| information_schema |
+--------------------+
3 rows in set (0.00sec)
是的,你没看错,a2连看都看不到了
我强制试试
MySQL [(none)]>use a2;
ERROR1044 (42000): Access denied for user 'app1'@'192.168.91.%' to database 'a2'
看到了吧,权限直接拒绝了