카테고리 보관물: Database

[DB Modeling] ERwin Data Modeler 사용법

1. ERwin 사용하기

1. 새 파일 만들기

2. 특성 바꾸기

1) 파일 이름 설정하기

2)Notation에서 표기방식 IE로 설정하기

3. 엔티티 만들기

– Enter: 이름/기본키/속성에서 빠져나가기

-Tab : 다음으로 넘어가기

– 더블클릭: 편집

– F2 : 속성 이름 바꾸기

4. 각 속성의 자료형 설정하기(도메인)

– 아래 예시에서는 이미 모든 속성을 만들었지만 오른쪽 클릭->Attribute에서 새로운 속성을 만들 수 도 있다.

– 이렇게 새로운 속성을 만들면서 자료형을 지정할 수 도 있고, tab키를 이용해 엔티티를 만들 때 속성명을 모두 지정한 뒤

attribute 창에서 Domain을 지정할 수 도 있다.

– 도메인(Domain) : 속성의 값, 타입, 제약사항등에 대한 값의 범위

-모든 속성에 그 특성에 맞게 자료형을 지정해준다.

5. 식별관계, 비식별 관계

– 식별 관계(Identifying Relationship): 부모 테이블의 기본키가 자식 테이블의 기본키 혹은 후보키 그룹의 구성원으로 전이되는 것 -> (부모테이블) 사원 엔티티, (자식테이블) 신체정보 엔티티

– 비식별 관계(Not Identifying Relationship): 부모 테이브릥 기본키가 자식 테이블의 일반 칼럼으로 전이되는 것

-> (부모테이블) 부서 엔티티, (자식테이블) 사원 엔티티

관계에 대한 옵션 설정: 부서 테이블과 사원 테이블 간의 관계선을 선택한 후 오른쪽 클릭해서 Relationship Properties 메뉴 선택
Verb Phrase에서 각 관계에 대한 설명을 입력한다
Relationship Type은 부서 테이블과 사원 테이블의 관계 유형이 비식별 관계이므로 Non-Identifying이 선택되었다.

관계 옵션을 설정한 뒤 바탕화면에서 오른쪽 버튼을 누르면 뜨는 팝업 메뉴의 Relationship Display로 설정

6. 다 대 다 관계 정의

1) 다 대 다 관계는 부모자식 관계가 아니므로 순서에 상관없이 ER-Win Toolbox에서 다 대 다 관계선을 선택하여 연결

2) 다대다 관계를 1 대 다 관계로 바꾸기 위해서는 관계선에서 오른쪽 클릭 후 팝업 메뉴에서 ‘Create Association Entity’를 선택하거나 다 대 다 관계선을 선택한 후 ‘ERWin Transform Toolbar’에서 ‘Many to Many Transform’버튼을 클릭하면 마법사가 나타난다.

3) 다음 버튼을 누르면 새롭게 추가될 교차 실체의 실 체명을 정의하는 대화상자가 나온다. 여기서 Entity Name에 ‘입고’라고 정의한후 다음 버튼을 선택한다.

4) Transform Name을 정의하는 대화상자가 나타나면 기본값으로 설정한 뒤 다음 버튼을 누른다

5) 현재 작업에 대한 정보가 나오면 마침 버튼 을 누르고 작업을 완료한다.

공급업체/상품 테이블의 다대다 관계가 해소되어 교차 실체가 정의(입고)된 모습

상품/회원 테이블간의 다 대 다 관계가 해소되어 판매엔티티를 정의한 모습

모든 관계선을 차례대로 선택한 후 오른쪽 버튼의 ‘Relationship Properties..’메뉴를 선택한 후 Relationships대화상자에서 ‘Non-Indentifying’옵션 과 ‘No Nulls’옵션을 선택한다.

입고/판매 테이블의 속성들을 추가하여 다이어그램을 완성한다

7. 재귀적 관계 정의(순환관계, Recursive Relationship)

– 비식별 관계선을 이용하여 자기 자신과 관계를 맺는 재귀적 관계를 정의한다.

– 동일한 PK를 가진 테이블을 별도로 분리하는 복합형(다중Entity)과 Entity타입 내에서 자기자신을 참조하는 통합형(단일 Entity)으로 나뉜다.

Rolename 정의 ⇒ Relationships 대화상자에서 Rolename 탭을 선택한 후 Rolename입력상자에 ‘직속상사번호’를 기술 (‘직속상사번호’라는 속성이 FK로 전이됨)

8. 슈퍼타입과 서브타입

