Yum_Yum

[DataBase] 데이터베이스 포렌식 개요

(1) 데이터베이스 포렌식 개요

• 데이터베이스 포렌식은 디지털 포렌식의 한 분야

• 데이터베이스의 구조 및 저장된 데이터, 메타데이터를 수집하여 법적 증거 자료로써의 요구 조건을 충족시키기 위해 분석하는 행위

• 주로 감사 로그를 수집하여 분석 

현재 활용 가능한 데이터베이스 포렌식 툴은 로그 분석기 수준으로 전문적인 도구는 아직까지 존재하지 않는다.

또한, 각 DBMS 벤더사별로 데이터베이스 구조가 판이하고, 데이터베이스의 트랜잭션 로그 구조 자체가 기업 보안 정보로 분류되어 구체적인 문서나 매뉴얼을 얻을 수 없다.

그리고 방대해지고 있는 어플리케이션 규모도 데이터베이스 포렌식에 있어 걸림돌이 될 수 있다.

데이터베이스에 저장된 사용자의 개인 정보 유출이 자주 발생하고, 또한 저장된 데이터를 조작하는 사고가 빈번히 발생한다.

대부분의 사고는 내부 관계자의 소행이 주를 이르고 있는 만큼 데이터베이스 포렌식의 중요성이 점점 커지고 있다.

1-1. 데이터베이스 포렌식 이해

• 데이버테이브 정보 침해 사건은 대부분 기업이나 기관 내부의 데이터베이스 관리자나 일반 사용자로 인해 일어남

• 이들은 관리자 권한을 악용해 감사 로그 기록을 우회하여 데이터베이스를 조작, 데이터 조작을 숨기기 위해 로그 기록을 중지함

• 따라서 로그파일 분석을 통해 언제, 어디서, 어떤 사용자에 의해 데이터가 조작되었는지 알아내는 것이 매우 중요

가) 왜 데이터베이스 침해사고가 일어나는가?

• 예산 부족으로 인한 관리 인력 및 보안 전문 인력 감축

• 정보 보안에 대한 이해 부족

• 부서 간의 협조 부족

• 데이터베이스 사전 보안 테스트 부족

• 너무 많은 'root' 권한 계정

• 데이터베이스 보안 관리에 대한 기술력 부족

나) 다양한 실무 환경에서 사용되는 데이터베이스

• 데이터베이스는 전자결제, 이메일, 기업 정보, 회계 압수 등에서 정보의 메타데이터 역할을 함 

데이터베이스 포렌식을 이용하여 데이터 선별이 가능한 주요 데이터들을 살펴보면 다음 그림과 같다.

1-2. 데이터베이스 조사 절차

• 1) DB 조사 기획

• 2) IT / BT 조사

• 3) IT / BT 실사

• 4) DB 증거 수집

• 5) 이송 및 보관

가) DB 조사 기획

• 정보 시스템 관련 정보 수집 및 공유

• 영장에 명시할 기업 정보 시스템 소재지 확인

• 데이터베이스 압수 수색 시 소요되는 인력과 시간 판단

• 데이터베이스 압수 방법 구체화

나) IT / BT 조사

• IT / BT는 정보 기술과 업무 기술을 뜻함

• 보유 시스템애 대한 조사 진행

• 하드웨어 정보, 네트워크 정보, 운영 중인 소프트웨어 등에 대해 조사 진행

• 운영 중인 데이터베이스에 대한 종류 및 버전과 용량 확인

• 업무 프로세스를 확인하여 동일 시스템이나 유사한 시스템이 있는지를 확인

• 외부 용역 시스템 개발자와 퇴사한 시스템 개발자 및 운영자에 대한 정보 수집

다) IT / BT 실사

• 데이터베이스 서버에 대한 메인프레임, 데스크탑 자원 정보 및 잉여 자원 보유 여부 확인

• 데이터베이스에 대한 DBA 계정, 암호 확인, 사용 권한 미 보유자의 데이터베이스 접근 가능 여부 확인

• 데이터베이스와 관련된 트랜잭션 로그 파일의 저장 위치 파악

• 데이터베이스에 대한 조작, 변조, 삭제, 은닉 및 이중 시스템 유무 파악

• 업무담당자의 PC 네트워크 정보와 프로세스 상태를 통해 이중 시스템 및 은닉 데이터베이스 찾기 가능

라) DB 증거 수집

• 현장을 통제하여 관련 없는 사람을 현장으로부터 격리하여 현장 보존과 관계자 및 입회인 참관 시킴

• 수사관이나 분석관이 바로 작업하지 않고 데이터베이스 관리자를 대동해 작업 지시를 하여 작업에 사용된 쿼리문 기록

