【MySQL 视频笔记10】权限
程序员文章站
2022-05-31 17:30:57
...
权限检查原理
用户登录mysql服务器分为两个阶段:
1.是否有权限连接服务器
2.是否有权限执行某些操作(如select,update等)
对于1:
服务器判断用户是否有权限连接服务器,依据三个参数
主机host,用户user,密码password
这三个参数的信息,存储在mysql.user表中
mysql> use mysql
Database changed
mysql> select host,user,password from user;
+-----------+-------+-------------------------------------------+
| host | user | password |
+-----------+-------+-------------------------------------------+
| localhost | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| % | user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| % | user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+-------+-------------------------------------------+
5 rows in set (0.04 sec)
修改主机域名和用户密码
mysql>update user set host='192.168.1.118' where user='root'; //修改主机域名
mysql>update user set password=password('123') where user='root'; //修改用户密码
mysql>flush privileges; //必须冲刷权限才能生效,因为权限表载入内存
全局授权与收回
用户登录mysql服务器时,依次会经过如下表的权限检查:
mysql库下的 user表 -> db表 -> tables_pri表
user表,管理用户是否能连接服务器的权限
db表,管理用户是否有操作某库的权限
tables_priv表,管理用户是否有操作某表的权限
-
新增用户:
grant [权限1,权限2,权限3...] on *.* to [email protected]'host' identified by 'password'
常用的表权限有 all,create,drop,insert,delete,update,select
//创建并授权一个mysql新用户
//root用户登录
C:\Users\Administrator>mysql -hlocalhost -uroot -p
有库下的,所有表的,所有权限
mysql> grant all on *.* to [email protected]'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.08 sec)
//创建用户,主机为 192.168.1.% ,用户名为 ywc ,密码为 123456 ,并授权用户 所
// all:所有权限
// on *.*:所有库及其下所有表
// to [email protected]'192.168.1.%': 用户ywc和主机192.168.1.%(%表示所有)
// identified by '123456':密码123456
mysql> select host,user,password from user where user='ywc';
+-------------+-------+-------------------------------------------+
| host | user | password |
+-------------+-------+-------------------------------------------+
| 192.168.1.% | ywc | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------+-------+-------------------------------------------+
6 rows in set (0.00 sec)
//登录新用户ywc
C:\Users\Administrator>mysql -h192.168.1.118 -uywc -p123456
//查看当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)
mysql> use mysql
Database changed
//查看用户ywc的权限
mysql> select * from user where user='ywc' \G
*************************** 1. row ***************************
Host: 192.168.1.%
User: ywc
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
1 row in set (0.00 sec)
//测试用户ywc的权限
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> use test1;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.45 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.12 sec)
- 撤消权限:
revoke all on *.* from [email protected]'192.168.1.%';
//root用户登录
C:\Users\Administrator>mysql -hlocalhost -uroot -p
//撤消用户ywc的权限
mysql> revoke all on *.* from [email protected]'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.13 sec)
mysql> select * from user where user='ywc' \G
*************************** 1. row ***************************
Host: 192.168.1.%
User: ywc
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
1 row in set (0.00 sec)
//ywc用户登录
C:\Users\Administrator>mysql -h192.168.1.118 -uywc -p123456
//Access denied 拒绝访问
mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'ywc'@'192.168.1.%' to database 'mysq
l'
非全局授权与收回
-
针对某个库做授权
上面的授权和撤销授权都是全局性的
下面将在指定库及其下的表做授权
//root用户登录
C:\Users\Administrator>mysql -hlocalhost -uroot -p
//创建用户ywc,只授权test库
mysql> grant all on test.* to [email protected]'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)
//用户ywc在全局上没有权限
mysql> select * from user where user='ywc' \G
*************************** 1. row ***************************
Host: 192.168.1.%
User: ywc
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
1 row in set (0.00 sec)
//用户ywc在test库上有所有权
mysql> select * from db \G
*************************** 1. row ***************************
Host: 192.168.1.%
Db: test
User: ywc
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
//重新登录
C:\Users\Administrator>mysql -h192.168.1.118 -uywc -p123456
//只能访问test库
mysql> use mysql
ERROR 1044 (42000): Access denied for user 'ywc'@'192.168.1.%' to database 'mysql'
mysql> use test;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
//具有test库下所有表的操作权
mysql> create table t1(id int);
Query OK, 0 rows affected (0.10 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)
撤销部分授权
//root用户登录
C:\Users\Administrator>mysql -hlocalhost -uroot -p
mysql> revoke all on test.* from [email protected]'192.168.1.%';
Query OK, 0 rows affected (0.01 sec)
//数据库级别的授权信息没有了
mysql> select * from db \G
empty set(0.00 sec)
- 针对表做授权
//root账户
//授权用户ywc对test2库的account表有select,insert,update权限
mysql> grant select,insert,update on test2.account to [email protected]'192.168.1.%';
Query OK, 0 rows affected (0.02 sec)
//ywc用户
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| test2 |
+--------------------+
3 rows in set (0.00 sec)
mysql> use test2;
Database changed
//test2库下有多张表,但只能操作account表
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| account |
+-----------------+
1 row in set (0.00 sec)
//具有account表的的增,改,查权限,没有删除权限
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 14.98 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
3 rows in set (0.10 sec)
mysql> delete from account;
ERROR 1142 (42000): DELETE command denied to user 'ywc'@'qicheng012' for table 'account'
//root用户
mysql> use mysql
//db表中没有用户ywc的信息
mysql> select * from db \G
empty set
//table_priv 表中有用户ywc对test2库的account表有Select,Insert,Update权限
mysql> select * from table_priv \G
*************************** 3. row ***************************
Host: 192.168.1.%
Db: test2
User: ywc
Table_name: account
Grantor: [email protected]
Timestamp: 2018-05-26 11:24:27
Table_priv: Select,Insert,Update
Column_priv:
上一篇: MYSQL笔记 之 用户权限管理