이 문서는 Oracle 9i의 new feature인 ORDER SIBLINGS BY 절을 
   Hierarchical query에 사용하는 예를 통하여 특정 컬럼을 기준으로 
   Ordering된 형태로 display하는 방법을 보여준다. 


Explanation & Example

   Hierarchical query를 구현할 때 ORDER BY 절을 사용하는 것은
   Oracle 7.1 버젼부터 가능한 것이었다.
   그러나, 순서대로 ordering되지 않고 특정 컬럼(emp table의 ename)을
   기준으로 ordering하기를 원한다면 <Bulletin:10373>처럼 procedure를
   작성하여야만 하였다.
   그러나, Oracle 9i 에서는 ORDER BY 절 대신에 ORDER SIBLINGS BY 절을
   사용할 수 있어 user-defined stored procedure를 만들 필요가 없게 되었다. 

1) Ordering 하기 전의 emp table의 Hierarchical query

SQL> @a


ename                      EMPNO    MGR JOB
------------------------- ------ ------ ---------------
     KING                   7839        PRESIDENT
          JONES             7566   7839 MANAGER
               SCOTT        7788   7566 ANALYST
                    ADAMS   7876   7788 CLERK
               FORD         7902   7566 ANALYST
                    SMITH   7369   7902 CLERK
          BLAKE             7698   7839 MANAGER
               ALLEN        7499   7698 SALESMAN
               WARD         7521   7698 SALESMAN
               MARTIN       7654   7698 SALESMAN
               TURNER       7844   7698 SALESMAN


ename                      EMPNO    MGR JOB
------------------------- ------ ------ ---------------
               JAMES        7900   7698 CLERK
          CLARK             7782   7839 MANAGER
               MILLER       7934   7782 CLERK

14 rows selected.


Ordering 하기 전의 a.sql 은 다음과 같다.

   col ename format a25
   col empno format 99999
   col mgr format 99999
   col job format a15
   select rpad(' ', LEVEL*5) || ename "ename", empno, mgr, job
   from emp
   start with job='PRESIDENT'
   connect by prior empno=mgr;

   /


2) 9i의 new feature인 Hierarchical query를 사용하여 Ordering한 경우

SQL> @new_a

ename                      EMPNO    MGR JOB
------------------------- ------ ------ ---------------
     KING                   7839        PRESIDENT
          BLAKE             7698   7839 MANAGER
               ALLEN        7499   7698 SALESMAN
               JAMES        7900   7698 CLERK
               MARTIN       7654   7698 SALESMAN
               TURNER       7844   7698 SALESMAN
               WARD         7521   7698 SALESMAN
          CLARK             7782   7839 MANAGER
               MILLER       7934   7782 CLERK
          JONES             7566   7839 MANAGER
               FORD         7902   7566 ANALYST


ename                      EMPNO    MGR JOB
------------------------- ------ ------ ---------------
                    SMITH   7369   7902 CLERK
               SCOTT        7788   7566 ANALYST
                    ADAMS   7876   7788 CLERK

14 rows selected.


Ordering하기 위해 사용한 new_a.sql 은 다음과 같다.

   col ename format a25
   col empno format 99999
   col mgr format 99999
   col job format a15
   select rpad(' ', LEVEL*5) || ename "ename", empno, mgr, job
   from emp
   start with job='PRESIDENT'
   connect by prior empno=mgr
   order siblings by ename;
   /

 


HIERARCHICAL QUERY DATA의 SORT와 ORDERING

ORACLE 6에서의 HIERARCHICAL QUERY에서는 SORT를 하기 위한 ORDER BY 절을 사용
할 수 없었다. 그러나, ORACLE 7.1이상 VERSION에서는 USER-DEFINED STORED
PROCEDURE를 이용하여 HIERARCHY 순서로 출력되면서 ORDERING할 수 있게 되었다.

세계의 지역에 관한 자료를 예로 보자.

 

      CREATE TABLE UNIVERSE
      ( PARENT VARCHAR2(30) REFERENCES UNIVERSE, 
      NAME  VARCHAR2(30) PRIMARY KEY );
     
     REM SOME TEST DATA
     INSERT INTO UNIVERSE VALUES ( NULL, 'WORLD' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'WORLD', 'EUROPE' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'EUROPE', 'ENGLAND' ) ;
     INSERT INTO UNIVERSE VALUES ( 'EUROPE', 'THE NETHERLANDS' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'EUROPE', 'GERMANY' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'WORLD', 'ASIA' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'ASIA', 'JAPAN' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'ASIA', 'CHINA' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'WORLD', 'AMERICA' ) ;
     INSERT INTO UNIVERSE VALUES ( 'AMERICA', 'UNITED STATES' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'AMERICA', 'MEXICO' ) ;
     INSERT INTO UNIVERSE VALUES ( 'WORLD', 'AFRICA' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'AFRICA', 'EGYPT' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'AFRICA', 'MOROCCO' ) ;

 


위의 자료를 다음과 같이 보고자 하는 경우
WORLD
        AFRICA
                EGYPT
                MOROCCO
        AMERICA
                MEXICO
                UNITED STATES
        ASIA
                CHINA
                JAPAN
        EUROPE
                ENGLAND
                GERMANY
                THE NETHERLANDS

 

만약,ORDER BY절이 없이 QUERY하면


     SELECT RPAD( ' ', LEVEL * 5 ) || NAME FROM UNIVERSE 
     CONNECT BY PRIOR NAME = PARENT START WITH PARENT IS NULL;

다음과 같은 결과를 얻게 된다.    
    WORLD
        EUROPE
                ENGLAND
                GERMANY
                THE NETHERLANDS
        ASIA
                JAPAN
                CHINA
        AMERICA
                UNITED STATES
                MEXICO
        AFRICA
                EGYPT
                MOROCCO

만약, 위 문장에 ORDER BY 절을 사용하면

 

      SELECT RPAD( ' ', LEVEL * 5 ) || NAME FROM UNIVERSE
      CONNECT BY PRIOR NAME = PARENT START WITH PARENT IS NULL 
      ORDER BY NAME;

 

다음과 같은 원치 않는 결과를 얻게 된다.    
        AFRICA
        AMERICA
        ASIA
                CHINA
                EGYPT
                ENGLAND
        EUROPE
                GERMANY
                JAPAN
                 MEXICO
                MOROCCO
                THE NETHERLANDS
                UNITED STATES
     WORLD

 

7. 1이상 VERSION에서는 다음과 같이 USER DEFINED FUNCTION을  이용하여

원하는 자료를 얻을 수 있다.

 

CREATE OR REPLACE FUNCTION UNIVERSESORTORDER( PKEY UNIVERSE.NAME%TYPE ) 
     RETURN VARCHAR2 IS
        PATH VARCHAR2(2000);
     BEGIN
        PATH := PKEY;
        -- INSERT ALL PREVIOUS PARENT RECORDS LIKE A DIRECTORY STRUCTURE
        -- E.G. WORLD/EUROPE/...
        FOR CREC IN ( SELECT PARENT FROM UNIVERSE 
                      CONNECT BY PRIOR PARENT = NAME
                      START WITH NAME = PKEY ) LOOP
           PATH := CREC.PARENT || '/' || PATH;
        END LOOP;
        RETURN PATH;
     END;
     /
     
SELECT SUBSTR( RPAD( ' ', LEVEL * 5) || NAME, 1, 40) "THE UNIVERSE"
       FROM UNIVERSE
       CONNECT BY PRIOR NAME = PARENT
       START WITH PARENT IS NULL
       ORDER BY UNIVERSESORTORDER( NAME ) ;


저작자 표시 비영리
Posted by 티엘로

"복구에 실패한 DBA는 용서받을 수 있어도, 백업에 실패한 DBA는 용서받을 수 없다"

 

이 길을 들어서서 제일 먼저 사수에게 들었던 전설과 같다는 말이다.

아무리 시스템의 성능이 고도화되고, 성능개선을 위해 숱한 시간을 허비했어도, 다음날 출근했을 때 어제의 작업, 심지어 그동안의 모든 작업이 허사가 되도록 시스템이 주저앉아버린다면 아무런 소용도 없게 된다.

 

본 문서는 ORACLE사의 RDBMS를 사용하는 사용자 및 운영자들이 적절한 수준에서 백업전략을 수립하고, 이에 맞는 복구전략을 숙지할 수 있도록 가이드를 하는 목적으로 작성하게 되었다.

 

본 장은 일반적인 데이터베이스 백업전략에 대해 정리해 보았다.

 

1. DATABASE-데이터베이스  백업

  1.1  백업의 개요 및 목적

 

 데이터베이스 백업이라 함은 기간시스템의 장애가 발생할 경우 이를 복구하기 위한 보험과 같은 개념이다. 관계형 데이터베이스를 사용하는데 있어서 가장 큰 장점중의 하나는 데이터베이스의 이상 발생시 언제든지 데이터베이스 RECOVERY를 수행하여 현재의 상황으로 복구할 수 있다는 점이다. 이러한 복구가 가능하기 위해서는 데이터베이스 관리자는 복구가 가능한 상태로 데이터베이스를 운용하여야 한다. 예를 들어 사용자가 NO ARCHIVE MODE로 운용할 경우에는 불행히도 데이터베이스를 처음 생성한 시점이나 전체 백업 받은 시점으로만이 복구가 가능하기 때문이다.

일반적인 경우 백업 정책이 없이 무작정 과다한 양의 백업을 받을 경우 일정 기간이 경과하면 백업에 대한 의미가 희미해지게 되고 정상적인 작업을 수행하지 않을 때, 백업파일이 꼭 필요한 경우 작업을 할 수 없는 경우가 발생할 수도 있다.

데이터베이스 관리자는 백업에 대한 정책을 수립하여 꼭 필요한 데이터를 최소의 약으로 백업을 받고 최소의 시간을 소비- 고객의 MTTR(MEAN TIME TO RECOVERY)을 만족할 수 있는 시간-하면서도 항시 복구가 가능한 상태를 유지하여야 한다.

 

1.2 주요 고려사항

 

데이터베이스는 기존의 파일시스템과는 달리 전체 사용자 OBJECT를 하나의 TABLESPACE로 관리하거나 필요에 따라 나누어 사용 및 관리하므로 백업뿐 아니라 복구 시에도 상당히 주의를 요한다. 만일 ARCHIVE LOG 상태에서 운용하고 있는 상태에서 이상이 발생할 경우 복구작업에 필요한 LOG FILE중에 하나의 파일이라도 없어지거나 사용할 수 없는 경우에는 정상적인 복구가 불가능하게 된다.

이러한 불행한 경우를 방지하기 위해서 DBA는 항시 복구가 가능한 상태로 작업하기 위한 백업정책을 수립하여 정확하게 작업하여야 한다.

또한 24 X 7(1 365) DOWN TIME없이 운용되는 시스템의 경우 백업 정책의 수립에 COLD BACKUP과 같은 FULL IMAGE 백업이 불가능 할 수 있기에 HOT BACKUP 혹은 EXPORT를 통한 LOGICAL 백업만이 가능할 수 있다. 이런 제약사항으로 인해 혹시 발생할 수 있는 장애에 적절한 대응을 하지 못할 수 있다.

이러한 결정상황을 파악하여 백업정책 수립에 심혈을 기울여야 할 것이며 , 시스템 운용의 묘를 살려야 할 것이다.

또한 HOT BACKUP등의 ONLINE상에서 데이터베이스를 백업하기 위해서는 반드시 ARCHIVE MODE로 운영되어야 한다.

 

 

1.3 백업 전략

 

DBA가 어떠한 방법으로 백업을 유지하느냐에 따라 복구 성공률이나 복구 속도 등이 결정된다 물론 매일 작업 종료 후 전체 데이터베이스에 대하여 FULL BACKUP을 한다면 가장 안전한 백업이라고 볼 수 있으나 실질적으로 백업을 받는데 많은 시간을 요구하므로 현실적으로는 불가능한 작업이라 볼 수 있다.

: XXX시스템의 특징은 24시간 365일 무중단 운영을 원칙으로 하고 있고, 타 시스템과의 INTERFACE 대상의 DATA LOAD가 야간에 대량으로 발생하며, 정산 및 온라인 통계작업을 통한 대량의 TRANSACTION이 발생한다.이는 다시 말해 COLD BACKUP을 위한 시스템 중단이 사실상 불가능하다는 말과 같다.

 

이런 시스템의 특성을 반영한 백업시스템 정책은 현실적으로 적용 가능한 HOT BACKUP을 업무가 집중하지 않는 시간에 수행하는 것으로 정하여야 하며, 단위 업무별로 대량의 변화가 발생할 경우에 데이터의 수정 혹은 삭제, 변화가 발생하기 전에 각 단위 팀의 별도 APPLICATION을 통해 데이터 BACKUP을 수행하는 것으로 한다.

 

가.    업무수행에 지장을 받지 않는 시간대에 HOT BACKUP을 수행한다.

나.    업무변화가 대량으로 발생하기 전에 APPLICATION을 통한 BACKUP수행

다.    자주 read-write되는 tablespace는 자주 online backup을 수행.

라.    데이터베이스에 구조적인 변화가 생기기 , full backup을 수행.

마.    이전의 backup본을 최소한 2본 이상 가지고 있을 필요가 있다.

바.    특정 테이블들에 대한 data의 입력 오류로 인해 과거 특정 시점으로의 회귀가 필요하거나, 특정 테이블 데이터의 분실로 인해 다시 복귀를 하고자 할 경우를 대비하여 Logical Backup Export를 수시로 받아놓도록 한다.

사.    Unrecoverable Creation Object redo log file logging되지 않기에 이러한 Object들에 대해서는 Export Utility를 사용하여 Backup하도록 하는 것이 좋으며, 초기 생성 후 정상적인 데이터 입력/수정이 이루어질 경우에는 logging으로 변경하도록 한다.

 

1.4 백업 방법

1.4.1 Physical Backup