• 원활한 협조가 이뤄지지 않을 경우 직접 쿼리문을 사용하여 조사 가능 (이때도 사용한 쿼리문을 꼭 기록해야 함)

• 특정 로그 및 설정 제어 파일은 DBMS마다 다르므로 사전에 각 DBMS 별 저장 경로 확인 및 수집

[표 1] RDBMS 별 증거 수집 파일 목록

그리고 압수 대상 데이터베이스가 원격지에 있다면, 원격지 데이터베이스의 IP 및 패스워드 등의 정보를 확인하고, 네트워크 위치와 시스템 정보를 수집한다.

원격이 아닌 데이터베이스 서버를 압수할 경우, shutdown 명령 후 운영제제를 종료한다.

과거 시점의 데이터 압수 시에는 백업 데이터를 활용하여 수집하며, 증거 수집에서 목적에 맞는 자료만 추출할 경우, 데이터베이스 또는 운영체제 CMD 상에서 명령어를 입력하여 자료 추출 및 복사한다.

추출된 결과를 엑셀과 같은 파일로 가공하면 분석 및 조사가 수월해진다.

대부분의 기업은 데이터베이스를 주기적으로 백업을 실시하기 때문에 삭제된 데이터 또한 백업 데이터를 통해 복원이 가능하다.

마) 이송 및 보관 단계

• 추출된 데이터베이스 복사본 또는 증거 파일의 해시 값을 계산하고 기록 및 확인 후에 보관

• 해시 값은 증거물이 압수 및 분석 과정에서 부당하게 훼손되거나 변경되지 않았음을 검증하는 수단

• 생성 후에는 증거 데이터 추출 시 작성된 문서 값과 비교 확인을 해야 함

• 적은 용량의 증거물일 경우, CD 또는 DVD로 제작하여 레이블에 피압수자의 서명을 받음

• 대용량 증거 파일은 분할하여 해시 값을 생성

• 또한 증거물 이송 중에 손상 방지를 위한 대책 강구 필요 (충격방지 케이스, 정전기 방지용 랩 등 활용)

데이터 추출 시 고려 사항

• 대형 범죄가 아닌 이상 규모가 큰 사이트에서 데이터베이스 전체를 복제하는 경우는 거의 없음

• 저장 용량이 크고 복제 시간도 많이 소요되는데다가 개인 정보나 기밀 정보 유출 등의 위험이 있기 때문

• 따라서 주로 미리 받은 ERD나 스키마로 데이터베이스 구조를 분석

• 그 이후 쿼리문을 입력하여 필요한 부분만 덤프 진행

• 덤프는 담당자가 직접 하거나 담당자 입회하에 수사관이 직접 수행

데이터 추출 과정에 앞서 데이터베이스 서버를 정지시킬 수 없는 경우에는 데이터베이스 덤프를 바로 시행하지 않고 해시 값을 먼저 생성하여 담당자나 소유자 확인과 서명을 받는다.

그리고 서버의 로그, 데이터베이스 테이블 space를 함께 복사하여 해당 데이터베이스 테이블을 재구성하는 데에 많은 시간이 소요되는 것을 방지한다.

레이드로 구성되어 있는 서버의 경우, 디스크를 분리하면 재구성이 어려워진다. (특히 레이드 10이나 01)

따라서 레이드가 구성되어 있는 상태에서 Encase 또는 dd로 이미징하거나 파일을 복사한다.

증거 자료를 확보한 후에는 역시 해시 값을 생성하여 관리자나 소유자에게 확인과 서명을 받아야 한다.

수집해야 할 기본 정보 리스트

• 운영체제 시스템과 관련된 정보 (시스템 시간 / 날짜, 로그인 기록, 사용자 및 그룹, 네트워크 정보)

• 실행중인 전체 프로세스 정보 (프로세스 간의 부모 자식 관계, 연결된 DLL 정보 등 확인)

• 메모리 덤프 (메모리 내에 저장된 데이터 분석을 위해 필요 시에 메모리 덤프 수행)

• 레지스트리 정보 (Windows Server를 사용하는 경우)

• 데이터베이스 서버 내 모든 로그 파일 (이벤트 로그, 메시지 로그,  etc)

은닉 데이터베이스 실행 확인

• 은닉 데이터베이스 실행 여부 확인을 우힌 방법으로 2가지 존재

• 첫 번째는 프로세스 정보를 확인하는 방법

• 두 번째는 네트워크 통신 상태를 확인하는 방법

윈도우 시스템의 경우 명령 프롬프트를 통해 [tasklist] 명령어를 통해 동작 중인 서비스를 확인할 수 있으며, 네트워크 정보는 [netstat -anob]를 통해 확인 가능하다.

