MySql 权限管理

    本文实例演示 MySql 权限管理。

1.user表

   user表存储了权限相关的信息。查看user表内容。

mysql> select host, user,password from  user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| %         | root |                                           |
| 127.0.0.1 | root |                                           |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

    给用户root添加密码。

update user set password=password('1234')where host='%';

     赋予来自'192.168.0.*'的用户April所有权限。

grant all on *.* to April@'192.168.0.*' identified by '1234';

     查看权限

mysql> select * from user where user = 'April' \G 
*************************** 1. row ***************************
                 Host: 192.168.0.*
                 User: April
             Password: *A4B6157319038724E3560894F7F932C8886EBFCF
          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
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

    如果觉得权限太大，可以收回权限。

revoke all on *.* from April@'192.168.0.*';

    查看权限

mysql> select * from user where user = 'April' \G 
*************************** 1. row ***************************
                 Host: 192.168.0.*
                 User: April
             Password: *A4B6157319038724E3560894F7F932C8886EBFCF
          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
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

   刷新权限。

flush privileges;

   查看user表内容。

mysql> select host, user,password from  user;
+-------------+-------+-------------------------------------------+
| host        | user  | password                                  |
+-------------+-------+-------------------------------------------+
| %           | root  | *A4B6157319038724E3560894F7F932C8886EBFCF |
| 127.0.0.1   | root  |                                           |
| 192.168.0.* | April | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-------------+-------+-------------------------------------------+
4 rows in set (0.00 sec)

2.db表

    db表存储用户对数据库的操作权限。

    赋予来自'192.168.0.*'的用户April 在test数据库中的所有权限。

grant all on test.* to April@'192.168.0.*';

    查看权限

mysql> select  * from db \G
*************************** 1. row ***************************
                 Host: 192.168.0.*
                   Db: test
                 User: April
          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
3 rows in set (0.00 sec)

    收回权限

revoke all on test.* from April@'192.168.0.*';

3.tables_priv表

     tables_priv表存储用户对表的操作权限。

     赋予来自'192.168.0.*'的用户April 在test数据库中表person的所有权限。

grant all on test.person to April@'192.168.0.*';

    查看权限

mysql> select * from tables_priv \G
*************************** 1. row ***************************
       Host: 192.168.0.*
         Db: test
       User: April
 Table_name: person
    Grantor: root@localhost
  Timestamp: 2019-05-09 21:33:16
 Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger
Column_priv: 
1 row in set (0.00 sec)

    收回权限

revoke all on test.person from April@'192.168.0.*';