Create, authorize and delete users with mysql

Posted by IceDragon on Tue, 07 Jan 2020 11:19:05 +0100

1.mysql creates a read-only account

Create users through GRANT (create users and authorize them at the same time):

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

*. * all libraries. If only one library is authorized, such as test, it is written as test*
reader is the user name
172.16%.% is the ip network segment that is allowed to connect to this database. Of course, if ip is not limited, it is written as%. Finally
Reader 123 is the connection password.

Permission specifier
   Alter modify tables and indexes

Create create database and table

Delete delete existing records in the table

Drop discards (deletes) databases and tables

INDEX create or discard INDEX

Insert inserts a new row into the table

REFERENCE not used

Records in the Select key

Update modify existing table record

FILE read or write files on the server

PROCESS view the thread information executed in the server or kill the thread

RELOAD reloads the authorization table or clears the log, host cache, or table cache.

SHUTDOWN shut down the server

ALL owned; synonyms ALL PRIVILEGES

USAGE special "no permission" permission
To view creation records and permissions:
*************************** 1. row ***************************
                  Host: 172.16.%.%
                  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. Recycling authority

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

The same user can view permissions according to different authorization ranges:

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)
Recall authority
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)

You can see that the permission recycling is completed

Reclaim grant permissions
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. Delete user

Delete users according to different authorization scope

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.%.%';
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)

Topics: MySQL Database network