물리적인 데이터베이스 파일을 한 위치에서 다른 위치로 COPY하는 물리적인 복제를 Physical Backup이라 한다. 또한 Physical Backup Offline, Online Backup(Without Archiving / With Archiving)으로 나눌 수 있다. 즉 데이터베이스 상태가 Down인 상황에서 Backup을 수행하면 Offline Backup이며 이 백업은 Archive Log파일의 Backup은 불필요하나, 데이터베이스가 Online인 상황에서 Backup을 수행하는 Online Backup인 경우에는 Backup도중에도 Transaction이 발생할 수 있고, 이 기간 중에 발생한 데이터의 보존을 위해 Archive Log를 반드시 백업하고 있어야 한다.

 

1.4.1.1 Cold Backup (Offline Backup)

데이터베이스를 Shutdown 한 이후 아래와 같은 파일들을 백업 Library COPY하여야 한다.

 . DataFiles (V$datafile확인자료)

 . Redo Log Files (V$logfile확인자료)

 . Control Files (V$controlfile확인자료)

 . Parameter Files(initSID.ora, spfileSID, configSID.ora, etc)

 

1.4.1.2 HOT Backup(Online Backup)

데이터베이스가 구동중인 상태에서 datafile을 복사하는 방식으로 Archive Log Mode로 운영되어야 한다.

SQL> ALTER TABLESPACE …… BEGIN BACKUP;

$ *.DBF COPY수행

SQL> ALTER TABLESPACE ….. END   BACKUP;

 

     이런 명령을 수행하는 기간 동안에는 해당 TABLESPACE HOTBACKUP MODE로 운영중이어서 해당 TABLESPACE안에 있는 TABLE에 대한 DML이 발생할 경우 DATAFILE WRITE가 불가능하기 때문에 REDO LOG에만 기록하는 기록하게 되고, 백업이 완료된 시점에서 LOG에 저장된 변경사항을 다시 Data file에 기록하기 위해 적지 않은  부하가 발생할 수 있다. 그러므로 ONLINE HOT BACKUP을 수행하는 시간은 작업량이 적고, 사용자의 접근을 최소화 할 수 있는 시간을 선정하여야 하며, 최소한의 시간에 HOT BACKUP을 수행할 수 있어야 한다.

 

또한 BACKUP의 시작과 끝에는 HOT BACKUP의 시작 바로 전까지 발생한 TRANSACTION REDO LOG CHANGE하도록 하여 ARCHIVING하도록 한다.또한 BACKUP이 종료한 후에도 LOG CHANGE를 하도록 하여 BACKUP중에 발생한 DATA에 대한 REDO LOG 내 변경분을 DATAFILE에 기록 및 ARCHIVING을 통한 ARCHIVE FILE BACKUP을 동시에 수행할 수 있도록 하여야 한다.

 

     SQL> ALTER SYSTEM ARCHIVE LOG CURRENTS;

 

 

1.4.2 Logical Backup

    Export Utility를 이용한 데이터 백업은 보통 DML 발생빈도가 높아 데이터블록의 활용도나 Capacity를 높이지 못할 경우 데이터블록을 최적화하기 위해 사용할 수 있고, 사용자의 실수 혹은 구조상의 문제로 인해 데이터의 손실을 최소화하기 위해 데이터의 보존을 목적으로 사용하는 방법이다.

Export Utility를 이용한 데이터 백업방법은 Full, User, Table단위의 Export Mode가 있다.

 

1.4.3 Archive Log File Backup

    1.4.3.1 Archive Log Mode 구조

    오라클에서 Online Backup을 받거나 완벽한 복구작업을 수행하기 위해서는 데이터베이스를 “Archive Log Mode”로 운영하여야 한다.

오라클의 log File기록방법은 순환기록방법을 채택하고 있다. 첫 번째 log File을 기입하고 나면 두 번째 것을 기입하고, 그것이 끝나면 세 번째 log를 기록한다. 그리고 마지막 Online Redo Log File을 쓰고 나면 Log Writer(LGWR)가 첫번째 Log File을 다시 선택하여 덮어쓰기 시작한다.

Oracle Archive Log Mode에서 작동하고 있을 때에는 Archive Background Process(ARCH)는 각각의 Redo Log File을 덮어쓰기 전에 그에 대한 복사본을 지정된 디렉토리에 만들게 된다.

 

 [그림 1 No Archive Log Mode]

CheckPoint가 발생할 때 까지는 Redo Log File은 재사용되지 않으며 ARCH에 의해 물리적으로 Redo Log File은 다시 backup된다.

 

 

             [그림 2 Archive Log Mode]

 

    1.4.3.2  Archive Mode No-Archive Mode의 비교

   위 그림에서 보는 바와 같이 Redo Log가 덮어 쓰이기 시작하고 Archive Mode가 아니면 Media Recovery는 마지막으로 Full Backup받은 시점으로 밖에 복구가 불가능 하다. 반면에 Archive Mode로 운영되는 데이터베이스는 가장 나중의 변화까지도 복구가 가능하다. Archive Log Mode로 운영 시 log_archive_dest Directory밑에 Archive File이 계속 발생하여 할당된 Space가 부족할 경우 log Change가 발생하지 않아 데이터베이스가 Hang-Up이 될 수 있으므로 Space관리를 유의하여야 한다.

 

    1.4.3.3 Archive Log의 백업

 데이터베이스 백업주기 결정시 archive log backup주기도 결정되어야 한다.

Archive log O/S Backup 을 통해 보관하고, Archive Log가 너무 많이 발생하지 않도록 Archive Log Size Redo Log의 사이즈를 적절히 조절하여야 복구를 위한 필요시간을 줄일 수 있다.

 Archive Log는 데이터베이스 백업수행과는 별도로 Space의 여유분을 Check하여 일정수치 이상 Free Space가 부족할 경우 자동적으로 Copy한 다음 삭제하도록 스케쥴링하여야 한다.

 

  1.5 백업 주기

    1.5.1 백업주기의 결정

백업의 주기 및 백업 시기, 시간은 어떠한 백업방법을 적용할 것인가와 어느 정도의 Down Time을 허용할 것인가에 따라 결정된다.

Hot Backup만을 허용하는 사이트에는 Transaction양이 최소화되는 시간을 선택하여 백업을 수행할 것이고, 시스템을 사용할 수 없는 최대한의 시간을 1~3시간으로 선정었다면 복구를 위해 주어진 시간이 1~3시간으로 판단되어 이에 맞는 백업주기가 결정되게 된다.

 

전체 시스템을 모두 Backup하는데 걸리는 시간을 산정하여야 한다. 예를 들어 전체 시스템을 Hot Backup하는데 걸리는 시간이 최대 3시간이 걸린다 할 경우 이를 3일 주기로 전체시스템을 백업할 수 있도록 나눈다면 하루에 백업에 소요되는 시간은 대략 1시간이 될 것이다.

그런데 3일 주기로 백업의 한 사이클이 종료되는 관계로 월요일에 백업한 테이블스페이스에 속한 데이터파일에 문제가 생긴 시기가 수요일 오후라면 약 이틀간 발생한 Archive Log를 이용하여 복구를 하여야 하는데 DataFile, Archive Log Restore 및 복구를 마치는데 주어진 Down Time안에 해결할 수 있는지 판단하여야 한다.

 일반적으로 백업의 주기는 1,1분기,1,1일에 두고 주기 및 방법을 정한다. 또한 백업의 주기 뿐 아니라  백업한 Media의 보관 주기 또한 백업 및 복구에 큰 영향을 미치는 요소이다.

 

    1.5.2 백업 주기 별 대상 결정

백업의 주기(일단위,주단위,월단위,분기단위,년단위,기타)별로 백업 대상을 선정하여 백업 매체를 선정하고, 백업대상을 LIST-UP한 다음 백업하도록 한다.

       1.5.2.1 백업 주기

 

요일

대상

백업대상

A

B

C

D

E

F

ALL

TBS

백업사이즈

354G

296G

338G

354G

296G

338G

998G

 


저작자 표시 비영리
Posted by 티엘로


# dd if=/dev/zero of=/swap1 bs=1024 count=532480
(1024
바이트의 크기로 532480 블럭을 만드는거죠 520M ,
만들고 싶은 크기x1024 해서 count 넣어 주시면 됩니다.)


# mkswqp /swap1 532480 (
스왑 파일 생성)

# chmod 0600 /swap1
# sync; sync (
확실하게 하기위해)
# swapon /swap1 (
스왑 파일 활성화 )

free
명령으로 확인해 보세요.

해제할때는 swapoff /swap1

부팅시 자동으로 스왑 파일을 활성화 시킬려면 

/etc/rc.d/rc.local 파일
끝에 swapon /swap1 이라고 추가하면 됩니다.

저작자 표시 비영리
Posted by 티엘로



웹 개발을 하는데 있어서 정보들을 저장하는 DB schema 또한 개발된다.

문제는 이러한 DB의 버전 관리가 매우 어렵다는 것이다.

레일즈에서는 DB의 버전 관리를 보다 쉽게 할 수 있는 도구(마이그레이션)을 제공한다.

 

마이그레이션은 단지 db/migrate 디렉토리에 있는 루비 소스 파일에 불과하다.

각 마이그레이션의 파일 이름은 세 자리 버전 숫자와 밑줄 문자로 시작한다.

마이그레이션을 생성하는 방법에는 크게 두 가지가 있다.

모델을 생성할 때 마이그레이션이 기본적으로 생성된다.

(물론 --skip-migration 옵션을 통해 생성되지 않게 할 수도 있다)

 ruby script/generate model account

또는 모델과는 상관없이 그냥 마이그레이션을 추가할 수도 있다.

 ruby script/generate migration add_account

사실 db/migrate에 원하는 파일을 직접 만들어서 작성해도 되지만 권장되지 않는다.

 

마이그레이션 파일을 작성하기에 앞서 마이그레이션을 실행하는 방법을 알아보자.

마이그레이션은 rake 프로그램을 통해 실행할 수 있다.

 rake db:migrate

이 명령은 현재 마이그레이션되지 않은 파일이 있으면 그 정보를 DB에 반영한다.

만약 현재 DB 버전이 최신 버전이라면 아무것도 반영되지 않는다.

이어서, DB를 이전 버전으로 돌리고 싶은 경우가 있을 것이다.

이 경우에는 rake task에 VERSION=?? 옵션을 주어 원하는 버전으로 되돌릴 수 있다.

 rake db:migrate VERSION=12

이제 마이그레이션 파일을 작성하는 방법에 대해 알아보도록 한다.

 

제일 처음 마이그레이션 파일을 생성하면 다음과 같은 구조를 볼 수 있다.

(흐린 부분은 마이그레이션 종류에 따라 바뀔 수 있음)

class Account < ActiveRecord::Migration

 def self.up

  # 모델을 생성한 경우엔 다음 코드가 추가되어있음

  create_table :accounts do |t|

  end

 end

 

 def self.down

  # 모델을 생성한 경우엔 다음 코드가 추가되어있음

  drop_table :accounts

 end

end

여기서 self.up은 DB schema 변경을 적용하는 역할을 하고,

self.down은 DB schema를 뒤로 되돌리는 역할을 한다.

 

이제 기본적으로 마이그레이션에 사용되는 코드들을 한번 보도록 한다.

 

table_name이라는 테이블 생성

create_table :table_name do |t| ... end

여기에 :option => "auto_increment = 100"과 같이 여러 SQL 설정을 조작할 수 있다.

또한 :primary_key => :login_id와 같이 기본 키를 직접 지정할 수도 있다.

(이 옵션을 주지 않고 :id => false 옵션을 주면 기본 키가 없는 테이블이 만들어진다)

 

table_name이라는 테이블 제거

drop_table :table_name

 

테이블 table_name1의 이름을 table_name2로 변경

rename_table :table_name1, :table_name2

단, 테이블 이름을 바꾸는 경우 문제가 되는 경우가 많으므로 주의해야 한다.

 

테이블에 컬럼 등록 / 추가 / 제거 / 이름바꾸기

컬럼을 등록할 때는 테이블 생성시에 같이 작성해주어야 한다.

create_table :table_name do |t|

 t.column :column1, :string

 t.column :column2, :integer

end

이후에 컬럼 추가 / 제거 / 이름 바꾸기는 다음과 같이 할 수 있다.

add_column :table_name, :column3, :float

remove_column :table_name, :column2

rename_column :table_name, :column3, :column4


저작자 표시 비영리
Posted by 티엘로


http://www.sqlusa.com/bestpractices2005/centurydateformat/


SQL Server
에서 convert를 이용해 datetime 포맷팅 스타일 값들

내가 원하는 포맷은 
 yyyy/mm/dd hh:mm:ss 인데...
지금은 111, 108 style을 이용해서 얻는데, 한방에 되는 방법은 없는건가?

How to sql format datetime with century?

 

The following conversion options are available for sql datetime format with century:

select convert(char, getdate(), 100) --mon dd yyyy hh:mmAM (or PM) 
select convert(char, getdate(), 101) --mm/dd/yyyy
 
select convert(char, getdate(), 102) --yyyy.mm.dd
 
select convert(char, getdate(), 103) --dd/mm/yyyy
 
select convert(char, getdate(), 104) --dd.mm.yyyy
 
select convert(char, getdate(), 105) --dd-mm-yyyy
 
select convert(char, getdate(), 106) --dd mon yyyy
 
select convert(char, getdate(), 107) --mon dd, yyyy
 
select convert(char, getdate(), 108) --hh:mm:ss
 
select convert(char, getdate(), 109) --mon dd yyyy hh:mm:ss:mmmAM (or PM)
 
select convert(char, getdate(), 110) --mm-dd-yyyy
 
select convert(char, getdate(), 111) --yyyy/mm/dd
 
select convert(char, getdate(), 112) --yyyymmdd
 
select convert(char, getdate(), 113) --dd mon yyyy hh:mm:ss:mmm
 
select convert(char, getdate(), 114) --hh:mm:ss:mmm(24h)
 
select convert(char, getdate(), 120) --yyyy-mm-dd hh:mm:ss(24h)
 
select convert(char, getdate(), 121) --yyyy-mm-dd hh:mm:ss.mmm
 


Without century (yy) (1)

With century (yyyy)

Standard

Input/Output (3)

-

0 or 100 (1, 2)

Default

mon dd yyyy hh:miAM (or PM)

1

101

U.S.

mm/dd/yyyy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106 (1)

-

