欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【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)
//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 8.0 Reference Manual:GRANT Syntax