【MySQL】数据库用户与权限

素颜马尾好姑娘i 2022-01-17 11:39 265阅读 0赞

创建用户
    mysql> create user 'tpcc'@'%' identified by 'tpcc';
    Query OK, 0 rows affected (0.02 sec)
    
    修改用户密码
    mysql> set password for 'tpcc'@'%'=password('abcd.1234');
    Query OK, 0 rows affected (0.03 sec)
    
    查询用户权限
    mysql> show grants for 'tpcc'@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*8E329B15E3C0FF9DDF7597B748CCE9473593BF60' |
    +-----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    删除用户
    mysql> drop user 'tpcc'@'%';
    Query OK, 0 rows affected (0.07 sec)
    
    权限级别分为全局，库，表，列四个层次，在指定级别授予的权限也必须在指定的级别撤销权限
    授予列的权限
    mysql> grant all (w_id) on tpcc100.warehouse to 'tpcc'@'%';
    Query OK, 0 rows affected (0.02 sec
    
    mysql> grant select (w_name) on tpcc100.warehouse to 'tpcc'@'%';
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> grant insert (w_street_1) on tpcc100.warehouse to 'tpcc'@'%';
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> select * from mysql.columns_priv;
    +------+----------+------+------------+-------------+---------------------+-------------+
    | Host | Db       | User | Table_name | Column_name | Timestamp           | Column_priv |
    +------+----------+------+------------+-------------+---------------------+-------------+
    | %    | tpcc100 | tpcc | warehouse  | w_id        | 0000-00-00 00:00:00 | Select      |
    | %    | tpcc100 | tpcc | warehouse  | w_name      | 0000-00-00 00:00:00 | Select      |
    | %    | tpcc100 | tpcc | warehouse  | w_street_1  | 0000-00-00 00:00:00 | Insert      |
    +------+----------+------+------------+-------------+---------------------+-------------+
    3 rows in set (0.00 sec)
    
    mysql> show grants for 'tpcc'@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |
    | GRANT SELECT (w_id, w_name), INSERT (w_street_1) ON `tpcc1000`.`warehouse` TO 'tpcc'@'%'            |
    +-----------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    撤销授予的列权限
    mysql> revoke select (w_id, w_name), insert (w_street_1) on tpcc1000.warehouse from 'tpcc'@'%';
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show grants for 'tpcc'@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |
    +-----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    授予表的权限
    mysql> grant all on tpcc100.warehouse to 'tpcc'@'%';
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show grants for 'tpcc'@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |
    | GRANT ALL PRIVILEGES ON `tpcc100`.`warehouse` TO 'tpcc'@'%'                                        |
    +-----------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from mysql.tables_priv where user='tpcc'\G;
    *************************** 1. row ***************************
           Host: %
             Db: tpcc100
           User: tpcc
     Table_name: warehouse
        Grantor: root@localhost
      Timestamp: 0000-00-00 00:00:00
     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)
    
    撤销授予的表权限
    mysql> revoke all on tpcc100.warehouse from 'tpcc'@'%';
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show grants for 'tpcc'@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |
    +-----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    授予库的权限
    mysql> grant all on tpcc100.* to 'tpcc'@'%';  
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show grants for 'tpcc'@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |
    | GRANT ALL PRIVILEGES ON `tpcc100`.* TO 'tpcc'@'%'                                                  |
    +-----------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from mysql.db where user='tpcc'\G;
    *************************** 1. row ***************************
                     Host: %
                       Db: tpcc100
                     User: tpcc
              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
    1 row in set (0.00 sec)
    
    撤销授予的库权限
    mysql> revoke all on tpcc100.* from 'tpcc'@'%';  
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show grants for 'tpcc'@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |
    +-----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    授予全局权限
    mysql> grant all on *.* to 'tpcc'@'%';  
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show grants for 'tpcc'@'%';
    +--------------------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                            |
    +--------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |
    +--------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from mysql.user where user='tpcc'\G;
    *************************** 1. row ***************************
                      Host: %
                      User: tpcc
                  Password: *D2B995B159B5840C27690FC435C6ACEE7B9E1348
               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: mysql_native_password
     authentication_string: 
          password_expired: N
    1 row in set (0.00 sec)
    
    撤销授予的全局权限
    mysql> revoke all on *.* from 'tpcc'@'%';
    Query OK, 0 rows affected (0.03 sec)
    
    mysql>  show grants for 'tpcc'@'%';
    +-----------------------------------------------------------------------------------------------------+
    | Grants for tpcc@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |
    +-----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

转载于:https://blog.51cto.com/13598811/2349838