슈퍼타입 엔티티란 상호 배타적인 더 작은 그룹으로 분할되는 엔티티를 의미한다
슈퍼타입 내의 분해된 그룹을 서브타입 엔티티라고 한다
하나의 엔티티에서 배타적인 관계를 파악하고 이를 각각의 실체로 정의하고자 했을 때 각각의 실체에 공통되는 속성들을 모아놓은 엔티티를 슈퍼타입으로 개별적인 속성들을 따로 분리해서 각각의 서브타입으로 정의할 수 있다.
예를 들어 아래 그림과 같이 사원 엔티티가 있다고 하면, 한 회사에는 정규직 사원과 임시직 사원이 있을 수 있다. 일반적으로 정규직 사원은 연봉으로 임시직 사원 은 월급이나 수당으로 급여내역이 정의되므로 하나의 레코드에 입력될 경우 정규직은 월급과 수당에, 임시직은 연봉에 Null 값이 입력될 것이다.

1) 우선 아래와 같이 사원, 정규직, 임시직 엔티티를 만든다.

2) 슈퍼타입과 서브타입에 관한 정의는 ERWin Roolbox의 ‘Exclusive sub-category’버튼을 이용 한다.

3) ‘Exclusive sub-category’ 버튼을 선택하고 슈퍼타입을 먼저 선택한 후 서브타입 중에 하나인 정규직 엔티티를 선택하면 다음과 같은 모습이 된다

4) 슈퍼타입과 서브타입의 관계 설정은 Exclusive-sub Category 버튼을 누른 상태로 중간에 있는 교차점을 선택한 후 임시직 엔티티를 선택하면 다음과 같이 슈퍼타입과 서브타입의 관계가 형성된다

How to use Oracle dbms_crypto (crypto package library)

오라클 10g 이상에서 사용할 수 있는 DBMS_CRYPTO 패키지를 이용하여 암호화 하는 방법

DBMS_CRYPTO 패키지 생성

설치 되어 있지 않을 경우 다음의 SQL을 수행해서 패키지를 생성한다.

/$ORACLE_HOME/rdbms/admin/dbmsobtk.sql

/$ORACLE_HOME/rdbms/admin/prvtobtk.plb

두 패키지를 추가하는 방법..

A. 오라클 서버에 sysdba으로 접속.

>sqlplus “/ as sysdba”

B. ‘dbmsobtk.sql’ 파일 실행.

리눅스 : @$ORACLE_HOME/rdbms/admin/dbmsobtk.sql;
윈도우 : @%ORACLE_HOME%/rdbms/admin/dbmsobtk.sql;

C. ‘prvtobtk.plb’ 파일 실행.

리눅스 : @$ORACLE_HOME/rdbms/admin/prvtobtk.plb;
윈도우 : @%ORACLE_HOME%/rdbms/admin/prvtobtk.plb;

D. 추가 후 시스템 계정으로만 사용 가능하기 때문에 권한 변경.

이 경우 모두다 실행 가능하도록 하였지만 필요하다면 특정 계정에만 실행권한 주면됨.

grant execute on dbms_crypto to public;

grant execute on dbms_obfuscation_toolkit to public;

# 참고) 콘솔에서 실행 전체 과정.

>sqlplus “/ as sysdba”

SQL>

SQL> @/oracle/rdbms/admin/dbmsobtk.sql

(중간 생략)

No errors.

Synonym created.

SQL> @/oracle/rdbms/admin/prvtobtk.plb

(중간 생략)

Package body created.

No errors.

SQL> grant execute on dbms_crypto to public;

Grant succeeded.

SQL> grant execute on dbms_obfuscation_toolkit to public;

Grant succeeded.

SYS유저에서 사용할 패키지를 생성 함.

테스트에서 사용 할 암호 키 값은 ‘12345678‘을 사용한다.

DOWNLOAD SAMPLE SCRIPT : CRYPTO_PACKAGE_CREATE_SCRIPT

