MySQL, MSSQL, Oracle 데이터베이스 스키마 객체(information_schema, sys.objects, sys.columns, tabs, cols, tab, col)

DB의 스키마 객체
DB의 데이터를 조회하기 위해서는 Select 명령과 테이블과 칼럼 정보가 필요하다. 실제로는 어떠한 테이블, 칼럼이 있는지 알 수 없기에 데이터베이스 제품별로 스키마 객체로 조회가 가능한 형태로 제공하고 있다. 

데이터베이스 스키마
데이터베이스에서 자료의 구조, 자료의 표현 방법, 자료 간의 관계를 형식 언어로 정의한 구조이다. 데이터베이스 관리 시스템이 주어진 설정에 따라 데이터베이스 스키마를 생성하며, 데이터베이스 사용자가 자료를 저장, 조회, 삭제, 변경할 때 DBMS는 자신이 생성한 데이터베이스 스키마를 참조하여 명령을 수행한다.

데이터베이스 객체
데이터베이스에서 객체는 테이블, 인덱스, 프로시져, 트리거, 뷰, 시퀀스 등 데이터베이스 내부에서 생성되어 관리되는 모든 대상을 의미한다.

MySQL 데이터베이스

Information_schema

information_schema.tables 전체

select Table_name from information_schema.tables;

출력된 테이블 목록을 꼼꼼하게 살펴보면 DB의 사용자가 생성했다고 보기 어려운 다양한 테이블이 확인된다.

 

select Table_name, Table_type from information_schema.tables;

Table_type을 추가하여 질의해보면 테이블타입 종류 3가지를 확인할 수 있다.

Base Table : 일반적인 테이블이다.

View : 뷰이다.

System View : information_schema 테이블이다

DBMS에서 자동으로 생성한 system view는 DB를 관리하기 위한 데이터가 저장되기 때문에 정보 획득에는 불필요하다고 할 수 있다. 정보 획득에 필요한 BASE TABLE만 조회하면 다음과 같다.

 

select Table_name from information_schema.tables where table_type='base table';

SQL 인젝션 공격은 제한된 환경에서 공격을 수행하기 때문에 꼭 필요한 데이터만 효율적으로 찾는 방법을 고려해야 한다. 그 중 하나가 불필요한 테이블을 배제하는 것이다.

 

information_schema.columns 전체

칼럼 정보 조회가 가능한 Columns 객체에 대해서 알아본다. columns 객체는 where 절에 특정 table_name을 지정하지 않을 경우 전체 칼럼 정보가 조회되기 때문에 'employees' 테이블을 조회한다.

select column_name from information_schema.columns where table_name='Employees';

테이블 이름을 적을때는 대소문자가 중요!

select concat_ws(',',column_name, data_type) from information_schema.columns where table_name='Employees';

data_type을 통해 해당 칼럼의 자료형을 확인할 수 있다.

MSSQL 데이터베이스
Microsoft SQL Server의 약자로 SQL Server로도 불린다. SQL Server는 information_schema와 sys.objects, sys.columns 객체에서 테이블과 칼럼의 목록 조회가 가능하다. information_schema 방식은 MYSQL와 같다

sys.objects : 고유하게 컴파일 된 스칼라 사용자 정의 함수를 포함하는 데이터베이스 내에서 만든 각 사용자 정의 스키마 범위 객체에 대한 행

sys.columns : 뷰 또는 테이블과 같이 열을 갖고 있는 개체의 각 열에 대한 행, 다음은 열을 갖고 있는 개체 형식의 목록 - 테이블 반환 어셈블리 함수, -인라인 테이블 반환 SQL 함수, - 내부 테이블, - 시스템 테이블, - 테이블 반환 SQL 함수, - 사용자 테이블, - 뷰

SYSTEM VIEW를 통한 조회
SQL Server에서 제공하는 시스템 카탈로그 뷰로 SYS.OBJECTS, SYS.COLUMNS를 이용하여 목록을 조회하는 방법이다. SYS.OBJECTS 에서는 TYPE이 U인 테이블만 조회한다. TYPE U는 사용자 정의 테이블을 의미한다

TYPE이 U인것의 전체 목록

select name from sys.OBJECTS where type='U';

시스템 카탈로그 뷰를 통한 칼럼 조회는 객체 식별을 위한 객체 번호가 필요하다. 객체 번호 조회는 OBJECT_ID 칼럼으로 확인한다.

select NAME,OBJECT_ID from SYS.OBJECTS where type='U';

조회에서 확인한 객체 번호로 이제는 칼럼의 목록을 조회하면 된다.

 

sys.columns의 전체목록

select name from sys.COLUMNs where OBJECT_ID=1269579561

sys.columns에서 칼럼의 데이터 타입은 type_name(user_type_id)를 통해서 조회가 가능하다
select name,  type_name(user_type_id) from sys.COLUMNs where OBJECT_ID=1269579561

Oracle 데이터베이스
Oracle 데이터베이스의 테이블과 칼럼 조회는 MySQL과 SQL Server와는 상이한 방식으로 테이블과 칼럼 정보를 제공하고 있다.

ALL_TABLES : 현재 사용자가 접근할 수 있는 모든 테이블에 관한 정보

USER_TABLES, TABS : 현재 사용자가 소유의 관계형테이블에 관한 정보

DBA_TABLES :  모든 데이터베이스내의 관계형 테이블에 관한 정보

테이블 정보 조회는 아래와 같다.

tabs의 전체 목록

select table_name from tabs;

칼럼 정보 조회가 가능한 스키마 객체

ALL_TAB_COLUMNS : 현재 사용자가 접근할 수 있는 모든 테이블, 뷰, 클러스트에 대한 칼럼 정보

USER_TAB_COLUMNS, COLS : 현재 사용자 소유의 모든 테이블, 뷰, 클러스트에 대한 칼럼 정보

DBA_TAB_COLUMNS : 모든 테이블, 뷰, 클러스트에 대한 칼럼 정보

 

cols의 전체 목록

select COLUMN_NAME from COLS where table_name='EMPLOYEES'

이외에도 하위 호환성을 위해서 구 버전에서 사용했던 객체인 TAS, COL을 지원한다.

select TNAME from tab

select CNAME from COL where TNAME='EMPLOYEES'