mariaDB 10.4 작업

user테이블에서 특정user(minion) 권한 없애고, user 삭제까지의 과정

 

 

 

minion 계정의 접속가능한 위치를 특정IP(x.x.x.x)로 고정해두어
모든 IP에서 접속 가능하도록 '%'로 바꾸기

 

 

MariaDB [mysql]> select * from user;

+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+-----------------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+-
| Host            | User        | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_prive_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv ns | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | is_role |
+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+-----------------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+-
| localhost       | mariadb.sys |                                           | N           | N           | N           | N           | N           | N               | N                | N            | N               | N                | N                | N              | N                   | N                   0 |           0 |               0 |                    0 | mysql_native_password |                                           | Y                | N       |
| localhost       | root        | *F76A3223CE4B73C745CEB02F8A0D8FB54E4B8F61 | Y           | Y           | Y           | Y           | Y           | Y               | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                   0 |           0 |               0 |                    0 | mysql_native_password | *F76A3223CE4B73C745CEB02F8A0D8FB54E4B8F61 | N                | N       |
| localhost       | mysql       | invalid                                   | Y           | Y           | Y           | Y           | Y           | Y               | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                   0 |           0 |               0 |                    0 | mysql_native_password | invalid                                   | N                | N       |
| x.x.x.x         | minion      | *5F5FAAC17A9CE02124188337777700E36F84B660 | Y           | Y           | Y           | Y           | Y           | Y               | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                   0 |           0 |               0 |                    0 | mysql_native_password | *5F5FAAC17A9CE02124188337777700E36F84B660 | N                | N       |
+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+-----------------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+-
4 rows in set (0.002 sec)

 

 

 

 

1. user를 아래와 같이 추가함

MariaDB [mysql]> grant all privileges on charlie.* to 'minion'@'%' identified by 'minion비번';
Query OK, 0 rows affected (0.001 sec)

 

 

 

1-1. user테이블 다시 조회해보니 맨밑에 HOST컬럼값이 '%' 로 설정된 User 새로생김  (근데 왜 권한은 다 N, N.. 이지..?)

MariaDB [mysql]> select * from user;
+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| Host            | User        | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | is_role | default_role | max_statement_time |
+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| localhost       | mariadb.sys |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      | N                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                                           | Y                | N       |              |           0.000000 |
| localhost       | root        | *F76A3223CE4B73C745CEB02F8A0D8FB54E4B8F61 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *F76A3223CE4B73C745CEB02F8A0D8FB54E4B8F61 | N                | N       |              |           0.000000 |
| localhost       | mysql       | invalid                                   | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | invalid                                   | N                | N       |              |           0.000000 |
| %               | root        | *FE4F2D624C07AAEBB979DA5C980D0250C37D8F63 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *FE4F2D624C07AAEBB979DA5C980D0250C37D8F63 | N                | N       |              |           0.000000 |
| x.x.x.x         | minion      | *5F5FAAC17A9CE02124188337777700E36F84B660 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *5F5FAAC17A9CE02124188337777700E36F84B660 | N                | N       |              |           0.000000 |
| %               | minion      | *5F5FAAC17A9CE02124188337777700E36F84B660 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      | N                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *5F5FAAC17A9CE02124188337777700E36F84B660 | N                | N       |              |           0.000000 |
+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
6 rows in set (0.003 sec)

 

 

 

1-2. 우선 저장?

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

 

 

 

 

1-3.  5번째 행의 user 권한 지우기

MariaDB [mysql]> revoke all on charlie.* from 'minion'@'x.x.x.x';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

 

 

 

 

1-4. 5번째 행의 user 권한이 Y, Y, Y ... -> N,N,N ... 으로 바뀜

MariaDB [mysql]> select * from user;
+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| Host            | User        | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | is_role | default_role | max_statement_time |
+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| localhost       | mariadb.sys |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      | N                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                                           | Y                | N       |              |           0.000000 |
| localhost       | root        | *F76A3223CE4B73C745CEB02F8A0D8FB54E4B8F61 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *F76A3223CE4B73C745CEB02F8A0D8FB54E4B8F61 | N                | N       |              |           0.000000 |
| localhost       | mysql       | invalid                                   | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | invalid                                   | N                | N       |              |           0.000000 |
| %               | root        | *FE4F2D624C07AAEBB979DA5C980D0250C37D8F63 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *FE4F2D624C07AAEBB979DA5C980D0250C37D8F63 | N                | N       |              |           0.000000 |
| x.x.x.x         | minion      | *5F5FAAC17A9CE02124188337777700E36F84B660 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      | N                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *5F5FAAC17A9CE02124188337777700E36F84B660 | N                | N       |              |           0.000000 |
| %               | minion      | *5F5FAAC17A9CE02124188337777700E36F84B660 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      | N                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *5F5FAAC17A9CE02124188337777700E36F84B660 | N                | N       |              |           0.000000 |
+-----------------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
6 rows in set (0.002 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

 

 

 

 

1-5. 테이블에서 5번째 행 아예 삭제(=user삭제)

MariaDB [mysql]> drop user 'minion'@'x.x.x.x';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

 

 

 

1-6. 삭제됨

MariaDB [mysql]> select * from user;
+-----------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| Host      | User        | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | is_role | default_role | max_statement_time |
+-----------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| localhost | mariadb.sys |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      | N                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                                           | Y                | N       |              |           0.000000 |
| localhost | root        | *F76A3223CE4B73C745CEB02F8A0D8FB54E4B8F61 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *F76A3223CE4B73C745CEB02F8A0D8FB54E4B8F61 | N                | N       |              |           0.000000 |
| localhost | mysql       | invalid                                   | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | invalid                                   | N                | N       |              |           0.000000 |
| %         | root        | *FE4F2D624C07AAEBB979DA5C980D0250C37D8F63 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *FE4F2D624C07AAEBB979DA5C980D0250C37D8F63 | N                | N       |              |           0.000000 |
| %         | minion      | *5F5FAAC17A9CE02124188337777700E36F84B660 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      | N                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *5F5FAAC17A9CE02124188337777700E36F84B660 | N                | N       |              |           0.000000 |
+-----------+-------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
5 rows in set (0.002 sec)

 

 

참고) 아까 1. 에서 추가한 minion계정의 권한이 다 N으로 설정되었는데

        그 이유는 DB명을 특정했기 때문임.

         아래와 같이 *로 변경하니, 권한 Y로 변경됨!

 

수정전: grant all privileges on charlie.* to 'minion'@'%' identified by 'minion비번';

                                             (특정 DB명)

 

수정후: grant all privileges on *.* to 'minion'@'%' identified by 'minion비번';

                                              (모든DB)