CREATE OR REPLACE PACKAGE PKG_CRYPTO
IS
FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2, KEY_DATA IN VARCHAR2 := ‘12345678’)RETURN RAW;
FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2, KEY_DATA IN VARCHAR2 := ‘12345678’)RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY PKG_CRYPTO
IS
— 에러 발생시에 error code 와 message 를 받기 위한 변수 지정.
SQLERRMSG VARCHAR2(255) ;
SQLERRCDE NUMBER;
— 암호화 함수 선언 key_data 는 입력하지 않을 시에 default 로 12345678 로 지정됨.FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2
, KEY_DATA IN VARCHAR2 := ‘12345678’
)
RETURN RAW
IS
KEY_DATA_RAW RAW(4000) ;
CONVERTED_RAW RAW(4000) ;
ENCRYPTED_RAW RAW(4000) ;
BEGIN
— 들어온 data 와 암호키를 각각 RAW 로 변환한다.
CONVERTED_RAW := UTL_I18N.STRING_TO_RAW(INPUT_STRING, ‘AL32UTF8’) ;
KEY_DATA_RAW := UTL_I18N.STRING_TO_RAW(KEY_DATA, ‘AL32UTF8’) ;
— DBMS_PKG_CRYPTO.ENCRYPT 로 암호화 하여 encrypted_raw 에 저장.
ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC => CONVERTED_RAW,
— typ 부분만 변경하면 원하는 알고리즘을 사용할 수 있다.
— 단, key value bype 가 다 다르니 확인해야 한다.
TYP => DBMS_CRYPTO.DES_CBC_PKCS5, KEY => KEY_DATA_RAW, IV => NULL) ;
RETURN ENCRYPTED_RAW;
EXCEPTION
WHEN OTHERS THEN
RETURN INPUT_STRING;
END ENCRYPT;FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2
, KEY_DATA IN VARCHAR2 := ‘12345678’
)
RETURN VARCHAR2
IS
CONVERTED_STRING VARCHAR2(4000) ;
KEY_DATA_RAW RAW(4000) ;
DECRYPTED_RAW VARCHAR2(4000) ;
BEGIN
KEY_DATA_RAW := UTL_I18N.STRING_TO_RAW(KEY_DATA, ‘AL32UTF8’) ;
DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(SRC => INPUT_STRING, TYP => DBMS_CRYPTO.DES_CBC_PKCS5, KEY => KEY_DATA_RAW, IV => NULL) ;
— DBMS_PKG_CRYPTO.DECRYPT 수행 결과 나온 복호화된 raw data 를 varchar2 로 변환하면 끝!
CONVERTED_STRING := UTL_I18N.RAW_TO_CHAR(DECRYPTED_RAW, ‘AL32UTF8’) ;
RETURN CONVERTED_STRING;
EXCEPTION
WHEN OTHERS THEN
RETURN INPUT_STRING;
END DECRYPT;
END;

사용할 유저에게 실행 권한 부여

기본적으로 DBMS_CRYPTO 패키지 권한 만 부여하면 되지만, 혹시 안 될 경우 두 개의 패키지에 대한 사용 권한을 준다.

grant execute on pkg_crypto to USER;

테스트

패키지가 정상적으로 생성되었는지 테스트

SQL> select sys.pkg_crypto.encrypt ( ‘test’ ) from dual ;

A04B686B118AF67B

SQL> select sys.pkg_crypto.decrypt ( ‘A04B686B118AF67B’ ) from dual ;

test

SQL> create table test_crypto (id int , pwd varchar2(64)) ;

SQL> insert into test_crypto values (1, sys.pkg_crypto.encrypt(‘password1’) ) ;

SQL> insert into test_crypto values (2, sys.pkg_crypto.encrypt(‘password2’) ) ;

SQL> commit ;

SQL> select * from test_crypto ;

1 8A65E0E80532B5FADACA597658B8E8E0

2 8A65E0E80532B5FA6635EBCA6EB4D195

SQL> select id , sys.pkg_crypto.decrypt(pwd) from test_crypto ;

1 password1

2 password2

패키지 소스 암호화 (WRAP)

패키지 바디 부분을 SQL 파일로 저장한 후에, 해당 SQL파일을 오라클의 WRAP 명령을 이용하여 소스를 암호화 한다.

암호화된 소스를 이용하여 패키지를 생성한다.

패키지 바디 부분을 pkg_crypto.sql 파일로 만든 후에, WRAP 명령을 이용하여 소스 암호화를 진행 한다.

..\client_1\BIN>wrap iname=pkg_crypto.sql oname=pkg_crypto.plb

PL/SQL Wrapper: Release 19c.0- Production on Wed Sep 14 12:59:27 2019

Processing pkg_crypto.sql to pkg_crypto.plb

pkg_crypto.plb 파일을 실행 시켜도 되고, 메모장으로 열어서 사용하는 툴에 붙여 넣어서 수행 시켜도 됨.

SQL> select text from dba_source where name = ‘PKG_CRYPTO’;

PACKAGE pkg_crypto

FUNCTION encrypt ( input_string IN VARCHAR2 ) RETURN RAW;

FUNCTION decrypt ( input_string IN VARCHAR2 ) RETURN VARCHAR2;

END pkg_crypto;

— pkg_crypto.plb

PACKAGE BODY pkg_crypto wrapped

a000000

34e