그리고 리눅스의 경우 [ps -ef] 명령어로 프로세스 정보를, 네트워크 정보는 [netstat -anop]를 통해 확인 가능하다.

수집해야 할 데이터베이스 정보 리스트

• 데이터베이스 종류

• 데이터베이스 인스턴스 버전 정보

• 휘발성 정보

• 사용자 목록

• 가장 최근에 실행된 SQL 파일 사본

• 테이블 정보

• 데이터베이스 전체 덤프 혹은 데이터베이스 내부 특정 파일들

• 로그 파일 (Error, General, binary, Redo, Listener 로그 등)

• 데이터베이스 백업 파일 

주요 증거 데이터

• 휘발성 데이터베이스 Connection & Session 정보

• 데이터베이스 전체 또는 특정 데이터베이스

• 테이블 전체 또는 특정 테이블 또는 특정 레코드

• 데이터베이스의 메타데이터 또는 테이블의 메타데이터

• 데이터베이스 자체적으로 기록하는 로그 파일

• 시스템 로그 파일

• 애플리케이션 로그 파일

자료 수집 우선 순위

• 1순위 : 데이터베이스 서버 Connection & Sessions 정보

• 2순위 : 트랜잭션 로그

• 3순위 : 데이터베이스 서버 로그

• 4순위 : 데이터베이스 서버 파일

• 5순위 : 시스템 이벤트 로그

데이터베이스 연결 정보와 트랜잭션 로그

• 데이터베이스 연결 정보(접속 정보)와 트랜잭션 로그는 데이터베이스 포렌식에 있어 가장 중요도가 높음

• 이는 어떤 사용자가 데이터베이스에 접속하여 어떤 쿼리를 사용하였는지에 대한 로그를 가지고 있기 때문에 향후 사건 추적에 매우 중요한 단서가 됨

• 비 인가자가 중요 데이터를 지웠다고 가정할 경우, 연결 정보와 트랜잭션 로그를 추적하여 실마리 잡기 가능

• 단, 대규모 시스템일수록 시간이 매우 오래 소요될 수 있음 

1-3. 증거 자료 분석 절차

• 1) 추출한 데이터베이스 복사본  또는 증거 파일들의 해시 값과 문서에 기재된 해시 값을 비교

• 2) 휘발성 정보를 획득했을 경우에는 메모리, 프로세스, 파일 등의 자원 사용 분석

• 3) 데이터베이스 증거에 맞는 운영체제 및 프로그램을 구축하여 증거 파일 복제

• 4) 데이터베이스 접속 프로그램과 로그 분석 프로그램을 사용하여 자료구조, 자료 관계, 접근한 사용자 목록, 사용 기록 등의 데이터를 획득하고, 목적에 따라 자료 복구를 수행

• 5) 데이터베이스의 분석자, 분석 과정, 분석 결과 등 세부 사항 기록

1-4. 데이터 추출 관련 참고 자료

가) SAP를 활용한 데이터 추출

• 많은 대기업들이 회계, 판매, 생산, 인사, 재정 등 중요 데이터를 통합적으로 관리 및 처리하기 위하여 ERP(Enterprise Resource Planning)라는 시스템 사용

• 데이터베이스 포렌식에서는 ERP 데이터 추출을 위해, ERP 관련 제품 시장에서 가장 큰 점유율을 차지하는 SAP 사 S/W 사용

SAP 기능

• GUI 기반으로 SE16, SE11 등 T-CODE라는 SAP 상에서 미리 정의된 추출 기능 사용

• T-CODE 또는 트랜잭션 코드로 SAP 메뉴의 커맨드 필드에 명령을 입력하여 원하는 작업을 보다 빨리 수행

• 시스템 작업을 경로를 찾아 들어가 수행하지 않아도 되는 바로가기와 비슷한 기능이라고 보면 됨

• 약 200,000가지의 T-CODE가 존재하여 거의 모든 트랜잭션 기능 제공

• 사용자가 T-CODE를 커스터마이징할 수 있지만 영문자, 숫자 조합 20자까지 가능

• SE11 / 16은 관련 T-code 실행 권한이 필요하며, 데이터 용량이 큰 경우 런타임 오류가 발생할 가능성 있음 

SE16은 일반 테이블을 조회하는 SAP T-CODE를 의미하며, SE11은 ABAP Dictionary를 조회하는 SAP T-CODE를 의미한다.

ABAP (Advanced Business Application Programming)

• SAP에서 개발한 SAP만을 위한 프로그래밍 언어로 데이터를 추출하는 데 사용

• 서버 사이드 프로그래밍 언어로 개발을 위한 환경인 SAP Application Server에 접속하기 위한 SAP GUI 필요

• SAP의 3가지 테이블 유형 모두의 데이터 추출 가능