dd mon yy

7

107 (1)

-

Mon dd, yy

8

108

-

hh:mi:ss

-

9 or 109 (1, 2)

Default + milliseconds

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

110

USA

mm-dd-yy

11

111

JAPAN

yy/mm/dd

12

112

ISO

yymmdd

-

13 or 113 (1, 2)

Europe default + milliseconds

dd mon yyyy hh:mi:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 or 120 (2)

ODBC canonical

yyyy-mm-dd hh:mi:ss(24h)

-

21 or 121 (2)

ODBC canonical (with milliseconds)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126 (4)

ISO8601

yyyy-mm-ddThh:mi:ss.mmm (no spaces)

 

127(6, 7)

ISO8601 with time zone Z.

yyyy-mm-ddThh:mi:ss.mmmZ

(no spaces)

-

130 (1, 2)

Hijri (5)

dd mon yyyy hh:mi:ss:mmmAM

-

131 (2)

Hijri (5)

dd/mm/yy hh:mi:ss:mmmAM

저작자 표시 비영리
Posted by 티엘로
TAG db, SQL, 날짜, 쿼리

from : http://nettop.pe.kr
from :
http://kkaok.pe.kr
from : http://koug.net


<25
가지 SQL작성법>

1.
데이터와 비즈니스 어플리케이션을 잘 알아야 한다.

동일한 정보는 다른 비즈니스 데이터 원천으로부터 검색될 수 있다. 이러한 원천
에 익숙해야 한다. 당신은 당신의 데이터베이스 안의 데이터의 크기와 분포를 반
드시 알아야 한다. 또한 SQL을 작성하기 전에 비즈니스 개체 안의 관계와 같은
데이터 모델을 전체적으로 이해해야 한다. 이러한 이해는 당신이 여러 테이블에
서 정보를 검색하는데 있어서 보다 좋은 쿼리를 작성할 수 있다. DESIGNER/2000
과 같은 CASE TOOLS은 다른 비즈니스와 데이터베이스 객체사이의 관계를 문서화
하는데 좋은 역할을 한다.

2.
실제 데이터를 가지고 당신의 쿼리를 검사하라.

대부분의 조직은 개발, 검사, 제품의 3가지 데이터베이스 환경을 가진다. 프로그
래머는 어플리케이션을 만들고 검사하는데 개발 데이터베이스 환경을 사용하는
, 이 어플리케이션이 제품 환경으로 전환되기 전에 프로그래머와 사용자에 의
해 검사 환경하에서 보다 엄격하게 검토되어야 한다.
SQL
이 검사 환경하에서 테스트될 때, 검사 데이터베이스가 가지고 있는 데이터
는 제품 데이터베이스를 반영해야 한다. 비실제적인 데이터를 가지고 테스트된
SQL
문은 제품 안에서는 다르게 작동할 수 있다. 엄격한 테스트를 보장하기 위해
서는, 검사 환경하에서의 데이터 분포는 반드시 제품 환경에서의 분포와 밀접하
게 닮아야 한다.

3.
동일한 SQL을 사용하라.

가능한한 BIND VARIABLE, STORED PROCEDURE, PACKAGE의 이점을 활용하라. IDENTICAL
SQL
문의 이점은 PARSING이 불필요하기에 데이터베이스 서버안에서 메모리 사용
의 축소와 빠른 수행을 포함한다. 예로서 아래의 SQL 문은 IDENTICAL하지 않다.

SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 20;

그러나 I_EMPID라고 이름 주어진 BIND VARIABLE을 사용하면 SQL 문은 이렇게 된
.
SELECT * FROM EMPLOYEE WHERE EMPID = :I_EMPID;

4.
주의 깊게 인덱스를 사용하라.

테이블상에 모든 필요한 인덱스는 생성되어야 한다. 하지만 너무 많은 인덱스는
성능을 떨어뜨릴 수 있다. 그러면 어떻게 인덱스를 만들 칼럼을 선택해야 하는
?

*
최종 사용자에 의해 사용되는 어플리케이션 SQL과 쿼리의 WHERE 절에서 빈번
하게 사용되는 칼럼에 인덱스를 만들어야 한다.

*SQL
문에서 자주 테이블을 JOIN하는데 사용되는 칼럼은 인덱스되어야 한다.

*
같은 값을 가지는 ROW가 적은 비율을 가지는 칼럼에 인덱스를 사용하라.

*
쿼리의 WHERE 절에서 오직 함수와 OPERATOR로 사용되는 칼럼에는 인덱스를 만들
면 안된다.

*
자주 변경되거나 인덱스를 만들때 얻는 효율성보다 삽입, 갱신, 삭제로 인해 잃는
효율성이 더 큰 칼럼에는 인덱스를 만들면 안된다. 이러한 OPERATION은 인덱스를
유지하기 위한 필요 때문에 느려진다.

*UNIQUE
인덱스는 더 나은 선택성 때문에 NONUNIQUE 인덱스보다 좋다. PRIMARY
KEY
칼럼에 UNIQUE 인덱스를 사용한다. 그리고 FOREIGN KEY 칼럼과 WHERE
에서 자주 사용되는 칼럼에는 NONUNIQUE 인덱스를 사용한다.

5.
가용한 인덱스 PATH를 만들어라

인덱스를 사용하기 위해서는 기술한 SQL문을 이용할 수 있는 식으로 SQL을 작
성하라. OPTIMIZER는 인덱스가 존재하기 때문에 인덱스를 사용하는 ACESS PATH
를 사용할 수 없다. 따라서 ACCESS PATH는 반드시 SQL이 사용할 수 있게 만들
어 져야 한다. SQL HINT를 사용하는 것은 인덱스 사용을 보증해주는 방법중 하
나이다. 특정 ACCESS PATH를 선택하기 위한 다음의 힌트를 참고 하라

6.
가능하면 EXPLAIN TKPROF를 사용하라

만약 SQL문이 잘 다듬어지지 않았다면 비록 오라클 데이터베이스가 잘 짜여져
있어도 효율성이 떨어질 것이다. 이럴 경우 EXPLAIN TKPROF에 능숙해져야 한
. EXPALIN PLAN SQL이 사용하는 ACCESS PATH를 발견할 수 있게 해주고
TKPROF
는 실제 PERFORMANEC의 통계치를 보여준다. TOOL은 오라클 서버 소
프트웨어에 포함되어 있고 SQL의 성능을 향상시켜 준다.

7.OPTIMIZER
를 이해하라.

SQL
RULE-BASED COST-BASED중 하나를 이용해서 기동된다.기존의 소
프트웨어는 RULE BASED 방식을 채택하고 있다. 그리고 많은 오라클 소프트웨
어가 이러한 방식을 오랫동안 사용해 왔다. 그러나 새로 출시된 소프트웨어에 대
해서는 COST BASED 방식의 OPTIMIZER를 고려해야 한다. 오라클은 새로 출
시되는 프로그램을 COST BASED방식으로 업그레이드 시켜왔으며 이러한 방식
은 시스템을 훨씬 더 안정적으로 만들었다. 만약 COST BASED방식의
OPTIMIZER
를 사용한다면 반드시 ANALYZE 스키마를 정기적으로 사용해야 한
. ANALYZE스키마는 데이터베이스 통계를 데이터 사전 테이블에 기록하는 역
할을 수행하며 그렇게 되면 COST BASED OPTIMIZER가 그것을 사용하게 된
. SQL COST BASED OPTIMIZER를 사용할 때만 잘 조정될 수 있다. 만약
RULE BASED
에서 COST BASED로 바꾸고 싶다면 데이터베이스를 사용하는 모
든 소프트웨어의 모든 SQL문의 성능을 평가해 보아야 한다.

8.
지엽적으로 동작하더라도 전역적으로 생각하라

항상 주의할 것은 하나의 SQL문을 조정하기 위해 생긴 데이터베이스안의 변화
는 다른 응용프로그램이나 다른 사용자가 이용하는 다른 명령문에 영향을 미친다
는 사실이다.

9.WHERE
절은 매우 중요하다.

비록 인덱스가 가용하다고 해도 다음의 WHERE 절은 그 인덱스 ACCESS PATH
를 사용하지 않는다.( COL1 COL2는 같은 테이블에 있으며 인덱스는
COL1
에 만들어진다.)

COL1 > COL2
COL1 < COL2
COL1 > = COL2
COL1 <= COL2
COL1 IS NULL
COL1 IS NOT NULL.

인덱스는 NULL값을 갖는 칼럼에는 ROWID를 저장하지 않는다. 따라서 NULL
을 갖는 ROW를 검색할 때는 인덱스를 사용하지 못한다.

COL1 NOT IN (VALUE1, VALUE2 )
COL1 != EXPRESSION
COL1 LIKE ''%PATTERN''.

이럴 경우 THE LEADING EDGE OF THE INDEX(?) 는 작동되지 않고 인덱스가 사
용되지 못하게 한다. 한편 COL1 LIKE ''PATTERN %''이나 COL1 LIKE ''PATTERN %
PATTERN%''
는 한정된 인덱스 스캔을 수행하기 때문에 인덱스를 사용할 수 있다.

NOT EXISTS SUBQUERY
EXPRESSION1 = EXPRESSION2.

인덱스된 컬럼을 포함하는 표현(EXPRESSION), 함수, 계산(CALCULATIONS)은 인덱스
를 사용하지 못한다. 다음의 예에서 보면 UPPER SQL 함수를 사용하면 인덱스
스캔을 사용할 수 없고 FULL TABLE SCAN으로 끝나고 만다.

SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) LIKE ''SALES%'';

10.
레코드 필터링을 위해서는 HAVING보다는 WHERE를 사용하라

인덱스가 걸려있는 칼럼에는 GROUP BY와 같이 HAVING절을 사용하지 마라. 이 경
우 인덱스는 사용되지 않는다. 또한 WHERE절로 된 ROW를 사용하지 마라. 만약
EMP
테이블이 DEPTID컬럼에 인덱스를 가지고 있다면 다음 질의는 HAVING 절을
이용하지 못한다.

SELECT DEPTID,
SUM(SALARY)
FROM EMP
GROUP BY DEPTID
HAVING DEPTID = 100;

그러나 같은 질의가 인덱스를 사용하기 위해 다시 씌여질 수 있다.

SELECT DEPTID,
SUM(SALARY)
FROM EMP
WHERE DEPTID = 100
GROUP BY DEPTID;

11. WHERE
절에 선행 INDEX 칼럼을 명시하라.

복합 인덱스의 경우, 선행 인덱스가 WHERE절에 명시되어 있다면 쿼리는
그 인덱스 를 사용할 것이다. 다음의 질의는 PART_NUM PRODUCT_ID 칼럼
에 있는 PRIMARY KEY CONSTRAINT에 기초한 복합 인덱스를 이용할 것이다.

SELECT *
FROM PARTS
WHERE PART_NUM = 100;

반면, 다음의 쿼리는 복합인덱스를 사용하지 않는다.

SELECT *
FROM PARTS
WHERE PRODUCT_ID = 5555;

같은 요청(REQUEST)이 인덱스를 이용하기 위해 다시 씌어 질 수 있다. 다음 질의
의 경우, PART_NUM컬럼은 항상 0 보다 큰 값을 가질것이다.

SELECT *
FROM PARTS
WHERE PART_NUM > 0
AND PRODUCT_ID = 5555;

12.
인덱스 SCAN FULL TABLE SCAN을 평가하라.

한 행(ROW) 15% 이상을 검색하는 경우에는 FULL TABLE SCAN INDEX
ACESS PATH
보다 빠르다. 이런 경우, SQL FULL TABLE SCAN을 이용할 수 있도록
여러분 스스로 SQL을 작성하라. 다음의 명령문은 비록 인덱스가 SALARY
COLUMN
에 만들어져 있어도 인덱스 SCAN을 사용하지 않을 것이다. 첫 번째 SQL
에서, FULL HINT를 사용한다면 오라클은 FULL TABLE SCAN을 수행할 것이다. 인덱
스의 사용이 나쁜 점이 더 많다면 아래의 기술을 이용해서 인덱스 수행을 막을
수 있다.

SELECT * --+FULL
FROM EMP
WHERE SALARY = 50000;

SELECT *
FROM EMP
WHERE SALARY+0 = 50000;

다음의 명령문은 비록 인덱스가 SS# COLUMN에 있어도 인덱스 SCAN을 사용하
지 않을 것이다.

SELECT *
FROM EMP
WHERE SS# || '' '' = ''111-22-333'';

오라클이 불분명한 데이터 변환을 수행해야 하는 경우 인덱스가 항상 사용되지
않는 것은 아니다. 다음의 예를 보면, EMP 칼럼에 있는 SALARY는 숫자형 칼
럼이고 문자형이 숫자값으로 변환된다.

SELECT *
FROM EMP
WHERE SALARY = ''50000'';

테이블의 행이 15%이거나 그보다 작을 경우 인덱스 스캔은 보다 잘 수행 될 것
이다. 왜냐 하면 인덱스 스캔은 검색된 행(ROW)하나 하나 마다 다중의 논리적인
읽기 검색(READ)을 할 것이기 때문이다. 그러나 FULL TABLE SCAN은 하나의 논리적
인 읽기 검색 영역 안의 BLOCK에 있는 모든 행들을 읽을 수 있다. 그래서 테이
블의 많은 행들에 접근해야 하는 경우에는 FULL TABLE SCAN이 낫다. 예로 다음의
경우를 보자. 만약 EMP TABLE과 그 테이블의 모든 인덱스에 대해 ANALYZE
는 명령어가 수행된다면, 오라클은 데이터 사전인 USER_TABLES
USER_INDEXES
에 다음과 같은 통계치를 산출해 낸다.

TABLE STATISTICS:
NUM_ROWS = 1000
BLOCKS = 100

INDEX STATISTICS:

BLEVEL = 2
AVG_LEAF_BLOCKS_PER_KEY = 1
AVG_DATA_BLOCKS_PER_KEY = 1

이러한 통계치 에 근거해서, 아래에 보이는 것이 각각의 다른 SCAN에 대한 논리
적인 읽기(READ)- ACESS BLOCK이 될 것이다.

USE OF INDEX TO RETURN ONE ROW = 3