abcd

569 237

ohqkYyNGzuK44ZjD/dc7zE6LBD4wg5VeACDWfC/NR2SlOfdRWqio8BgJ+rTLDeZ0KhuUtwfL

pHsh5UXSmrE5hH7n/MYlulcFuxz+/3JEolt108hdUznBsR865PlC+TBOESlCZ+k6dfP/0AHl

m7ZdffubfOeMEW+6xue2jQP8dS8cEnnuvOBaUG77FS9kmfvhlxjyhQm4lwlnk65byQ4mpm7j

GILd7l4DK2J9rhLoBmcn9GupCftFAI05Ew3eYFuXMfS4NUsCqzdZDLE1ssWoPgFg+nUzSFvF

96FFaMLpCoAYcU9Tq8HdBzHF2Ns/HrqqvzJZx/uPlmo9e4NoSHOonHhr6S2BSS6PXVSXOfeW

dCG489cqwDaf/h2Nxx6WEONFUFoTb7aNG1pvW8Ng5SfDjDYeq4D+lAQA3onCzKeT6/y2hsuA

IJnvdL8FCN3PdeQlz1W8zWexpkBqPyhKvj+RXuuxlTfRnM2voRmdrRch6sSNazdobfnUJOL4

tbN6GdLtMV5ecSORI6U3gkDu9v0wuFojLTjBxLTrCgbIq5AI1x9AOIKC

ORACLE DB dump backup and recovery (export / import)

오라클에서 데이터베이스나 스키마 별로 데이터를 저장, 백업하거나

다시 복구해야할때, DB Dump를 떠논다고 말한다.

Dump는 간단한 명령어로 쉽게 떠지지만,

반드시 Dump를 저장할 디렉토리를 만들어 지정해줘야한다.

1. 오라클시스템상 디렉토리 만들기.

CREATE DIRECTORY datadump AS 'C:\dump';
GRANT READ, WRITE ON DIRECTORY datadump TO SYSTEM;
GRANT CREATE ANY DIRECTORY TO SYSTEM;

SELECT * FROM DBA_DIRECTORIES;

1. C밑에 물리적인 폴더를 생성하고, (dump라는 이름으로 생성했다)

이후 “CREATE DIRECTORY datadump AS ‘C:\dump'”를 수행하여

데이터 베이스 상에 논리적 디렉토리를 지정해준다. (datadump라는 폴더를 만들었다.)

2. 이후 읽고 쓰기 권한을 부여해주어야 한다.

GRANT READ, WRITE ON DIRECTORY datadump TO SYSTEM
;
GRANT CREATE ANY DIRECTORY TO SYSTEM;

3. 확인

SELECT * FROM DBA_DIRECTORIES; 를 수행하면 만들어졌음을 확인할수있다.

로컬이라면, CMD에서 sqlplus 명령어로 dba로 접속하여 진행해도 무방하다.

cmd> sqlplus / as sysdba

2. export / emport 시작

CMD창에서 반출 명령어를 입력한다. (접속 스키마 id/pw , ip:포트, db풀, 만든 directory정보 확인)

-- expdp 반출
--db full
expdp userid=ECUBE2/ECUBE2@//127.0.0.1:1521/OBZMETA dumpfile= OBZMETA.dmp directory=datadump  full=y logfile=fullexp.log

*localhost 경우 @//127.0.0.1:1521/OBZMETA 생략 가능
--schema (ECUBE2, ECUBEEBM2)
expdp userid=ECUBE2/ECUBE2@//127.0.0.1:1521/OBZMETA  dumpfile=ECUBE2.dmp directory=datadump  schemas=ECUBE2 logfile=fullimp.log

expdp userid=ECUBEEBM2/ECUBEEBM2@//127.0.0.1:1521/OBZMETA  dumpfile=ECUBEEBM2.dmp directory=datadump  schemas=ECUBEEBM2 logfile=fullimp.log

--반입
--db full
expdp userid=ECUBE2/ECUBE2@//127.0.0.1:1521/OBZMETA dumpfile= OBZMETA.dmp directory=datadump  full=y logfile=fullexp.log


--schema (ECUBE2, ECUBEEBM2)
impdp userid=ECUBE2/ECUBE2@//127.0.0.1:1521/OBZMETA  dumpfile=ECUBE2.dmp directory=datadump  schemas=ECUBE2 logfile=fullimp.log

impdp userid=ECUBEEBM2/ECUBEEBM2@//127.0.0.1:1521/OBZMETA  dumpfile=ECUBEEBM2.dmp directory=datadump  schemas=ECUBEEBM2 logfile=fullimp.log