SAP의 3가지 테이블 유형으로는 Transparent, Pool 그리고 Cluster라는 3가지 테이블 유형이 있다.

Transparent는 오직 하나의 테이블이 존재하며 데이터베이스와 1대1 관계를 형성한다.

주로 중요한 정보를 담은 마스터 데이터를 저장할 때 사용한다.

Pool은 커스터마이징 데이터나 자잘한 시스템 데이터와 같이 테이블 자체는 작지만 수가 많을 경우 사용하며, 데이터베이스와 다대일 관계를 형성한다.

Cluster는 큰 시스템 데이터와 같이 테이블이 크고 수가 적은 경우에 사용하며, 데이터베이스와 다대일 관계를 형성한다.

SQL Query

• RDBMS에 직접 접근해 SQL 명령어를 입력하는 것으로 데이터 추출

• 데이터베이스 구조(인덱스, 키 ,테이블, 데이터 관계 등)에 대한 이해 필요

• SAP 테이블 중 Cluster 테이블과 Pool 테이블에 대한 데이터 추출은 편리하지 않음

ACL과 SAP Direct Link / ODBC (Open Database Connectivity)

• ACL 사에서 개발한 Direct Link 소프트웨어로 모든 데이터에 접근하여 추출하는 방법

• 반복적 업무를 batch 파일로 만들어 수행하는 것이 가능

• 별도의 IFD(Input File Definition)가 없어 편의성이 높음

• 하지만, 수사관의 PC 및 데이터 압수 대상 회사의 컴퓨터에 해당 S/W가 설치되어 있어야만 함

• ODBC는 ACL에서 기본적으로 제공하는 프로그램

• SAP 테이블 중 Cluster 테이블과 Pooled 테이블에 대한 데이터 추출이 편리하지 않음

나) Oracle ERP 데이터베이스 압수 체계

데이터 추출 방법

• Standard Report 기능 이용 : Oracle Web Browser를 통해 압수 대상 데이터와 관련한 Standard Report를 다운로드하며, Data sources and relationship에 대한 지식이 필요하고 다운로드할 수 있는 레코드 길이에 제한 있음

• SQL Query 이용 : RDBMS에 직접 접근하여 정의된 명령어를 사용하여 데이터를 추출하는 방법이며, 데이터베이스 구조(index, keys, tables, data relations)에 대한 이해 필요

다) Oracle의 전표, 기본 데이터 테이블 및 압수 방법

[표 2] Oracle의 전표, 기본 데이터 테이블

# Reference

http://www.yes24.com/Product/Goods/87579830

[DataBase] 데이터베이스 구조

(1) 데이터베이스 구조

• 데이터베이스를 물리적 공간을 이해하기 쉽게 논리적으로 구체화

• Table Space, Segment, Extent, Block 단위로 나뉨

테이블 스페이스(Table Space) 안에는 데이터 파일이 존재하며, 데이터 파일은 세그먼트(Segment)로 구성된다.

각각의 세그먼트는 익스텐트(Extent)로 구성되고, 익스텐트는 블록(Block)으로 구성된다.

상위 개념과 크기 순서로 나열하면 다음과 같다.

• 1. 테이블 스페이스 (Table Space)

• 2. 데이터 파일 (Data File)

• 3. 세그먼트 (Segment)

• 4. 익스텐트 (Extent)

• 5. 블록 (Block)

1-1. Data Block (Block 또는 Page)

• 데이터베이스에서 데이터를 저장하는 가장 작은 단위 데이터 블록

• 물리적 디스크 공간 크기에 따라 데이터 블록 크기도 결정됨

• 위 그림을 예로 들자면, 데이터 블록의 크기는 2KB

• 데이터 블록은 담고 있는 데이터를 추적하고 비어있는 공간을 수월하게 찾기 위해 일정한 형식을 가짐

사용자가 입력한 데이터는 테이블에 저장되고 테이블은 물리적 파일 안에 데이터 블록 단위로 분할되어 저장된다.

데이터 블록은 헤더와 Row 디렉토리로 구성되며, 헤더는 각 블록에 대한 기본 정보를 담고 있고, Row 디렉토리는 각각의 Row를 가리키는 오프셋 목록을 가지고 있다.

Row 디렉토리 안에는 Flag(플래그) 값도 포함되어 있는데, 플래그 값은 해당 Row의 삭제 여부를 알려준다.

데이터 블록의 구조는 다음 그림과 같다.

가) Block Header

• 물리 디스크 주소나 데이터 블록에 대한 일반적인 정보를 담음

• 블록을 업데이트한 모든 트랜잭션에 대해서는 트랜잭션 엔트리가 필요하며, 그것에 대한 정보 또한 Block Header가 담음

