【MySQL】数据库用户与权限-向日葵屋

创建用户
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)