(BLEVEL+(AVG_LEAF_BLOCKS_PER_KEY - 1) +
AVG_DATA_PER_KEY

FULL TABLE SCAN = 100
(BLOCKS)

USE OF INDEX TO RETURN ALL ROWS = 3000
(NUM_ROWS * BLOCKS ACCESSED TO RETURN ONE ROW USING INDEX)

13.
인덱스 스캔에 ORDER BY를 사용하라

오라클의 OPTIMIZER , 만약 ORDER BY라는 절이 인덱스된 칼럼에 있다면 인
덱스 스캔을 사용할 것이다. 아래의 질의는 이러한 점을 보여 주는 것인데 이 질
의는 비록 그 칼럼이 WHERE 절에 명시되어 있지 않다고 해도 EMPID컬럼에 있
는 가용한 인덱스를 사용할 것이다. 이 질의는 인덱스로부터 각각의 ROWID
검색하고 그 ROWID를 사용하는 테이블에 접근한다.

SELECT SALARY
FROM EMP
ORDER BY EMPID;

만약 이 질의가 제대로 작동하지 않는다면, 당신은 위에서 명시되었던 FULL HINT
를 사용하는 같은 질의를 다시 작성함으로써 다른 대안들을 이용해 볼 수 있다.

14.
자신의 데이터를 알아라

내가 이미 설명한 것처럼, 당신은 당신의 데이터를 상세하게 알고 있어야 한다.
예를 들어 당신이 BOXER라는 테이블을 가지고 있고 그 테이블이 유일하지 않은
인덱스를 가진 SEX라는 컬럼과 BOXER_NAME이라는 두 개의 테이블을 가지고 있
다고 가정해 보자. 만약 그 테이블에 같은 수의 남자, 여자 복서가 있다면 오라
클이 FULL TABLE SCAN을 수행하는 경우 다음의 질의가 훨씬 빠를 것이다.

SELECT BOXER_NAME
FROM BOXER
WHERE SEX = ''F'';

당신은 다음과 같이 기술함으로써 질의가 FULL TABLE SCAN을 수행하는지를 확실
하게 해 둘 수 있다.

SELECT BOXER_NAME --+ FULL
FROM BOXER
WHERE SEX = ''F'';

만약 테이블에 980 명의 남성 복서 데이터가 있다면, 질의는 인덱스 SCAN으로
끝나기 때문에 아래형식의 질의가 더 빠를 것이다.

SELECT BOXER_NAME --+ INDEX (BOXER BOXER_SEX)
FROM BOXER
WHERE SEX = ''F'';

이 예는 데이터의 분포에 대해 잘 알고 있는 것이 얼마나 중요한 가를 예시해 준
. 데이터가 많아지고(GROW) 데이터 분포가 변화하는 것처럼 SQL 도 매우 다
양할 것이다. 오라클은 OPTIMIZER 가 테이블에 있는 데이터의 분포를 잘 인식하
고 적절한 실행 계획을 선택하도록 하기 위해 오라클 7.3 HISTOGRAMS라는
기능을 추가했다.

15. KNOW WHEN TO USE LARGE-TABLE SCANS.

작거나 큰 테이블에서 행들을 추출할 때, 전체 테이블의 검색은 인텍스를 사용한
검색보다 성능이 더 좋을 수도 있다. 매우 큰 테이블의 인덱스 검색은 수많은
인덱스와 테이블 블록의 검색이 필요할수도 있다. 이러한 블록들이 데이터베이
스 버퍼 캐쉬에 이동되면 가능한한 오래도록 그곳에 머무른다. 그래서 이러한
블록들이 다른 질의등에 필요하지 않을 수도 있기 때문에, 데이터베이스 버퍼 히
트 비율이 감소하며 다중 사용자 시스템의 성능도 저하되기도 한다. 그러나 전
체 테이블 검색에 의해서 읽혀진 블록들은 데이터베이스 버퍼 캐쉬에서 일찍 제
거가 되므로 데이터베이스 버퍼 캐쉬 히트 비율은 영향을 받지 않게 된다.

16. MINIMIZE TABLE PASSES.

보통, SQL질의시 참조하는 테이블의 숫자를 줄임으로 성능을 향상시킨다. 참조
되는 테이블의 숫자가 적을수록 질의는 빨라진다. 예를 들면 NAME, STATUS,
PARENT_INCOME, SELF_INCOME
의 네개의 컬럼으로 이루어진 학생 테이블
에서 부모님에 의존하는 학생과 독립한 학생의 이름과 수입에 대해서 질의시,
학생 테이블을 두번 참조하여 질의하게 된다..
SELECT NAME, PARENT_INCOME
FROM STUDENT
WHERE STATUS = 1
UNION
SELECT NAME, SELF_INCOME
FROM STUDENT
WHERE STATUS = 0;
( NAME
이 프라이머리 키이며, STATUS는 독립한 학생의 경우는 1, 부모님에
의존적인 학생은 0으로 표시한다)
위의 같은 결과를 테이블을 두번 참조하지 않고도 질의 할 수 있다.

SELECT NAME,PARENT_INCOME*STATUS + SELF_INCOME(1-STATUS)
FROM STUDENT;

17. JOIN TABLES IN THE PROPER ORDER.

다수의 테이블 조인시 테이블들의 조인되는 순서는 매우 중요하다. 전반적으로,
올바른 순서로 테이블이 조인되었다면 적은 수의 행들이 질의시 참조된다. 언제
나 다수의 조인된 테이블들을 질의시 우선 엄격하게 조사하여 행들의 숫자를 최
대한으로 줄인다. 이러한 방법으로 옵티마이저는 조인의 차후 단계에서 적은 행
들을 조사하게 된다. 뿐만 아니라, 여러 조인을 포함하는 LOOP JOIN에서는 가
장 먼저 참조되는 테이블(DRIVING TABLE)이 행들을 최소한으로 리턴하도록 해야
한다. 그리고, 마스터와 상세 테이블 조인시에는(예를 들면 ORDER & ORDER
LINE ITEM TABLES)
마스터 테이블을 먼저 연결 시켜야 한다.
규칙에 근거한 옵티마이저의 경우에는 FROM CLAUSE의 마지막 테이블이 NESTED
LOOP JOIN
DRIVING TABLE이 된다. NESTED LOOP JOIN이 필요한 경우에는
LOOP
의 안쪽의 테이블에는 인텍스를 이용하는 것을 고려할 만하다. EXPLAIN
PLAN
TKPROF는 조인 타입, 조인 테이블 순서, 조인의 단계별 처리된 행들
의 숫자들을 나타낸다.
비용에 근거한 옵티마이저의 경우에는 WHERE CLAUSE에 보여지는 테이블의 순
서는 옵티마이저가 가장 최적의 실행 계획을 찾으려고 하는 것과 상관 없다.
인되는 테이블의 순서를 통제하기 위해서 ORDERED HINT를 사용하는 것이 낫다.

SELECT ORDERS.CUSTID, ORDERS.ORDERNO,
ORDER_LINE_ITEMS.PRODUCTNO --+ORDERED
FROM ORDERS, ORDER_LINE_ITEMS
WHERE ORDERS.ORDERNO = ORDER_LINE_ITEMS.ORDERNO;

18. USE INDEX-ONLY SEARCHES WHEN POSSIBLE.

가능하다면, 인덱스만을 이용하여 질의를 사용하라. 옵티마이저는 오직 인덱스만
을 찾을 것이다. 옵티마이저는 SQL을 만족시키는 모든 정보를 인덱스에서 찾을
수 있을 때, 인덱스만을 이용할 것이다. 예를들면, EMP테이블이 LANME
FNAME
의 열에 복합 인덱스를 가지고 있다면 다음의 질의는 인덱스만은 이용할
것이다.

SELECT FNAME
FROM EMP
WHERE LNAME = ''SMITH'';

반면에 다음의 질의는 인덱스와 테이블을 모두 참조한다.

SELECT FNAME , SALARY
FROM EMP
WHERE LNAME = ''SMITH'';

19. REDUNDANCY IS GOOD.

WHERE CLAUSE
에 가능한한 많은 정보를 제공하라. 예를 들면 WHERE COL1 =
COL2 AND COL1 = 10
이라면 옵티마이저는 COL2=10이라고 추론하지만,
WHERE COL1 = COL2 AND COL2 = COL3
이면 COL1=COL3이라고 초론하지는
않는다.

20. KEEP IT SIMPLE, STUPID.

가능하면 SQL문을 간단하게 만들라. 매우 복잡한 SQL문은 옵티마이저를 무력
화시킬 수도 있다. 때로는 다수의 간단한 SQL문이 단일의 복잡한 SQL문보다
성능이 좋을 수도 있다. 오라클의 비용에 근거한 옵티마이저는 아직은 완벽하지
않다. 그래서 EXPLAIN PLAN에 주의를 기울여야 한다. 여기서 비용이란 상대적인
개념이기에 정확히 그것이 무엇을 의미하는지 알지 목한다. 하지만 분명한 것은
적은 비용이 보다 좋은 성능을 의미한다는 것이다.
종종 임시 테이블을 사용하여 많은 테이블들을 포함하는 복잡한 SQL 조인을 쪼
개는 것이 효율적일 수도 있다. 예를 들면, 조인이 대량의 데이터가 있는 8개의
테이블을 포함할 때, 복잡한 SQL을 두 세개의 SQL로 쪼개는 것이 낫을 수 있
. 각각의 질의는 많아야 네개정도의 테이블들을 포함하며 그 중간 값을 저장
하는 것이 낫을 수 있다.

21. YOU CAN REACH THE SAME DESTINATION IN DIFFERENT WAYS.

많은 경우에, 하나 이상의 SQL문은 의도한 같은 결과를 줄 수 있다. 각각의
SQL
은 다른 접근 경로를 사용하며 다르게 수행한다. 예를들면, MINUS(-) 산술
자는 WHERE NOT IN (SELECT ) OR WHERE NOT EXISTS 보다 더 빠르다.
예를들면, STATE AREA_CODE에 각각 다른 인덱스가 걸려 있다. 인덱스에
도 불구하고 다음의 질의는 NOT IN의 사용으로 인해 테이블 전체를 조사하게
된다.
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (''VA'', ''DC'', ''MD'')
AND AREA_CODE NOT IN (804, 410);

그러나 같은 질의가 다음 처럼 쓰여진다면 인덱스를 사용하게 된다
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (''VA'', ''DC'', ''MD'')
MINUS
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE AREA_CODE IN (804, 410);

WHERE
절에 OR을 포함한다면 OR대신에 UNION을 사용할 수 있다. 그래서,
SQL
질의를 수행하기 전에 먼저 실행계획을 조심스럽게 평가해야 한다. 이러한
평가는 EXPLAIN PLAN AND TKPROF를 이용하여 할 수 있다.

22. USE THE SPECIAL COLUMNS.

ROWID AND ROWNUM
열을 이용하라. ROWID를 이용하는 것이 가장 빠르다.
예를들면, ROWID를 이용한 UPDATE는 다음과 같다.

SELECT ROWID, SALARY
INTO TEMP_ROWID, TEMP_SALARY
FROM EMPLOYEE;

UPDATE EMPLOYEE
SET SALARY = TEMP_SALARY * 1.5
WHERE ROWID = TEMP_ROWID;

ROWID
값은 데이터베이스에서 언제나 같지는 않다. 그래서, SQL이나 응용 프
로그램이용시 ROWID값을 절대화 시키지 말라. 리턴되는 행들의 숫자를 제한
시키기위해 ROWNUM을 이용하라. 만약에 리턴되는 행들을 정확히 모른다면
리턴되는 행들의 숫자를 제한하기위해 ROWNUM을 사용하라
다음의 질의는 100개 이상의 행들을 리턴하지는 않는다.
SELECT EMPLOYE.SS#, DEPARTMENT.DEPT_NAME
FROM EMPLOYEE, DEPENDENT
WHERE EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID
AND ROWNUM < 100;

23.
함축적인 커서대신 명시적인 커서를 사용하라.

함축적 커서는 여분의 FETCH를 발생시킨다. 명시적 커서는 DECLARE, OPEN,
FETCH
CLOSE CURSOR문을 사용하여 개발자에 의해서 생성된다. 함축 커서는
DELETE, UPDATE, INSERT
SELECT문을 사용하면 오라클에 의해서 생성
된다.

24.
오라클 병렬 쿼리 옵션을 찾아서 이용하라.

병렬 쿼리 옵션을 사용하면, 보다 빠른 성능으로 SQL을 병렬로 실행할 수 있다.
오라클 7에서는, 오직 FULL TABLE SCAN에 기반한 쿼리만이 병렬로 수행될 수 있다.
오라클 8에서는, 인덱스가 분할되어있다면 INDEXED RANGE SCANS에 기반한 쿼리도
병렬로 처리될 수 있다. 병렬 쿼리 옵션은 다수의 디스크 드라이버를 포함하는
SMP
MPP SYSTEM에서만 사용될 수 있다.

오라클 서버는 많은 우수한 특성을 가지고 있지만, 이러한 특성의 존재만으로는
빠른 성능을 보장하지 않는다. 이러한 특성을 위해서 데이터베이스를 조정해야하
며 특성을 이용하기 위해 특별하게 SQL을 작성해야 한다. 예를 들면, 다음의
SQL
은 병렬로 수행될 수 있다.

SELECT * --+PARALLEL(ORDERS,6)
FROM ORDERS;

25.
네트웍 소통량을 줄이고 한번에 처리되는 작업량을 늘려라.

ARRAY PROCESSING
PL/SQL BLOCK을 사용하면 보다 나은 성능을 얻을 수 있고
네트웍 소통량을 줄인다. ARRAY PROCESSING은 하나의 SQL문으로 많은 ROW를 처
리할 수 있게 한다. 예를 들면, INSERT문에서 배열을 사용하면 테이블내의
1,000 ROW
를 삽입할 수 있다. 이러한 기술을 사용하면 주요한 성능 향상을 클라
이언트/서버와 배치시스템에서 얻어질 수 있다.

복합 SQL문은 과도한 네트웍 소통을 유발할 수 있다. 그러나 만일 SQL문이 단
PL/SQL 블록안에 있다면, 전체 블록은 오라클 서버에 보내져서 그곳에서 수
행되고, 결과는 클라이언트의 APPLICATION에게 돌아온다.

개발자와 사용자는 종종 SQL을 데이터베이스에서 데이터를 검색하고 전송하는
간단한 방법으로 사용한다. 때때로 직접적으로 SQL을 작성하지 않고 코드 발생
기를 사용하여 작성한 APPLICATION은 심각한 성능 문제를 일으킨다. 이러한 성능
감퇴는 데이터베이스가 커지면서 증가한다.

SQL
은 유연하기 때문에, 다양한 SQL문으로 같은 결과를 얻을 수 있다. 그러나
어떤 문은 다른 것보다 더 효율적이다. 여기에 기술된 팁과 기법을 사용하면 빠
르게 사용자에게 정보를 제공할 수 있는 APPLICATION과 리포트를 얻을 수 있다.

 

저작자 표시 비영리
Posted by 티엘로

MYSQL 주요함수

Blah Blah 2008/10/31 18:33

MYSQL 주요함수
여기서는 mysql에 사용되는 중요한 몇 가지 기본 함수들을 소개하겠습니다.    대부분의 함수가 php와 연관하여 비슷한 것들이 많이 있습니다.    php에서 만약 이 함수를 사용하려면 mysql query 문을 이용하는 방법도 있습니다.    (여기서의 설명은 직접 local에서 접속해서 하는 것보다 클라이언트에서 telnet으로 접속해서 사용한 예를 더 많이 들었습니다.)


연산자 
연산자의 경우 일반적으로 사용하는 연산자들을 사용합니다.    "+" , "-" , "*" , "/" 등 입니다.    그럼 각각의 수행 결과를 확인 하겠습니다. 

비교 연산자 

비교 연산자는 결과가 참(1) 과 거짓(0)으로 결과를 출력합니다.

그리고 비교 연산자의 경우 몇 가지 규칙이 있습니다.

   ~
인수가 모두 문자열이면 문자열로 비교됩니다.
   ~
인수가 모두 정수면 정수로 비교됩니다.
   ~
인수가 모두 "null" 이면 연산의 결과도 "null"이 됩니다.
   ~
한쪽의 인수가 "timestamp" 이면 나머지도 같이 변환되어 비교됩니다.
   ~
한쪽의 인수가 "datetime" 이면 나머지도 같이 변환되어 비교됩니다.
   ~
나머지의 경우는 부동소수점 실수로 비교됩니다.

기호로는
 

   "=" -
같다 
   "!= ,<>" -
같지 않다  
   "<=" -
작거나 같다
 
   "<" -
작다  
   ">=" -
크거나 같다
 
   ">" -
크다
   "<=>" -
둘 중 한쪽이 "null" 일 경우 0을 출력

   
인자 between (최소값) and (최대값) - 인자가 최소값과 최대값 사이에 존재 하면 "1"을 그렇지
   
않을 경우 "0"을 출력합니다.
    
인자 in (, , ) - 인자가 ( ) 안에 존재하면 "1"을 그렇지 않으면 "0"을 출력합니다.

 날짜와 시간 관련 함수 
날짜와 시간 관련 함수는 여러모로 사용하는 곳이 많은 부분입니다.    대부분의 표현에서 날짜와 시간은 꼭 들어가기 때문에 이 부분을 먼저 다루도록 하겠습니다. 

MONTHNAME("
날짜") - 해당하는 날짜의 월을 영어로 리턴합니다.
QUARTER("
날짜") - 해당하는 날짜의 분기를 리턴해 줍니다.
YEAR("
날짜") - 해당 날짜의 년도를 리턴해 줍니다.
HOUR("
시간") - 해당하는 시간을 리턴합니다.
MINUTE("
시간") - 해당 시간의 분을 리턴합니다.
SECOND("
시간") - 해당 시간의 초를 리턴합니다.
PERIOD_ADD(
날짜, N) - 해당하는 날짜에의 개월에 "N"개월을 더 합니다.
            (
날짜는 YYMM, YYYYMM형식으로 주어지면 YYYYMM 형식으로 리턴합니다.)
CURDARE( ) -
오늘 날짜를 YYYY-MM-DD 또는 YYYYMMDD 형식으로 리턴해 줍니다.
           
함수가 문자열 또는 숫자로 사용됨에 따라 리턴 값은 달라 집니다.
CURTIME( ) -
현재 시간을 HH:MM:SS 또는 HHMMSS 형식으로 리턴해 줍니다.
           
이 함수 역시 함수가 문자열 또는 숫자로 사용됨에 따라 리턴 값이 달라 집니다.
NOW( ) -
현재의 날짜와 시간을 리턴합니다.
UNIX_TIMESTAMP( ) -
유닉스 타임스탬프를 리턴합니다.
                   
날짜 인자가 있을 경우 해당 날짜의 유닉스 타임스탬프를 리턴하고, 인자가 없을
                   
현재의 유닉스 타임스탬프를 리턴합니다.(초 단위로 나타냅니다.)
FROM_UNIXTIME (
유닉스 타임스탬프) - 유닉스 타임스탬프 날짜에서 일반 형식의 날짜와 시간으로 리턴합니다이 함수를 이용해서 원하는 데이터 형태로도 출력이 가능합니다. (DATE_FORMAT( ) 함수는 날짜와 시간을 여러 가지 형태로 표현 가능하게합니다. 다음 함수를 참고 하세요.)
DATE_FORMAT(
날짜, 형태) - 형태의 종류에 맞게 여러 가지 양식으로 날짜와 시간을 리턴해 줍니다.
                         
특히 이 함수는 자주 사용 되므로 관심있게 보기 바랍니다.
형태의 종류는….
   %M -
월 이름을 영어로 리턴합니다.(January)
   %D -
접미사를 사용해 영어로 일을 리턴합니다.(1st , 2nd ..)
 
   %W -
요일을 영어로 리턴합니다. (Monday)
   %y - 2
자리 연도를 리턴합니다.
   %m -
월을 숫자로 리턴합니다.(01 , 02 , 03)
   %d -
일을 숫자로 리턴합니다. (00 , 01 ,02 )
   %a -
요일을 짧은 영어로 리턴합니다. (Mon)
   %e -
일을 숫자로 리턴합니다.(0 , 1 , 2)
   %c -
월을 숫자로 리턴합니다. (1 , 2 , 3)
   %j -
한해의 몇 번째 요일인지 리턴합니다. (001 ~ 366)
   %b -
월을 짧은 영어로 리턴합니다. (Jan)
   %H - 24
시간 형식의 시간을 리턴합니다. (00 ~ 23 )
   %h - 12
시간 형식의 시간을 리턴합니다. (01 ~ 12)
   %k - 24
시간 형식의 시간을 리턴합니다. (1 ~ 23)
   %l -
시간을 리턴합니다. (1 ~ 12)
   %i -
분을 리턴합니다. (00 ~ 59)
   %T -
시분초의 24시간 형식을 리턴합니다. (hh:mm:ss)
   %r -
시분초의 12시간 형식을 리턴합니다. (hh:mm:ss)
   %s -
초를 리턴합니다. (00 ~ 59)
   %p - AM , PM
을 리턴합니다.
   %w -
일주일 중 몇 번째 요일인지 리턴합니다.(0 - 일요일)
   %U -
한해 중 몇 번째 주인지 리턴합니다.(일요일이 시작)
   %u -
한해 중 몇 번째 주인지 리턴합니다.(월요일이 시작)

   second -
초를 추가 합니다(interval 1 second)
   minute -
분을 추가 합니다.(interval 1 minute)
   hour -
시간을 추가 합니다.(interval 1 hour)
   day -
일을 추가 합니다.(interval 1 day)
   month -
달을 추가 합니다. .(interval 1 month)
   year -
년을 추가 합니다. .(interval 1 year)
   minute_second -
분과 초를 추가 합니다. (interval "1:1" minute_second)
   hour_minute -
시간과 분을 추가 합니다. (interval "1:1" hour_minute)
   day_hour -
일과 시간을 추가 합니다. (interval "1 1" day_hour)
   year_month -
년과 월을 추가 합니다. (interval "1-1" year_month)
   hour_second -
시간과 분, 초를 추가 합니다. (interval "1:1:1" hour_second)
   day_minute -
일과 시간, 분을 추가 합니다.(interval "1 1:1" day_minute)
   day_second -
일과 시간, , 초를 추가 합니다.(interval "1 1:1:1" day_second)

만약 날짜와 시간을 빼기를 원한다면 " - "를 사용하면 됩니다. 또 다른 방법은 DATE_SUB( ) 함수를
사용하는 것입니다.

PERIOD_DIFF(
날짜1, 날짜2) - 날짜1 과 날짜2 사이의 개월 수를 리턴합니다.


문자열 관련 함수
 


php
스크립트 프로그래밍을 하면서 php에서 지원하는 함수를 이용한 방법으로 많은 문자열 처리하게 될 것입니다.    Mysql 역시 문자열을 처리하는 여러 가지 함수가 있습니다.    여러분들은 데이터를 데이터베이스에 저장할 때 php 함수에서 지원하는 함수를 사용할 수도 있지만 여기서 설명할 mysql 함수를 사용해도 됩니다.    (문자열 함수는 결과 값이 정의된 길이보다 클 경우 NULL을 리턴합니다.) 

HEX(n) -
해당 10진수를 16진수로 리턴해 줍니다. (NULL NULL로 리턴)
OCT(n) -
해당 10진수를 8진수로 리턴해 줍니다.(NULL -> NULL로 리턴)
BIN(n) -
해당 10진수를 2진수로 리턴합니다. (NULL -> NULL로 리턴)
CONV(n , a , b) -
해당 숫자를 a 형식의 진수에서 b 형식의 진수로 변환해서 리턴합니다.
 
                 
앞에서 설명한 각 진수별 변환 방법의 기능을 다 가지고 있는 함수입니다.
                (
인자중 NULL이 있으면 NULL을 리턴합니다. 2 ~36진까지 가능)
ASCII(
문자열) - 해당 문자열의 처음 위치의 ASCII 코드를 리턴합니다.(NULL -> NULL로 리턴 합니다.)
FIELD(Nstring , string ,
) - Nstring에 해당하는 문자열이 몇 번째 인지 리턴합니다.
LOWER(
문자열) - 해당 문자열을 소문자로 변환해서 리턴합니다.(컴파일 시 선택한 문자 설정)
UPPER(
문자열) - 해당 문자열을 대문자로 변환해서 리턴합니다.
              (
컴파일 시 선택한 문자 설정)
LOAD_FILE(
파일명) - 64kb 보다 작은 내용의 파일을 읽어 들여 문자열로 리턴합니다.
                  (64kb
보다 클 경우 NULL을 리턴합니다.)
SPACE(n) -
해당 인자의 수 만큼 공백을 리턴합니다.
REVERSE(
문자열) - 해당 문자열의 순서를 바꾸어 리턴합니다.
INSERT(
문자열, a , b , 문자열1) - 해당 문자열을 a 위치부터 b 크기 만큼 문자열1을 넣어 리턴합니다.
SUBSTRING_INDEX(
문자열, a , count ) - 해당 문자열을  a로 구분해서 배열로 만들고 count 수만큼 리턴해줍니다.  오른쪽부터 출력하길 원하면 음수를 적으면 됩니다.

REPEAT(
문자열 , c ) - 해당 문자열을  c 만큼 반복해서 리턴합니다.
LTRIM(
문자열) - 해당 문자열의 왼쪽 공백을 제거합니다.
RTRIM(
문자열) - 해당 문자열의 오른쪽 공백을 제거하고 리턴합니다.
TRIM(
옵션 a FROM 문자열) - 주어진 옵션에 따라 a 문자를 제거하고 리턴합니다.
옵션에는 LEADING | TRAILING | BOTH 등이 있으며 각각 앞쪽 공백제거 | 뒤쪽 공백 제거 | 앞뒤 공백 제거입니다.
 
CONCAT(
문자열, 문자열 …..) - 해당 문자열을 이어 줍니다.
                             
이 함수는 php " . "을 이용한 문자열 연결 방법과 유사 합니다.
                            (NULL -> NULL
로 리턴합니다.)
LEFT(
문자열,n) - n 만큼 해당 문자열을 왼쪽부터 리턴합니다.
 
RIGHT(
문자열, n) - n만큼 해당 문자열을 오른쪽부터 리턴합니다.
LOCATE(a , b) - a(
문자열) b(문자열)에서 처음부터 몇 번째 위치인지 리턴합니다.
LPAD(
문자열 , a , b) - 해당 문자열에 a 길이 만큼 b 문자를 왼쪽부터 넣어 리턴합니다.
RPAD(
문자열, a , b) - 해당 문자열에 a 길이만큼 b 문자를 오른쪽에 넣어 리턴합니다.

여기까지 문자열 함수의 대부분을 설명했습니다.몇 가지 빠진 함수들도 있지만 여기 있는 것만으로도 대부분을 표현하기는 충분합니다.

기타 함수들
 


mysql
의 수학 함수는 여기서 다루지 않도록 하겠습니다.    대부분의 경우 php 함수가 대신 할 수 있기 때문에 구지 mysql 함수를 이용해 수학연산을 할 필요는 없습니다.    하지만 어느 경우든 꼭 사용해야만 할 경우는 mysql 매뉴얼을 보시기 바랍니다.    짧은 영어 실력으로도 충분히 보실 수 있는 매뉴얼이기 때문에 걱정은 필요 없습니다.    그럼, 여기서는 자주 사용되는 나머지 함수들을 설명하겠습니다. 


USER() -
현재 mysql에 접속 중인 사용자 이름을 리턴합니다.
VERSION( ) - mysql
의 버전을 리턴해 줍니다.
PASSWORD(
문자열) - 이 함수는 mysql의 데이터 베이스에 문자열을 암호화 해서 저장해 줍니다. 대부분의 경우 사용자 인증에 이용합니다.  

, 암호화된 문자열끼리 비교함으로 해서 인증을 하는 것입니다. 뒤에 설명할 admin tool의 사용자 인증 역시 이 방법을 이용했습니다.

이렇게 중요하게 사용되는 몇 가지 함수들을 봤습니다.
대부분의 프로그램과 스크립트들은 거의 비슷한 양식의 함수를 가지고 있습니다.
 
그래서 다른 곳에서 비슷한 함수를 보면 그 기능이 무엇인지는 어느정도 예상할 수 있습니다.
꼭 필요한 것을 확실히 이해하는 것도 중요합니다.

 

저작자 표시 비영리
Posted by 티엘로


/etc/crontab -e

0  22 *  *  * /etc/db_backup/babonara/babonara_backup.sh >> /etc/db_backup/babonara/cron_log
[분][시][일][월][년] 실행명령

위 처럼 설정하면 매일 밤 10 시에 /etc/db_backup/babonara/babonara_backup.sh >> /etc/db_backup/babonara/cron_log 라는 작업을 하게 된다.

/etc/db_backup/babonara/babonara_backup.sh
#!/bin/sh
## babonara DataBase Daily Backup

cd /etc/db_backup/babonara

dir=`date +%y%m%d`
mkdir $dir
/usr/local/mysql/bin/mysqldump -u bak babonara > ./$dir/**.txt

이렇게 하면 그날의 날짜로 되어있는 디렉토리에 **.txt라는 이름으로 DB가 백업된다.

폴더에 담지 않고 파일명으로 구분할려면

dir=`date +%y%m%d`
mkdir $dir
/usr/local/mysql/bin/mysqldump -u bak babonara > ./$dir/babonaraBackup.txt

이부분을

fileName=`date +%y%m%d`
/usr/local/mysql/bin/mysqldump -u bak babonara > ./$fileName

Posted by 티엘로

 

내부자 기업정보 유출 사고, DB보안으로 해결하라
DB암호화·접근제어로 정보 유출 방지 … 속도 저하·표준 미비·경쟁 심화 등 걸림돌


제 1부 DB보안 주요 솔루션 현황 및 시장 동향
제 2부 DB보안 기술 동향


최근 빈번하게 발생하고 있는 내부자에 의한 기업정보 유출 사고로 기업과 개인이 막대한 피해를 입는 사례가 속출하며 비즈니스 핵심 인프라인 데이터베이스(Database) 보안에 대한 관심이 급속히 증가하고 있다.
DB보안 솔루션은 중요 정보를 데이터베이스에 보관할 시 해당 특정필드에 암호화를 적용하거나 사용자의 접근을 제어해 해킹 및 허가받지 않는 내부자에 의한 불법적인 정보유출을 방지할 수 있게 해준다. 최근 금융권 등에서는 내부자에 의한 고객 명단, 패스워드 등의 유출로 인한 금융사고를 방지하기 위해 DB보안 솔루션 도입을 검토중이라 관련 시장의 전망도 밝은 편이다.

특히 개인정보보호법의 발효를 앞두고 DB보안 솔루션은 기업의 선택 사항이 아닌 기업의 존폐를 결정지을 필수 사항으로 떠오르게 될 전망이다. 하지만 DB보안 솔루션은 일정한 형태의 표준이 없이 다양한 형태의 제품이 난립, 고객들이 어떤 제품을 선택해야하는지 정확한 기준이 모호하다. 또한 속도저하, 기존 시스템과의 호환성 등 아직 해결해야할 문제가 산적해 있다.
DB보안을 위한 제품들은 어떤 것들이 있으며 어떻게 활용하는 것이 좋을지 관련 업체들의 제품과 사업현황을 통해 살펴본다. <편집자>


제 1부 DB보안 주요 제품 현황 및 시장 동향
시장 선점위한 DB보안, 제품 출시 봇물

개인정보보호법 시행으로 도입 열기 ‘후끈’…
올해 약 150억원 시장 형성 기대



허가받지 않은 사용자의 DB 접근을 제한하고 내부자에 의해 DB가 유출됐다 하더라도 DB를 활용하지 못하도록 접근제어, 암호화, 감사를 수행하는 DB보안 솔루션이 최근 개인정보보호법의 발효와 맞물려 주목을 끌고 있다. 특히 최근 빈번하게 발생하고 있는 고객정보유출 사고로 인해 전자상거래 포털사이트, 공공기관 등이 DB보안 솔루션 도입을 속속 추진하고 있다.
이에 따라 데이터 베이스의 속도저하를 우려해 DB의 접근제어, 감사 등을 수행하는 제품과 일부를 암호화하는 소극적인 DB 보안 솔루션부터 DB 자체를 통채로 암호화하는 솔루션, 네트워크와 연동한 하드웨어 일체형, 개인 PC용 DB보안까지 다양한 DB보안 제품들의 출시가 봇물을 이루고 있다. 그러나 기업의 중요 자산인 민감한 DB를 다루고 있기 때문에 DB 보안 솔루션에 대한 고객들의 접근은 조심스러운 편이다. 필요성을 인지하고 있지만 어떤 제품을 선택해야할지, 자사에 맞는 솔루션은 무엇인지 선택하기가 쉽지 않다는 것.
이에 DB보안 솔루션을 어떻게 활용해야 좋을지, 각 제품들은 어떤 특징을 갖고 있으며 내게 맞는 솔루션은 무엇인지, 관련 업체들의 시장 동향을 통해 DB보안 솔루션의 가이드 라인을 제시해본다.
|장윤정 기자·linda@datanet.co.kr|

인터넷 사용의 폭발적인 증가 및 국가 차원의 전자정부 구축, 디지털 콘텐츠 사업 지원 등의 확대에 따라 중요 정보에 대한 DB는 갈수록 쌓여가고 있다. 하지만 이런 DB들이 어떻게 관리되고 있을까?
관련전문가들은 “DB를 전문적으로 관리하는 인원도 부족하고 개인정보 보호 인식 부족 등으로 실상 DB에 대한 체계화된 관리, 보안은 거의 전무하다시피 했다”며 “특히 그간 외부로부터의 침입에는 민감해 해킹, 웜 등을 방지하기 위한 방화벽, IPS 등의 도입에 열을 올렸으나 내부 사용자를 단속함에 있어 거의 신경쓰지 못한 실정”이라고 언급한다.
각종 조사에 따르면, 조직 내에서 발생하는 정보 침해 사고의 70% 이상이 내부자에 의해 발생하는 것으로 분석되고 있다. 한국산업기술진흥원 조사에 따르면 조직 내 산업기술정보 유출자의 90% 이상이 현직/퇴직 사원이 차지하고 있다. 또 경찰청 집계에 따르면 내부자에 의한 정보유출 및 해킹 등의 범죄건수는 지난 2000년 278건에서 2001년 7천595건으로 급증했으며 그 이후에도 2배 이상씩 해마다 급격히 증가하는 추세다.
관련 전문가들은 “네트워크나 IT 시스템 보안에 국한된 것이 아니라 기업 전체의 정보보호를 위한 보안체계가 시급하다”며 “특히 내부 사용자들을 단속할 수 있는 특화된 정보보호 제품이 필요하며 기업 주요 정보, 고객 개인정보 등과 같은 외부 유출이 우려되는 DB를 보안할 수 있는 전용 보안 제품이 시급하다”고 언급하고 있다.

기업중요 자산 1호 DB를 보안하라
그동안 국내 보안시장을 주도해왔던 것은 해킹 및 바이러스 등의 외부침입에 효과적으로 대응할 방화벽, VPN 등의 외부침입 방지장치였다. 하지만 빈번하게 발발하고 있는 금융권 등에서의 횡령, 고객 DB유출 등 내부자 정보유출 사고는 해당 기업을 금전적 손실뿐만 아니라 고객의 불신, 기업이미지 하락 등 돈으로 환산할 수 없는 막대한 피해를 입게 한다.
국내에서도 피해사례는 갈수록 증가하는 추세다. 지난해 2월 은행 전산망 조작으로 거액의 인출 사건이 일어났으며 쇼핑몰 등의 해킹으로 10만명의 개인정보가 유출된 사고도 있었다. 또 지난 3월에는 대형 쇼핑몰의 데이터 유출 사고로 주요 일간지에 사과광고를 게재하는 등 사고가 잇따르고 있다. 특히 기업의 데이터베이스는 조직내에서 필요로 하는 정보를 체계적으로 축적하여 그 조직내의 이용자에게 필요한 정보를 제공하는 정보서비스 기관의 심장부에 해당된다. 따라서 데이터베이스에 대한 해킹이나 내부 사용자에 의한 누출은 기업의 신뢰성에 심각한 손상을 입혀 기업의 이미지 하락을 물론 금전적인 피해까지도 이어진다. 정부에서도 잇따른 정보 유출 사고로 인해 개인정보보호법 발효를 앞당길 조짐이다. 올 하반기로 예정돼 있던 개인정보보호법은 연초 예상보다 빨리 시행될 계획이며 이에 따라 사고 발생시 집단소송, 피해보상 등이 겹쳐진다면 기업에 막대한 손실을 일으킬 위험이 있다.
이미 가까운 일본에서는 개인정보보호법이 실행돼 각종 내부보안 솔루션들이 속속 구축되고 있는 실정이며 미국, 유럽 등에서도 샤베인옥슬리(Sarbanes-Oxley) 법안, 바젤 Ⅱ 등으로 기업, 금융 등에 프라이버시, 비즈니스 정보 등 각종 분야별 정보리스크에 대한 대비를 촉구하고 있다. 업계의 전문가들은 “완벽한 보안이란 불가능하며 결국 정보의 누출은 불가피할지 모른다”며 “따라서 정보가 누출될 수 있는 가능성을 최소화하며 설혹 누출되더라도 피해를 최소화할 수 있도록 시스템적인 보완이 필수다”고 언급하고 있다.
그렇다면 기업의 주요 자산인 데이터베이스를 보호할 수 있는 DB보안 솔루션이란 무엇일까?
DB보안 솔루션은 중요 정보를 데이터베이스에 보관할 시 해당 특정필드에 암호화를 적용하거나 사용자의 접근을 제어해 해킹 및 허가받지 않는 내부자에 의한 불법적인 정보유출을 방지하는 솔루션이다. DB보안은 크게 DB 사용자의 접근을 제어하는 방식과 DB 자체를 암·복호화하는 방식으로 나뉜다.
접근제어 방식의 경우 웨어밸리, 바넷정보기술, 피엠피시큐어 등 업체들이 포진하고 있다. 이들 업체는 DB에 대한 SQL(질의) 작업시 별도 서버에서 가상계정이 부여된 사용자만 접근이 가능하도록 해 비인가자의 DB접근을 원천 차단하며 해당 로그정보 저장·분석 기능이 탑재돼 DB가 유출되더라도 추적이 가능한 솔루션을 공급하고 있다.
또 최근에는 펜타시큐리티, 위즈메카 등이 DB 컬럼 단위로 선택적인 암호화를 통해 암복호화 권한과 암호가 없는 사용자에 의한 정보 유출과 사후 해독을 차단하는 ‘암복호화 방식’의 솔루션을 공급하기 시작했다. 또 소프트포럼, 이니텍, 케이사인, 한국전자인증 등은 DB에 PKI 기반의 암호화 및 전자서명을 적용해 특정 필드를 암호화하는 솔루션을 제공하고 있다. 나아가 파수닷컴 등은 기존 문서보안 솔루션을 응용해 개인 PC에 DB를 저장할 때 암호화하고 이를 로그관리할 수 있는 DB보안 솔루션 등도 내놓고 있어 DB보안은 다각도에서 접근이 이뤄지고 있는 추세다.



사전·사후 감사로 DB 접근을 차단하라
데이터베이스 SI 사업에서 쌓인 노하우를 바탕으로 DB보안에 접근하는 웨어밸리, 바넷정보기술 등은 DB에 대한 이해와 노하우를 기반으로 직접적인 암호화보다 접근제어, 감사 등에 특화된 제품으로 DB보안 시장에 접근하고 있다.
네트워크 및 서버 단계에서 DB에 대한 접근을 통제하는 솔루션인 DB 접근제어 솔루션은 DBMS에 부하를 주지 않고 SQL을 실시간 감시할 수 있도록 패시브 모드, 인라인 모드, IP포워드 모드 등으로 보안을 적용, DB보안에 필수적으로 발생할 수밖에 없는 속도저하를 최소화할 수 있다는 장점이 있다. 또한 사전 정보 접근 통제 및 사후 정보보호 감시체계로 구성되며 인가된 사용자를 어떻게 통제하고 감시할 것인가에 초점을 맞추고 있다.
웨어밸리(대표 손삼수)의 ‘샤크라(Chakra)’는 데이터베이스의 접근을 실시간으로 추적하고 감시하는 데이터베이스 보안 솔루션이다. 데이터베이스 서버에 누가, 언제, 어디서, 어떤 데이터를 조회 또는 변경했는지 100% 기록함으로써 데이터 유출 사고를 미리 방지하는 데 초점을 맞췄다. 또 사전접근통제용 ‘트러스티드 오렌지(TRUSTED Orange)’도 보유하고 있다.
웨어밸리 컨설팅 본부 김병철 본부장은 “이미 인가된 사용자가 접근할 수 있는 DB를 암호화하는 것보다 기록, 탐지, 감사에 초점을 맞춘 웨어밸리의 DB보안 솔루션은 써드파티 솔루션으로 성능저하 없이 DB에 생길 수 있는 사고를 사전, 사후 보안하는 것이 특징”이라며 “샤크라는 패킷 스니핑 방식을 이용해 서버나 네트워크에 부하를 주지 않고 100% 쿼리를 로깅하며 침입탐지, 침입차단 및 후속조치를 가능케 하고 트러스티드 오렌지는 종합적인 검색을 통해 문제 발생시 실행된 SQL 문장, 실행시간, 실행 유저 등을 추적하는 기능을 제공한다”고 설명했다.
웨어밸리는 기존 고객들인 금융권을 중심으로 샤크라, 트러스티드 오렌지를 공급하며 SMB용 어플라이언스 형태의 제품도 오는 6월중 출시할 계획이다.
바넷정보기술(대표 이창하)의 ‘미들만(Middleman)’은 내부 전산 정보의 유출·조작 등을 방지하기 위해 사전·사후 접근통제 기능을 제공한다. 게이트웨이 방식에 의한 DB접근제어 및 감사와 모니터링 시스템으로 내부사용자(IT부서직원 및 아웃소싱 업체 직원)에 의한 데이터 오남용 및 외부유출 방지 솔루션. 특징으로는 중요한 DB 작업은 온라인상으로 책임자의 승인 후 작업이 실행되도록 하며, 이 경우 모든 작업이력은 로그 DB에 저장한다. 기타 조회작업에 있어서는 개인별 조회건수, 다운로드 허용여부 등을 내부 보안정책에 따라 선택할 수 있는 옵션 기능을 내재하고 있다. 또 개인별 ID 관리를 통해 DB에 대한 사전접근을 통제하고 사고 발생 후 모니터링 및 이력관리 기능으로 원인을 추적, 분석할 수 있다.
한국증권금융에 미들만을 공급한 바넷정보기술은 올해 일본 시장 진출 등도 고려하며 금융, 공공, 통신, 제조 등 데이터베이스를 보유한 모든 회사들을 타깃으로 영업을 강화할 방침이다.
바넷정보기술의 김덕형 이사는 “태생자체가 금융권 현장에서 실무적인 요구에 의해 개발된 제품이어서 일반 연구소에서 이론적인 접근을 통해 개발된 제품이 가지는 한계성을 극복 가능한 것이 미들만의 장점”이라며 “금융권 마케팅은 직판에 주력하고, 비 금융권은 일반 및 공공기관을 분류하여 전문영업채널 구축을 통한 영업력 극대화를 통해 국내시장 석권을 목표로 하고 있다”고 언급했다.
역시 게이트웨이 방식의 권한제어 솔루션인 피앤피시큐어(대표 박천오)의 ‘DB세이퍼(DB Safer)’는 모든 DB 접속 요청이 DB 보안 게이트웨이를 통해 처리되며 게이트웨이 방식만이 할 수 있는 보안기능으로 DB 서버에 명령을 입력하기 전에 DB세이퍼가 보안 정책에 의해 차단이 가능하다. 접속제어, 권한제어, SQL 감시 및 로깅, 보고서 등의 기능을 제공하며 바이패스 기능으로 네트워크 중단사태를 방지해주며 속도 문제도 해결해 준다.
피앤피시큐어의 박천오 사장은 “DB세이퍼는 오라클 커넥션을 유지하면서 사용자가 모르게 처리, 로그 자체를 DB에 쌓으면서도 시스템에 부하를 안 가게 하는 것이 특징이다”며 “DB에 로그를 넣으면 하드웨어 성능이 급격히 떨어지는 것이 문제인데 그 문제를 해결했다. DB보안을 적용하면 성능이 떨어지는 건 당연하지만 사용자가 느낄 만큼의 속도는 아니다”라고 언급했다. 피앤피시큐어의 DB보안 제품은 현재 오라클 DBMS 중심이지만 싸이베이스, MS SQL 등을 제공하는 제품을 3/4분기에 개발할 예정이다. 또 텔넷보안을 강화시키는 신제품도 개발중이다. 서울대, 기획예산처, 성남시청, 파주시청 등의 레퍼런스를 보유하고 있는 피앤피시큐어는 관공서 등 공공 기관을 타깃으로 집중 영업하며 금융권 고객도 강화, 올해 약 18억원 이상의 매출을 올린다는 방침이다.



DB 자체 암호화로 안전보장 ‘OK’
하지만 DB에 가능한 부하를 최소화하면서 DB의 사전 접근 제어, 사후 감사 등을 통해 DB의 안전한 사용을 보장하는 컨셉도 좋지만 만약 DB가 유출됐을 시에는 접근제어 솔루션 만으로는 해결할 수가 없다. 이에 DB자체를 암호화, 데이터 유출시에도 안전을 보장할 수가 있는 DB암호화 솔루션도 속속 출시되고 있다. DB 자체를 암호화하는 솔루션은 해킹 및 내부 사용자의 DB 유출시에도 DB의 불법적인 사용을 차단할 수 있다는 점에서 각광받고 있다.
지난해 초반부터 제품을 출시, 선발업체로 기반을 다진 펜타시큐리티(대표 이석우)는 DB보안 솔루션인 ‘디아모(D’Amo)’를 올해의 주력사업으로 레퍼런스 확보에 박차를 가할 방침이다. 오라클 DB에 적용 가능한 디아모는 기업내 DB보안 정책 수립 및 적용, 암호화된 컬럼에 대한 세분화된 접근 컬럼 및 부여 등의 기능을 제공하며, 타사보다 풍부한 고객사 지원 노하우를 내세운다.
DB내 주요 데이터를 컬럼 단위로 암호화해 성능 저하를 최소화하며 총 16단계의 암호화 작업으로 암호화 설정 및 제거 작업 중 에러가 발생되면 자동 롤백 또는 수정 후 작업이 진행되기 때문에 기업의 연속성을 보장할 수 있다. 또한 디아모는 DB에 직접 탑재돼 설치가 간단하다는 것이 장점이며 장비와 네트워크 구성 변경 등의 기존 시스템 변경이 전혀 없다.
펜타시큐리티 기술지원팀 이종의 부장은 “지난해보다 올해 DB보안에 대한 고객들의 문의가부쩍 늘어 올 하반기쯤되면 본격적으로 DB보안 시장이 꽃을 피울 수 있을 것”이라며 “DB보안의 성격이 아니면서도 DB보안 기능이 가능하다고 언급하는 업체들이 많아 업체간 난립, 출혈 경쟁 등이 우려된다. 고객들이 제품 성능을 신중히 살펴보고 선택해야할 것”이라고 조언했다.
펜타시큐리티는 최근 공정거래위원회와 정읍시청에 디아모를 공급한 것을 비롯해 기업은행, 수출입은행 등 금융권과 경찰청, 부패방지위원회, 서울시청, 울산시 교육청, 그리고 서울대학교 등 다수의 고객사를 확보하고 있다.
지난 3월 DB보안 사업을 위해 설립된 위즈메카(대표 오영섭)은 미국 프로테그리티의 ‘시큐어닷데이터(Secure.Data)’의 총판이다.
시큐어닷데이터는 필드 및 사용자 레벨에서의 보안 감사 정보를 제공하며 DB관리와 보안 관리의 권한분리, 테이블내 특정 컬럼의 암호화, 접근제어, DB 접근에 대한 감사 로그, 키관리, AES, 3-DES 등 국제 표준 암호화 알고리즘 적용 등이 특징이다. 또한 오라클 DB뿐만 아니라 MS SQL, DB2, 사이베이스, 인포믹스, 테라데이타 등 거의 모든 시중에 나와 있는 DBMS를 지원할 수 있다. 솔라리스, 윈도, 리눅스, NCR 등 운영체계도 모두 지원한다. 시큐어닷데이터 역시 DB에 직접 엔진이 창착되지만 애플리케이션에서 암호화를 적용해도 인덱스를 타고 내려오기 때문에 성능 저하를 5% 내외로 안정화시킨 것도 장점으로 내세운다.
위즈메카의 오영섭 사장은 “시큐어닷데이터의 특성은 성능과 안정성으로 글로벌한 노하우가 보장된 제품”이라며 “오라클 등 DBMS의 사상과 함께 개발됐기 때문에 오라클 등의 업데이트가 이뤄지면 소스 코딩 없이 바로 커스트마이징 해줄 수 있어 적용이 손쉽다”고 언급했다. 또 그는 “가격은 다소 비싸지만 제품의 품질과 AS 측면에서 결코 떨어지지 않는 제품이다”며 “혹여라도 DB에 영향을 주지 않는지 걱정하는 고객들을 위해 부분적으로 적용시켜가며 검증되면 전사적으로 도입하는 것을 권유하고 있다”고 덧붙였다.
위즈메카는 현재 대주주이자 주요 리셀러인 퓨처인포넷을 비롯해 리셀러를 모집중이다. 향후 리셀러 중심으로 판매할 계획이며 주요 고객 대상의 타깃마케팅으로 인지도를 높이는데 우선 주력할 방침이다.

PKI 암호화로 한층 강화된 DB 보안 ‘약속’
이처럼 DB의 특정 부분을 컬럼 단위로 암호화시키고 DB 자체에 직접 탑재되는 디아모, 시큐어닷데이터와는 조금 다르지만 역시 DB암복호화를 통해 DB를 보안하는 제품을 출시하고 있는 소프트포럼, 이니텍 등의 제품도 서서히 세력을 넓혀가는 중이다. 주로 PKI 기반의 암호화를 수행하던 업체들에서 암호화 기법에 대한 노하우를 적용시켜 제품 출시를 서두르고 있는데 이 제품들은 DB와 별도로 서비스 서버 등에서 DBMS와 연동해 DB암복호화를 수행하는 것이 특징이다.
소프트포럼(대표 김상철·정현철)은 ‘엑스시큐어(Xecure)DB 스탠더드’와 ‘엑스시큐어 DB 엔터프라이즈’ 두 종류의 제품을 구비하고 있다. 엑스시큐어 DB는 애플리케이션과 연동해 데이터 암복호화를 수행하는 방식의 DB암호화 제품이며 엑스시큐어 DB 엔터프라이즈는 DBMS와 연동해 데이터 암복호화를 수행하는 방식의 DB 암호화 제품이다. 이 제품은 애플리케이션 수정이 필요 없어 적용이 간편하나 애플리케이션 서버와 DB서버 간 구간 암호화를 고려해야 한다.
DBMS와 연동한 DB 암/복호화, 전자서명, 검증 기능, 데이터의 선택적 암/복호화, 전자서명, 검증 기능, PKI를 이용한 관리자 인증, 키 관리 기능, 암/복호화에 관련된 키의 안전한 관리, 암/복호화 응답 성능 증대를 위한 캐시 기법 구현 등이 엑스시큐어 DB 시리즈의 장점이다.
소프트포럼도 PKI 등에서 쌓은 노하우와 기술력을 접목시켜 자사의 DB보안 솔루션 ‘엑스시큐어(Xecure)DB 엔터프라이즈’에 접목시킬 계획이다. 소프트포럼 아이덴티티 솔루션 개발팀 심문수 팀장은 “현재 기존 엑스시큐어 DB 엔터프라이즈의 제품외에도 ‘시큐어 DB 스탠더드’ 버전 업그레이드 출시를 위해 DB전문업체와 공동개발한 제품이 곧 발표될 것”이라며, “공공 기업 금융기관 등 전 기관에 걸쳐 폭넓게 영업할 방침”이라고 밝혔다. 또 소프트포럼은 삼성카드, 롯데카드, 금융결제원, 국세청, 서울교통카드, 모디아 등에 엑스시큐어 DB를 공급, 올해 레퍼런스를 전방위로 넓혀갈 계획이다.
지난해 10월에 출시된 이니텍(대표 김재근)의 ‘세이프(Safe) DB’는 데이터베이스에 저장된 데이터를 암호화하고 데이터베이스에 대한 접근을 제어함으로써 중요한 데이터를 보호할 수 있는 데이터베이스 보안 솔루션이다. 세이프 DB는 데이터베이스에 저장된 데이터를 암/복호화하기 위해 애플리케이션의 수정이나 별도의 개발 과정 없이 데이터베이스에 추가 설치하는 과정만으로 중요 데이터를 암호화하고 간편하게 보안정책을 적용할 수 있는 것이 장점이다.
또한 허가된 사용자 이외에는 암호화된 정보에 접근할 수 없도록 함으로써 보안을 강화했고, 데이터베이스 관리자도 보안관리자의 승인 없이는 암호화된 정보를 조회하거나 수정, 삭제할 수 없어 내부자에 의한 정보 유출을 방지할 수 있다. 키의 안전한 저장 및 관리를 위한 하드웨어 방식의 스마트 카드도 지원한다. 아직 오라클 버전만을 지원하지만 곧 사이베이스와 인포믹스 지원용으로 업그레이드할 방침이다. 한편 이니텍은 기존에 보유한 ‘이니세이프(INISAFE) DB 프로텍터(Protector)’ 제품과 새로 출시한 세이프 DB를 연동해 시너지를 높일 방침이다.
이니텍의 보안사업부 이홍일 팀장은 “DB보안의 가장 큰 관건은 성능이다”며 “PKI 등은 강제사항으로 정부에서 설치를 권유하지만 기업의 주요 정보가 담겨있는 DBMS는 상당히 민감한 부분이라 강제사항이 적용된다 해도 성능 저하를 꺼려 고객들이 적용하는데 주저한다”고 언급했다. 또 그는 “이에 따라 이니텍은 성능에 가장 적은 영향을 미치는 제품을 개발하는 한편 대형 레퍼런스 확보에 가장 큰 주안점을 둘 예정”이라고 언급했다.
올해 말경 출시 예정인 케이사인(대표 최승락)의 ‘케이사인 시큐어(KSignSecure)DB’는 DB 암호화를 위해 성능적인 측면을 고려한 대칭 키 사용 및 키 관리 안전성 확보를 위해 키 프로파일 관리 목적의 PKI 기술을 조합, 사용함으로 성능적, 보안적 수준을 강화한 제품이다.
케이사인 시큐어 DB는 개인정보보호를 위해 개인별 키 프로파일 관리를 통해 개별적 개인정보 접근 통제에 따른 프라이버스 보안 기능을 제공하며 필드별, 정보별 암호 키를 별도 사용할 수 있도록 함으로 암호 키 파괴 및 노출에 대한 피해를 최소화할 수 있다.
또한 암호화 키의 안전성 보장을 위해 주기적인 키 갱신 처리에서 다수의 키를 사용하고 사용되는 키의 갱신 주기의 분산화 처리로 인한 성능적으로 발생하는 문제점을 미연에 해결할 수 있는 메커니즘을 제공할 계획이다.
케이사인 구자동 연구소장은 “비교적 후발로 출시되는 만큼 차별화된 요소를 갖기 위해 케이사인은 PKI, EAM, SSO 등 기존 제품들과 결합된 기능의 좀더 견고한 보안솔루션으로 출시, 기술력으로 승부할 것”이라며 “개인정보보호법이 시행될 하반기와 맞물려 제품을 출시하고 우선 공공 기관 등을 중심으로 영업한다는 방침”이라고 언급했다. 또 그는 “케이사인 시큐어 DB는 데이터 암호화키는 제공 서비스 외부에 노출되지 않고, 데이터 암/복호화 시점에만 인가된 자에게 해당 암호 키를 제공하기 때문에 안전한 암호화 관리가 가능하다”며 “특히 데이터를 필드(컬럼)보다 미세한 로우(레코드)별로 암/복호화를 시킬 수 있어 성적 등 같은 종류의 데이터에 대해 권한별로 데이터 접근허용 범위를 다양화할 수 있는 것이 장점”이라고 강조했다.

네트워크·PC단위의 DB보안까지 책임진다
한편 S/W타입의 DB보안 솔루션이 주류를 이루고 있는 가운데 한국전자증명원(대표 이재동)은 美 인그리안사의 하드웨어 일체형 DB 암호화 솔루션 ‘데이터시큐어’를 출시, 판매하고 있다.
이 제품은 DB서버 뿐 아니라 애플리케이션 서버, 웹서버에 연동되며 인터넷 환경에서 데이터를 암호화된 상태로 전송, 저장한다. 보안 적용대상 서버의 플랫폼에 상관없이 설치가 가능하며 오라클, MS SQL, DB2, 사이베이스, 인포믹스 등을 적용할 수 있다. SAN, NAS 환경의 DB암호화도 가능하며 어플라이언스 타입이기 때문에 환경 변화에 따른 위험성도 최소화되고 다량의 애플리케이션/DB 서버와도 연동될 수 있다는 것도 장점이다.
데이터시큐어는 FIPS 지원과 리던던시 지원의 ‘i211, i215, i221, i225’의 4가지 형태의 모델이 있으며 최상위 모델인 i225는 기가비트 인터페이스를 지원한다.
한국전자증명원의 솔루션사업부 박태희 과장은 “하드웨어 형태의 제품이라 네트워크에 붙어 동작하기 때문에 위치적으로 유연성, 호환성이 뛰어나다”며 “소프트웨어적으로 DB에 직접 설치되는 것이 아니라서 DB에 주는 부하가 적어 속도 저하 걱정이 없다. 특히 기가비트급 성능을 제공하는 하이엔드 모델도 있어 전체 네트워크 속도 저하를 우려하는 고객들의 걱정도 덜었다”고 언급했다.
한국전자증명원은 금융, 통신, 대기업, 공공 등에 활발히 영업중이며 상반기까지는 우선 인지도를 알리는데 주력하며 하반기부터 본격 기술력으로 승부한다는 방침이다.
또한 문서보안 솔루션을 주력으로 시장에 공급하던 파수닷컴(대표 조규곤)은 최근 자사의 문서보안 솔루션 기술을 활용해 DB보안에 접목시킨 솔루션을 공급중이다.
파수닷컴은 파일전달 솔루션 랩소디, 문서전달 솔루션 FSD를 응용해 파일 생성시의 암호화, 사용내역, 로그관리 등의 기능을 제공한다. 한마디로 개인용 PC에 저장할 때 암호화할 수 있도록 지원하는 파수닷컴의 솔루션은 쇼핑몰 등에서 택배사나 아웃소싱 업체로 DB를 넘겨주고 받을 때 DB가 유출될 수 있는 가능성을 최소화하기 위해 개발됐다.
로그로 남겨야 하는 일련번호, 형태, 일시, 목적, 행위를 한 사람의 소속 및 성명, 전달 받을 자, 파기 일자의 7가지 항목에 대해서 관리할 수 있도록 제품을 공급중이며 DRM의 원천 기술을 응용해 DB에 적용시킨 것이 특징이다.
파수닷컴의 사업부 김규봉 부장은 “DB를 조회하는 사용자를 통해 유출되는 경우가 많아 이에 대비하기 위해 개발된 파수닷컴의 DB보안 솔루션은 경쟁사들이 갖지 못하는 ‘추적관리기술’이 파수닷컴의 장점이다”라며 “파수닷컴의 제품은 외부에서 사용시 자동으로 로그가 남는 특징으로 외부 유출을 방지하고 외부 유출시 이에 대한 추적이 가능하다”고 언급했다.
파수닷컴은 중소기업들이 요구가 많기 때문에 가격정책은 유연하게 가져갈 예정이다. 기존 랩소디 서비스의 ASP 서비스와 맞물려 DB보안 솔루션도 ASP 서비스를 개시, 고객들이 초기에 큰 투자비용 없이 구축가능토록 ASP형 서비스를 준비중이다. 쇼핑몰, 유통업체, 운수업체, 숙박업, 학원 등을 주 타깃으로 영업하고 있는 파수닷컴은 최근 고객들의 문의가 줄을 이어 올해 DB보안과 관련해 10억원 이상의 매출을 올릴 수 있을 것으로 기대하고 있다.

올해 약 150억원 시장 형성 기대
이처럼 다양한 방식의 DB 보안 솔루션들이 격돌을 벌이고 있는 가운데 올해 국내 DB보안 시장은 약 150억원 이상의 시장을 형성할 전망이다. 본지의 조사에 의하면 국내 주요 DB보안 업체들은 약 200억원 가량의 예상매출액을 제시했지만 다소 부풀려진 경향이 크고 전체 보안 시장 규모 등을 감안할 때 DB보안에 대한 도입이 활발하다면 약 150억원 가량의 시장 형성을 기대할 수 있을 것으로 전망된다.
지난해 고작 20억~30억원대의 시장을 형성했던 것에 비하면 엄청난 성장을 기대하는 것인데 이에 대해 업계의 관계자들은 개인정보보호법 발효에 대한 기대심리와 도처에서 발생되고 있는 보안 사고로 기업들의 경각심이 고조돼 DB보안에 대한 요구가 갈수록 커지고 있기 때문에 기대해 볼만한 수치라고 언급한다.
DB보안 제품을 출시하고 있는 업체들은 공공, 금융 등이 우선 주 고객으로 떠오를 전망이라고 이구동성으로 언급하고 있다. 특히 금융 기관의 요구가 강하며 공공과 통신 등 고객서비스를 우선하는 곳에서도 DB보안에 대한 필요성이 대두되고 있다.
한 업계의 관계자는 “올해는 DB보안 암호화의 원년이 될 것”이라며 “대다수의 업체들이 금융권을 타깃으로 주로 생각하지만 고객의 정보를 취급하는 모든 곳이 고객이 될 수 있다. 따라서 시장은 엄청나다”고 언급했다.
지금까지 국내 DB 보안은 암호화나 인증 또는 접근통제 방식 등의 기술적 관점에서 논의돼 왔다. 그러나 데이터베이스는 조직의 가장 내부에 존재하는 정보이고 이미 인가된 사용자들만 접근할 수 있다는 것을 기본 조건으로 한다. 즉 DB 보안이라는 개념이 일반적인 보안의 의미가 아니라 데이터베이스 관리자 등의 인가된 사용자들의 작업 내역에 대한 감사, 내부 사용자들의 자료 유출 방지 등의 의미로 초점이 맞춰져야 한다는 것이다.
따라서 DB보안은 기술적 관점이 아닌 경영적, 조직적, 프로세스적 관점으로 논의돼야 한다. 보안 담당자와 외부 침입 방시 시스템이나 DB 암호화, 인증시스템 등의 기술적 인프라에 대하여 논하는 하부적인 방식이 아니라 CEO 또는 CIO가 기업 정보자산에 대한 보안 전략을 수립하고 그에 따른 조직을 운영하고 업무 절차를 수립한 후 효율적인 보안 임프라를 구축하는 전사적 체제의 보안 방식으로 운영돼야 한다는 것.
업계의 전문가들은 “문제가 있는 것을 고객들이 알지만 일단 효과가 눈에 보이는 부분부터 치중하는 경향이 있다. DB보안을 안했다고 윗선에서 지침이 내려오는 것도 아니라서 일선에서 DB보안은 IPS 등과 같은 눈에 보이는 보안제품보다 밀리는 경향이 있다”며 “IPS나 방화벽 등을 설치하는 이유도 궁극적으로는 서버의 데이터베이스를 보안하기 위한 것이다. DB보안은 기업의 핵심이다”고 언급하는 등 경영자들의 DB보안에 대한 각성을 촉구하고 있다.
국내에서도 DB보안 시스템이 단지 DB에 대한 접근을 막는 한정적인 의미에 멈출 것이 아니라 기업의 가장 근간이 되는 중요 정보자산에 대한 궁극적인 보호와 건전한 운영을 위한 전사적인 경영 전략과 그에 따른 시스템 운영의 방식으로 인식되어야 할 것이다. 그리고 그런 움직임은 이미 고객들로부터 시작돼 올해 국내 DB보안 시장은 개화의 원년으로 향후 지속적인 성장이 기대되고 있다.

인지도 확보·고객 마인드 전환 관건
하지만 DB보안 솔루션은 아직 정형화된 표준이 없고 SI성으로 진행되는 경우가 많다. 또 DB에 보안을 걸면 어떤 방식으로 적용시키든 적용전보다 시스템의 속도가 느려지기 마련이다. 따라서 속도저하를 꺼리는 관리자들에 의해 DB보안 솔루션 도입이 쉽게 이뤄지지는 못한다. 한 업계의 관계자는 “DB보안 솔루션을 제공하는 업체들은 고객들의 속도와 편이성을 최우선으로 고려해야한다. 절대 사용자에게 불편을 줘서는 안된다”며 “정보를 보호해주는 바탕위에서 성능과 안정성이 보장돼야만 DB보안이 진정으로 확산될 수 있을 것”이라고 언급했다.
또 기존 애플리케이션을 얼마나 수정해야 하는지 기존 시스템과의 호환성 등도 걸림돌이 되고 있으며 무엇보다 DB보안에 대한 고객들의 인식이 부족하다는 점이 DB보안 확산의 근본적인 문제라고 지적한다. 관련 전문가들은 “개인정보에 대해 보안해야하는 필요성은 공감하지만 그 정도가 약하다”며 ”주요정보는 반드시 암호화해서 저장해야하며 권한에 따른 접근제어가 반드시 필요할 것“이라고 언급하고 있다.
그러나 가장 큰 문제는 DB보안의 시장 활성화를 기대하며 무분별하게 진입하고 있는 업체들의 난립이다. 앞서 살펴본 것처럼 DB보안은 데이터베이스에 접근하는 방식이 여러 가지이고 다양한 형태의 제품이 공존하고 있어 제품 선택에 고객들의 혼란이 우려된다. 따라서 고객들은 자사의 상황을 우선적으로 파악한 후 자사의 형태에 가장 적합한 제품을 고르는 현명한 선택이 요구되고 있다. 즉 시스템 성능을 최우선으로 고려하고 있는 고객이나 내부 사용자에 대한 정보 유출의 가능성이 낮다고 판단된다면 예방적 의미로 접근제어, 사후 감사 솔루션 등이 적합할 것이다.
반면 아웃소싱 업체를 많이 활용하고 있거나 다양한 사용자들이 DB에 접근할 가능성이 있다면 DB자체를 암호화하는 솔루션 등이 적합하다. 공급 업체들도 자사의 제품이 최고라는 식으로 선전하기 보다 고객 상황을 먼저 살피고 이에 적합한 제품을 공급, 시장 자체를 키워나가려는 노력이 선행돼야할 것이다.
현재 대부분의 기업은 중요 데이터에 대한 데이터베이스 의존도가 증가하고 있으며 주요 정보에 대한 침해사고 및 도용, 악용사례가 늘어나는 추세다. 고객정보, 금융정보 등 정보 유출시 심각한 피해가 발생할 수 있으며 이는 기업 이미지 추락으로 이어져 금액으로 환산할 수 없는 피해를 입히게 된다.
따라서 이런 문제를 해결하기 위해 DB에 대한 전문적이고 강력한 보안 솔루션의 필요성이 대두되는 것이다. DB보안은 주요 DB의 유출을 방지하고 고객의 신뢰성을 향상시키며 기업 정책 및 가이드라인을 만족시키는 솔루션으로 그 유용성이 점차 증대될 전망이다
Posted by 티엘로

JAVA 를 DB에 연결하는 Exam - 1

* 시나리오 Java를MySQL 연결

JAVA 가 설치된곳의 라이브러리 폴더에
mysql-connector-java-5.1.5-bin.jar 라이브러리 추가
(http://www.mysql.com Download 받을수있음)

java.sql.*    import
===========================================================
Connection 타입의 맴버변수 추가 (초기값 null)
     - Exam) Connection m_connection = null;
===========================================================
Class의 생성자에서 드라이버클래스 로드
     - Class.forName( "com.mysql.jdbc.Driver" );
        * try-catch 로 예외처리 할것!
===========================================================

커넥션을 만들기위한 함수 를 추가하여 커넥션 생성

(함수명은 자유롭게)

     - m_connection = DriverManager.getConnection("jdbc:mysql://host:port/db","ID","PWD" );
         *try-catch 로 예외처리 (이때 SQLException 으로 예외처리 )


이후 생성자의 드라이버 클래스 로드 후 바로 커넥션 함수 호출
===========================================================
Exam 1) select * from testdb

ResultSet rs; //쿼리 결과를 저장하기위한 데이타형식
Statement st;
String sql = "select * from testdb"; //쿼리문
try{
      st = m_connection.createConnection();
      rs = st.executeQuery( sql ); //쿼리 결과 rs에 저장
          while( rs.next() ) //쿼리의 결과를 Record 단위로 가져옴
          {
           System.out.println( rs.getString("필드명") + " " + rs.getInt("필드명") );
           }
             rs.close();
             st.close();
          }
catch( SQLException e )
{
         System.out.println( e.toString() );
}



Exam 2) 데이터의 삽입 수정 삭제

Exam 1)과 다른점은 없다
다만 쿼리문을 보낼때 st.executeQuery() 가 아닌 st.excuteUpdate() 함수를 통해서 보내야한다.
이때 쿼리문이 정상적으로 처리되었다면 1이라는 값을 반환한다 또한 테이블을 반환 받는 쿼리가 아니므로ResultSet 타입의 변수는 필요없음

Posted by 티엘로