• 대부분 운영체제에서 트랜잭션 엔트리는 약 23Byte 정도가 필요

나) Table Directory

• 데이터 블록 안에 레코드 데이터가 저장된 테이블에 대한 메타데이터를 담고 있는 영역

• 하나의 데이터 블록은 여러 테이블의 레코드를 저장하고 있을 수 있음

다) Row Directory

• 데이터 블록 특정 위치에 저장된 레코드 데이터 위치 정보를 담고 있는 영역

• 해당 영역은 한 번 레코드 삭제 이후에도 재할당 받을 수 없음

• 새로운 레코드가 데이터 블록에 추가되었을 때만 해당 공간을 다시 사용 가능

• 각각의 Row는 Row Directory에 있는 슬롯을 가짐

• 슬롯은 Row 데이터의 시작을 가리킴

Block Header, Table Directory, Row Directory를 총칭하여 블록 오버헤드(Block Overhead)라고 한다.

일부는 고정된 크기를 갖지만, 총 사이즈는 가변적이다.

대부분 블록 오버헤드의 총 사이즈는 84에서 107Byte 정도이다.

라) Row Data

• 테이블 레코드나 인덱스 키 엔트리 등과 같은 실제 데이터를 담고 있는 곳

• 모든 데이터 블록이 내부 구조로 되어 있듯이 모든 레코드들도 데이터베이스가 원하는 데이터를 수월하게 찾기 위한 형식 존재

• 하나의 Row는 여러 개의 Row 조각들로 구성

• 각각의 Row 조각은 Row 헤더와 Column 데이터로 구성 

마) Row Header

• 데이터베이스에 저장된 Row Piece들을 관리하는 데 필요한 영역

• Row Piece에 있는 Column들과 데이터 블록 안에 저장된 다른 Row Piece에 대한 정보, 클러스터 키 정보를 가지고 있음

• 블록 하나를 온전히 차지하는 Row는 최소 3Byte의 Row 헤더를 가짐

일반적으로 Row는 하나의 데이터 블록에 담아지는 크기라 보통 하나의 Row Piece로 저장하지만, 하나의 데이터 블록에 담기에는 Row의 크기가 너무 크거나 업데이트로 인해 기존 Row 크기가 블록 크기를 넘어갈 경우, 여러 개의 Row Piece에 데이터를 저장한다.

RowID는 데이터베이스가 각 Row를 고유하게 식별하기 위한 것이다.

데이터베이스가 Row에 접근하기 위한 정보를 담고 있으며, 각 Row의 물리 주소를 Base64로 인코딩한 형식을 사용하며 확장형 RowID는 데이터 오브젝트 넘버를 포함한다.

ROWID 값으로 AAAR59AABAAALohAAA가 나왔다고 가정하자.

ROWID는 4부분으로 나뉘며, 다음 그림과 같이 나눌 수 있다.

[표 1] ROWID 구분과 설명

바) Column Data

• Row의 실제 데이터를 담는 공간

• Column 데이터는 CREATE TABLE로 명시한 Column 이름 순서대로 넣어짐

1-2. Extent

• 데이터 블록이 연속적으로 할당된 것

• 24KB의 익스텐트는 12개의 2KB 데이터 블록을 연속적으로 할당 받은 것이 됨

• 72KB의 익스텐트라면 데이터 블록 36개가 연속적으로 할당된 것

• 특정 타입의 정보를 저장하기 위한 단위

1-3. Segment

• 데이터베이스 테이블의 특정 오브젝트를 담기 위해 할당되는 단위 (한 개 이상의 익스텐트 모음)

• 해당 테이블 안의 데이터는 데이터 세그먼트에 저장

• 테이블의 인덱스 정보는 인덱스 세그먼트에 저장

• 모든 데이터베이스 오브젝트는 각각의 세그먼트에 저장

1-4. Table Space

• 실제 데이터를 물리적으로 저장하는 공간

• 데이터베이스의 레코드와 테이블 스키마 정보가 이곳에 들어 있음

# Reference

http://www.yes24.com/Product/Goods/87579830

[DataBase] 데이터베이스 포렌식 도구

(1) 데이터베이스 포렌식 도구

• 데이터베이스는 DBMS마다 다르고 용량이 커서 일반 포렌식 도구로 분석 수행 시 어려움이 있음

• 각 DBMS의 감사와 관리 기능을 보완하는 도구를 사용하여 분석해야 함

1-1. Oracle LogMiner

• 오라클 데이터베이스 버전 8i부터 사용할 수 있는 감사 도구

• SQL 쿼리문을 적절하게 입력 > 바이너리 형식으로 된 Redo 로그를 분석할 수 있음

• 분석뿐만 아닌, 변경 이전 시점으로 데이터를 되돌리는 데에도 유용하게 사용

