mysql 创建、授权、删除用户

Dear 丶 2022-05-17 04:20 213阅读 0赞

#### 1.mysql 创建只读账户 ####

通过GRANT来创建用户（创建用户同时授权）：

GRANT SElECT ON *.* TO 'reader'@'172.16.1.%' IDENTIFIED BY "reader123"

**\*.\*** 是所有库，如果是只授权某个库例如test，写成test.\*  
**reader** 是用户名  
**172.16.1.%** 是允许连接到这个数据库的ip网段,当然如果不限制ip，那就写成%  
**reader123** 是连接密码了。

###### 权限指定符 ######

Alter 　　　　　　修改表和索引 　　Create 　　　　 创建数据库和表 　　Delete 　　　　 删除表中已有的记录 　　Drop 　　 抛弃（删除）数据库和表 　　INDEX 　　　　 创建或抛弃索引 　　Insert 　　　　 向表中插入新行 　　REFERENCE 　　未用 　　Select　　　　 检索表中的记录 　　Update 　　　　 修改现存表记录 　　FILE 　　　　　　读或写服务器上的文件 　　PROCESS 　　 查看服务器中执行的线程信息或杀死线程 　　RELOAD 　　　　重载授权表或清空日志、主机缓存或表缓存。 　　SHUTDOWN　　 关闭服务器 　　ALL 　　　　　　所有；ALL PRIVILEGES同义词 　　USAGE 　　　　特殊的“无权限”权限

###### 查看创建记录及权限： ######

*************************** 1. row ***************************
                      Host: 172.16.1.%
                      User: reader
                  Password: *C40C2140D0511910F6142739820C4B7878E3F603
               Select_priv: Y
               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: mysql_native_password
     authentication_string: 
          password_expired: N
    2 rows in set (0.00 sec)

#### 2.回收权限 ####

###### 查看用户权限 ######

mysql> show grants for 'reader'@'172.16.1.%'; +--------------------------------------------------------------------------------------------------------------------+
    | Grants for reader@172.16.1.% | +--------------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'reader'@'172.16.1.%' IDENTIFIED BY PASSWORD '*C40C2140D0511910F6142739820C4B7878E3F603' |
    | GRANT SELECT ON `test`.* TO 'reader'@'172.16.1.%' | +--------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

同一个用户按不同的授权范围查看权限：

mysql> show grants for 'reader'@'%'; +-----------------------------------------------------------------------------------------------------------+
    | Grants for reader@% | +-----------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'reader'@'%' IDENTIFIED BY PASSWORD '*C40C2140D0511910F6142739820C4B7878E3F603' |
    | GRANT SELECT ON `test`.* TO 'reader'@'%' | +-----------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

###### 收回权限 ######

mysql> revoke SELECT ON `test`.* from 'fundreader'@'%';
    Query OK, 0 rows affected (0.00 sec)
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    mysql> show grants for 'reader'@'%'; +-----------------------------------------------------------------------------------------------------------+
    | Grants for reader@% | +-----------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'reader'@'%' IDENTIFIED BY PASSWORD '*C40C2140D0511910F6142739820C4B7878E3F603' | +-----------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

可以看到权限回收完毕

###### 回收grant权限 ######

mysql> show grants for 'fundread'; +---------------------------------------------------------------------------------------------------------+
    | Grants for fundread@% | +---------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'fundread'@'%' IDENTIFIED BY PASSWORD '*27A6D9517F68EAB764F6150D85E78C827BE23FB6' |
    | GRANT USAGE ON `test`.* TO 'fundread'@'%' WITH GRANT OPTION | +---------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    
    mysql> revoke grant option on `test`.* from 'fundread'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for 'fundread'; +---------------------------------------------------------------------------------------------------------+
    | Grants for fundread@% | +---------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'fundread'@'%' IDENTIFIED BY PASSWORD '*27A6D9517F68EAB764F6150D85E78C827BE23FB6' | +---------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

#### 3.删除用户 ####

按不同的授权范围删除用户

mysql> delete from user where user='reader' and host='host';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> delete from user where user='reader' and host='172.16.1.%';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> delete from user where user='reader' and host='%';
    Query OK, 1 row affected (0.00 sec)