MySQL权限管理

一、权限保存的位置

msyql.user		所有mysql用户的账号，密码，以及用户对全库全表的权限(*.*)
mysql.db			非mysql库的授权保存在这(db.*)
mysql.table_priv		某库某表的授权(db.table)
mysql.columns_priv	某库某表某列的授权(db.table.coli)
mysq.procs_priv		某库存储过程的授权

二、查看某个用户的全局权限

（当授权*.*时，就是全局权限）
select *from mysql.user where user=‘用户名’\G
查看root用户的全局权限：
select *from mysql.user where user=‘root’\G

mysql>  select *from mysql.user where user='root'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           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: Y
       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: mysql_native_password
 authentication_string: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
      password_expired: N
 password_last_changed: 2021-11-06 00:50:51
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

三、mysql的权限列表：

四、给用户授权：

grant 权限1，权限2…on 库,表 to 用户@主机;
grant 权限(列1,列2…) on 库,表 to 用户@主机;

库.表 表示方法：. 表示所有的数据库的所有表（代表授权了全局权限），test.* 代表test数据库的所有表，test.test 代表此test数据库中的test表

为jack账号分配test库的all权限

mysql> grant all on test.* to 'jack'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;			//刷新权限
Query OK, 0 rows affected (0.00 sec)

为jack用户分配test库test表select权限

mysql> grant select on test.test to 'jack'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;			//刷新权限
Query OK, 0 rows affected (0.00 sec)

为jack用户分配test库test表update权限(只能更改id)

mysql> grant update(id) on test.test to 'jack'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;			//刷新权限
Query OK, 0 rows affected (0.00 sec)

五、查询用户的权限

查询当前用户的权限
show grants;

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)

查询其他用户的权限
show grants for ‘用户名’@‘授权登录主机名或IP’;

查看jack的权限
mysql> show grants for 'jack'@'localhost';
+-------------------------------------------------------------+
| Grants for jack@localhost                                   |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jack'@'localhost'                    |
| GRANT SELECT ON `test`.* TO 'jack'@'localhost'              |
| GRANT ALL PRIVILEGES ON `test`.`test` TO 'jack'@'localhost' |
+-------------------------------------------------------------+
3 rows in set (0.00 sec)

六、WITH GRANT OPTION 选项

WITH GRANT OPTION 如果grant授权时有这个选项，表示这个用户可以将权限下发到其他用户；但权限不能超过自身权限。
语法格式：
grant 权限1，权限2…on 库,表 to 用户@主机 identified by ‘密码’ with grants option;

给jarry针对test库设置update下发权限
mysql> grant update on test.* to 'jarry'@'%' identified by 'jarry' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for 'jarry'@'%';
+-----------------------------------------------------------+
| Grants for jarry@%                                        |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jarry'@'%'                         |
| GRANT UPDATE ON `test`.* TO 'jarry'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

使用grant创建用户(5.7后不推荐)
语法格式：
grant 权限 on 数据库.数据表 to ‘新用户名’@‘授权主机名或IP’ identified by ‘用户密码’;

创建yom用户，密码为‘tom’，授权test.test all权限
mysql> grant all on test.test to 'tom'@'localhost' identified by 'tom';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jarry         | %         |
| jack          | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| tom           | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

mysql> show grants for 'tom'@'localhost';
+------------------------------------------------------------+
| Grants for tom@localhost                                   |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'                    |
| GRANT ALL PRIVILEGES ON `test`.`test` TO 'tom'@'localhost' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

八、回收权限

语法格式：
revoke 权限1,权限2 on 库.表 from 用户;
回收指定的权限：

回收jack用户test.*的select权限
mysql> show grants for 'jack'@'localhost'; //查看jack用户的权限
mysql> revoke select on test.* from 'jack'@'localhost';  //回收test.* select权限
mysql> flush privileges; 	//刷新权限
mysql> show grants for 'jack'@'localhost'; 	//再查看Jack用户的权限，看看是否回收

回收所有的权限
revoke all on 库.表 from ‘用户’@‘主机或IP’;

mysql> revoke all on test.* from 'jarry'@'%'; //回收test.*所有权限
mysql> flush privileges; 	//刷新权限