1-1-1. LogMiner 기능

• 데이터베이스의 변경 기록을 트랜잭션 / 사용자 / 테이블 / 시간대 별로 확인 가능

• 데이터가 언제 변경되었는지 확인 가능

• 시간의 흐름에 따른 데이터 성향과 패턴 분석 용이

참조 사이트 : www.dba-oracle.com/t_cbo_using_logminer.htm  

1-2. Toad

• Quest Software사에서 개발한 데이터베이스 관리 도구

• 자동화 작업, 직관적 작업 흐름, 빌트인 기능 등으로 생산성을 극대화

• 원하는 데이터에 빠르게 접근 가능하며, 서버나 데이터, 스키마를 동기화하고 비교하여 어떤 부분이 변했는지 쉽게 확인 가능

• 자동화 기능으로 반복적 비교 작업, 쿼리문 튜닝, SQL 스캔 및 최적화를 수행

• 지원하는 DBMS는 Oracle, SQL Server, DB2, SAP, MySQL

참조 사이트 : https://www.toadworld.com/

1-3. Adminer

• PHP로 작성된 무료 도구

• 데이터베이스, 테이블, 릴레이션, 인덱스, 사용자를 관리하는 데 사용

• 사용자 이름과 비밀번호를 입력하여 데이터베이스 서버에 접속, 새로운 데이터베이스 생성, 기존 데이터베이스 조작 등이 가능

• 지원하는 DBMS는 MySQL, MSSQL, Oracle, MongoDB, PostgreSQL 등

참조 사이트 : www.adminer.org/en/editor/  

1-4. SQLite Database Browser

• 오픈 소스 기반의 SQL 도구로 SQLite 데이터베이스 파일 생성 및 변경 가능

• 애플리케이션 자체에서 사용된 SQL 명령어 기록을 보는 것이 가능

• 기본 기능은 SQLite 형식의 데이터베이스 파일 내부 인덱스, 레코드 등을 분석

• csv 파일에서 테이블, SQL 덤프 파일에서 데이터베이스를 import 또는 export하는 기능

참조 사이트 : www.sqlitebrowser.org/dl/  

1-5. Sequel Pro

• Mac OS를 위한 오픈 소스 기반의 MySQL/MariaDB 데이터베이스 관리 도구

• 설치와 수행이 빠르고, 초보자도 사용하기 쉬운 GUI 방식으로 구성

• 프로그램을 실행하기 위한 Java가 필요하지 않고, 클라우드 서비스 지원

• MySQL 데이터베이스를 로컬 또는 원격으로 접속 가능

참조 사이트 : www.sequelpro.com

# Reference

http://www.yes24.com/Product/Goods/87579830

[DataBase] DCL (데이터 제어어)

(1) DCL (Data Control Language, 데이터 제어어)

• 데이터베이스 접근 및 사용에 대한 권한 할당이나 회수하는 명령어

• GRANT, REVOKE, COMMIT, ROLLBACK 등이 있음

• COMMIT, ROLLBACK은 TCL(Transaction Control Language)로 분류되는 경우도 있음

TCL : 데이터베이스 상태를 변화시키는 논리적 작업 단위

가) GRANT

• 사용자에게 데이터베이스 오브젝트에 대한 권한 부여

privilege_name

• 사용자에게 부여된 접근 권한

• 접근 권한은 시스템 권한과 오브젝트 권한으로 나뉨

[표 1] 시스템 권한과 오브젝트 권한

Object_name

• TABLE, VIEW, STORED PROC, SEQUENCE와 같은 데이터베이스 오브젝트 이름

user_name

• 권한을 부여받을 사용자 이름

PUBLIC

• 모든 사용자에게 권한을 부여할 경우 사용

ROLES

• 권한들을 하나의 그룹으로 묶은 것

데이터베이스 사용자 수가 많을 때 권한 부여 및 회수를 좀 더 편리하게 한다.

다음은 몇 가지 시스템 ROLE에 들어있는 권한 목록이다.

[표 2] Microsoft Access 데이터 타입

RESOURCE : 주로 데이터베이스 오브젝트에 대한 접근을 제한할 때 사용

WITH GRANT OPTION

• 해당 사용자가 다른 사용자에게 접근 권한을 부여할 수 있게 함

나) REVOKE

• 사용자의 데이터베이스 오브젝트에 대한 접근 권한을 회수

다) COMMIT

• RDBMS의 트랜잭션을 종료시키고 데이터베이스 내의 변화 내용이 다른 모든 사용자가 볼 수 있도록 함

• 데이터 변경 시 아직 임시적인 상태로 있는 데이터를 저장 > 데이터가 변경 및 저장되었음을 확실히 함

