DBMS 6. MYSQL mysql.db 테이블 파헤치기

 

스키마를 mysql로 변경

 

mysql> use mysql
Database changed

 

 

mysql 테이블 구성 확인하기

 

mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
37 rows in set (0.02 sec)

 

 

저기서 지난 포스팅에서 본 것이 바로 계정과 관련된 user 테이블

2021.12.08 - [IT 일기 (상반기)/주요정보통신기반시설] - DBMS 5. MYSQL mysql.user 테이블 파헤치기

 

👇  👇  👇  👇  👇  👇  👇  👇  👇  👇  👇  👇 

mysql> select host, user, authentication_string from user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | debian-sys-maint | $A$005$4%qt>aFMQ+Xl'EOLK67oB6sAnqkicZJegafwTMAliEqbhEOMSu3PtSsS6 |
| localhost | juu              | $A$005$v2oz,lR_m8cfpZWdRqM3HXrxOASCsA8mi.erFAtw8u6Rr8mQ1EUsSUE8 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             |                                                                        |
| localhost | test             | $A$005$n
m;Xo0wX|X80xVP71nlcZFuXKS904s9PfMdfeSKPRwbmUVG43p8ujQB |
0.rQYVyjIAdDsrLFrHRrGxjPqghPWgKzYYtJxyt2kN3 |7Nkx%VYoE2N
+-----------+------------------+------------------------------------------------------------------------+
8 rows in set (0.00 sec)

 

 

db 테이블은 user 테이블과 달리 사용자가 이용할 데이터베이스까지 지정하는 것

그래서 두 테이블을 통해 어느 사용자가 어떤 아이디와 패스워드로 mysql dbms에 접근하는 지 파악 가능

 

db 테이블 컬럼 조회

 

mysql> show full columns from db;
+-----------------------+---------------+------------------+------+-----+---------+-------+---------------------------------+---------+
| Field                 | Type          | Collation        | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------------------+---------------+------------------+------+-----+---------+-------+---------------------------------+---------+
| Host                  | char(255)     | ascii_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
| Db                    | char(64)      | utf8_bin         | NO   | PRI |         |       | select,insert,update,references |         |
| User                  | char(32)      | utf8_bin         | NO   | PRI |         |       | select,insert,update,references |         |
| Select_priv           | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Insert_priv           | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Update_priv           | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Delete_priv           | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Create_priv           | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Drop_priv             | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Grant_priv            | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| References_priv       | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Index_priv            | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Alter_priv            | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Create_tmp_table_priv | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Lock_tables_priv      | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Create_view_priv      | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Show_view_priv        | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Create_routine_priv   | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Alter_routine_priv    | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Execute_priv          | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Event_priv            | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
| Trigger_priv          | enum('N','Y') | utf8_general_ci  | NO   |     | N       |       | select,insert,update,references |         |
+-----------------------+---------------+------------------+------+-----+---------+-------+---------------------------------+---------+
22 rows in set (0.02 sec)