• 여기서 트랜잭션은 데이터베이스의 가장 작은 작업 단위이며, 데이터베이스에 내린 명령어의 연속

• 트랜잭션은 프로그래머에 의해 수동적으로 수행될 수 있으며, 자동화 프로그램을 사용하여 자체적 수행도 가능

• 보통 이전 COMMIT부터 이후 COMMIT 이전까지를 트랜잭션으로 봄 

DELETE 명령을 수행 후에 COMMIT을 입력하면, 해당 테이블에서 student_id가 '1420891'인 레코드를 삭제한 변경 상태를 확실하게 저장한다는 의미가 된다.

라) ROLLBACK

• 말 그대로 이전 작업을 되돌리는 명령

• 이전 COMMIT 또는 ROLLBACK 명령 전까지의 상태를 복구할 수 있다.

• COMMIT과 사용법이 같음

student_info 테이블에 'Hannah'라는 학생 정보를 넣었다가 ROLLBACK 명령을 입력하면 테이블 갱신 이전 상태로 돌아가게 된다.

여기까지 SQL의 DDL, DML, DCL을 모두 확인하였다.

데이터베이스 포렌식을 효율적으로 하기 위해서는 SQL 쿼리문을 능숙하게 사용할 수 있어야 한다.

# Reference

http://www.yes24.com/Product/Goods/87579830

[DataBase] DML (데이터 조작어)

(1) DML (Data Mgnipulation Language, 데이터 조작어)

• 데이터베이스의 데이터 조회나 검색하는 명령어 SELECT

• 데이터 삽입, 수정, 삭제하는 명령어 INSERT, UPDATE, DELETE

가) SELECT

• 데이터베이스에서 원하는 데이터 조회

• 다양한 활용 가능

• 매우 자주 쓰이는 명령어 중 하나

SELECT는 원하는 데이터를 뽑아내고 자 할 때 가장 많이 쓰는 쿼리 명령어로 WHERE, LIKE, HAVING, JOIN 등의 구문과 함께 쓰일 수 있다.

SELECT를 다음과 같이 다양하게 활용 가능하다.

AS

• SELECT로 선택한 Column에 별칭을 부여하는 역할

• 결과 테이블이 출력될 때 Column 이름은 별칭으로 나타남

ORDER BY

• 결과 테이블의 레코드를 오름차순 또는 내림차순으로 정렬 

WHERE

• SELECT 구문에 조건을 추가할 때 사용

• 일반적 구문 형식은 가장 기본적인 select ~ from 구문 뒤에 where 키워드와 조건을 붙임

조건식은 주로 'Column 이름', '연산 기호', '조건 값' 순서로 온다.

LIKE

• WHERE 구문 안에 위치

• SELECT에서 뽑은 Column 안의 문자열 데이터의 패턴을 명시하여 찾을 때 사용

• 조건절에서 어떻게 사용하느냐에 따라 수많은 로그에서 내가 원하는 로그만을 출력할 때 매우 유용하게 사용

문자열의 패턴을 명시할 때는 와일드카드라는 것을 사용한다.

DB 종류마다 와일드카드가 조금씩 다르며, 다음 표와 같이 대표적인 와일드카드가 있다.

[표 1] 와일드카드 설명

MS Access의 경우 %는 *로, _는 ?로, ^는 !로 사용하며, 하나의 숫자 문자를 나타낼 때는 #을 사용한다.

AND, OR, NOT

• WHERE 구문과 함꼐 조건문을 만족하는 레코드만을 뽑아낼 때 사용

BETWEEN

• WHERE 구문에서 조건을 명시할 때 값의 범위 표현

IN

• WHERE 구문의 조건에서 다양한 값을 명시할 때 사용

IN 안에 또 다른 SELECT 구문을 사용하여 조건에 여러 값을 명시할 수 있다.

IN 안의 SELECT 구문이 가장 먼저 수행 된다.

먼저 email_list 테이블에서 email 문자열 데이터가 '@gmail.com'으로 끝나는 것들 중 user_name을 뽑아낸다.

여기서 뽑아낸 데이터들이 바깥에 있는 WHERE 구문 emp_name의 조건 값이 된다.

만약 IN 안의 SELECT 구문을 실행하여 얻어낸 데이터들이 'Betty', Carol', 'Demian' 이라면 employee_table에서 emp_name이 'Betty', 'Carol', 'Demian'인 레코드의 emp_name과 emp_num 정보를 보여주는 결과 테이블이 출력된다.

COUNT, SUM, AVG, MIN, MAX

• 다음과 같은 함수를 집계 함수라고 하며, 숫자 데이터를 계산할 때 유용

• COUNT는 조건을 만족하는 모든 레코드의 개수를 셈

• SUM은 숫자 값을 담는 Column의 데이터 총 합을 계산

• AVG는 숫자 값을 담는 Column의 데이터 평균 값을 계산

• MAX는 해당 Column의 가장 큰 값을 반환

• MIN은 해당 Column의 가장 작은 값을 반환

HAVING ~ GROUP BY

GROUP BY는 주로 위에서 소개한 집계 함수와 함께 쓰인다.

집계 함수의 결과를 특정 Column을 기준으로 그룹을 짓는 역할을 한다.

HAVING은 GROUP BY와 함께 쓰이며 집계 함수 조건절을 추가할 때 사용한다.

[표 2] Group By 실행 결과 테이블

JOIN

• 둘 이상의 테이블이 서로 연관된 Column을 가지고 있을 때 JOIN 명령으로 테이블들의 레코드 결합 가능

테이블 1 : Order_Info

테이블 2 : Customer_Info

위 두 개 테이블에서 동일한 Column은 cust_num이다

따라서 cust_num을 기준으로 두 개의 테이블을 결합할 수 있다.

JOIN은 결합 형식에 따라 INNER JOIN, LEFT JOIN, RIGHT JOIN이 있다.

INNER JOIN

• 두 테이블에 모두 매치되는 값을 가진 레코드만 반환

LEFT (OUTER) JOIN

• 반드시 출력되는 테이블을 결정

왼쪽 테이블은 반드시 출력되어야 하므로, Customer_Info의 cust_num과 일치하지 않는 레코드로 출력

(위 예문에서는 Order_Info)

RIGHT (OUTER) JOIN

• 반대로 오른쪽 테이블이 반드시 출력

이번에는 오른쪽 테이블(Customer_Info)이 반드시 출력된다.

나) INSERT INFO

• 테이블에 새로운 레코드를 추가

• 테이블의 Column이 어떠한 순서로 조직되어 있는지 모른다면, 테이블명 뒤에 Column을 나열

• 그 순서대로 VALUES 뒤에 추가할 데이터를 입력

만약 테이블의 Column 순서를 알고 있다면, 위 예시처럼 Column을 나열할 필요가 없다.

다만 추가할 데이터를 반드시 Column 순서대로 입력해야 한다.

다) UPDATE

• 테이블의 레코드 데이터를 갱신

WHERE 절로 데이터를 갱신할 레코드를 지정해주지 않으면 테이블의 모든 레코드 데이터가 갱신되므로 주의한다.

라) DELETE

• 테이블의 레코드 데이터 삭제

• UPDATE와 마찬가지로 WHERE 절로 갱신할 레코드를 지정해주지 않으면 테이블의 모든 레코드 데이터 삭제

# Reference

http://www.yes24.com/Product/Goods/87579830

[DataBase] DDL (데이터 정의어)

(1) SQL (Structured Query Language)

• 관계형 데이터베이스를 조작하기 위한 표준 언어

• 기본 구문 형태는 Select, 원하는 컬럼 이름, From, 테이블 이름

SQL은 대소문자를 구분하지 않으며, SELECT와 select 또는 Select 모두 같은 명령으로 인식한다.

SELECT 뒤에 *는 모든 Column을 조회한다는 의미이다.

SQL은 명령어의 역할에 따라 DDL, DML, DCL로 구분할 수 있다.

(2) DDL (Data Definition Language, 데이터 정의어)

• 데이터 구조를 정의하는 명령어

• CREATE, DROP, TRUNCATE, ALTER 등이 있음

가) CREATE

• 데이터베이스 또는 테이블 생성

데이터베이스는 다양한 데이터 타입을 담을 수 있으므로, 테이블을 생성하면서 각각의 Column에 이름과 함께 데이터 타입을 지정해주어야 한다.

[표 1] Oracle 데이터 타입

[표 2] MySQL 데이터 타입

[표 3] SQL Server 데이터 타입

[표 4] Microsoft Access 데이터 타입

나) DROP

• 데이터베이스 또는 테이블 삭제

• 내부 데이터가 모두 사라지므로 사용 시 주의 필요

• 데이터베이스 DROP할 경우 관리자 권한 필요

다) TRUNCATE

• DROP과 비슷한 명령어

• 테이블 자체는 유지하면서 내부 데이터만 전부 삭제할 때 사용

라) ALTER

• Column을 추가 또는 제거하거나 데이터 타입을 변경하는 데 쓰이는 명령어

• MODIFY의 경우 DBMS마다 구문이 조금씩 다름

1) ALTER ~ ADD

2) ALTER ~ DROP

3) ALTER ~ MODIFY

4) MSSQL / Oracle

5) SQL Server / MS Access

# Reference

http://www.yes24.com/Product/Goods/87579830