Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #####################################################################################
- KONA_USER, KONA_SQL_DATASOURCE 두개 테이블의 password 컬럼을 암호화 적용 작업절차
- - KONA_USER 테이블의 패스워드는 이미 암호화되어 처리되고 있기때문에 제외
- - 오라클 FUNCTION 예시
- 암호화 : XX1.ENC_VARCHAR2_INS(컬럼명,10,'SSNF')
- 복호화 : XX1.DEC_VARCHAR2_SEL(컬럼명,10,'SSNF')
- #####################################################################################
- 1. 암호화 오라클 함수 TOGA 계정으로 접근여부
- ex) grant EXECUTE, DEBUG ON "SSO_DEPT"."CRYPTO_AES256" TO "TOGA_SSGDPT_4_0" ;
- 2. KONA_SQL_DATASOURCE 테이블 구조 확인필요(4.0 ORACLE기준)
- COLUMN_NAME | DATA_TYPE | NULLABLE | DATA_DEFAULT | COLUMN_ID
- -------------------|--------------------|-----------|--------------|------------
- SQLDATASOURCE_ID | VARCHAR2(5 BYTE) | No | NULL | 1
- NAME | VARCHAR2(60 BYTE) | No | NULL | 2
- TYPE | VARCHAR2(2 BYTE) | Yes | NULL | 3
- DRIVER_CLASSNAME | VARCHAR2(60 BYTE) | Yes | NULL | 4
- URL | VARCHAR2(500 BYTE) | Yes | NULL | 5
- USERNAME | VARCHAR2(60 BYTE) | Yes | NULL | 6
- PASSWORD | VARCHAR2(60 BYTE) | Yes | NULL | 7
- INITIAL_SIZE | NUMBER(10,0) | Yes | 0 | 8
- MAX_ACTIVE | NUMBER(10,0) | Yes | 10 | 9
- MAX_IDLE | NUMBER(10,0) | Yes | 0 | 10
- MAX_WAIT | NUMBER(10,0) | Yes | -1 | 11
- LOG_ABAND | VARCHAR2(2 BYTE) | Yes | 'N' | 12
- REMOVE_ABAND | VARCHAR2(2 BYTE) | Yes | 'N' | 13
- ABAND_TIMEOUT | NUMBER(10,0) | Yes | 300 | 14
- VALIDATION_QUERY | VARCHAR2(200 BYTE) | Yes | NULL | 15
- 3. kona-engine.jar\com\eyeq\kona\persistence\sqlmaps\resource\JDBCResource.xml 아이큐내부소스와 신세계푸드 운영파일과 비교
- (다르다면 운영파일기반으로 수정)
- 4. 상세 수정
- - selectJDBCResources, selectJDBCResourceByID 수정 : * 를 전체 컬럼으로 수정, XX1.DEC_VARCHAR2_SEL(컬럼명,10,'SSNF') 적용
- SELECT
- SQLDATASOURCE_ID
- , NAME
- , TYPE
- , DRIVER_CLASSNAME
- , URL
- , USERNAME
- , XX1.DEC_VARCHAR2_SEL(PASSWORD,10,'SSNF') AS PASSWORD --수정
- , INITIAL_SIZE
- , MAX_ACTIVE
- , MAX_IDLE
- , MAX_WAIT
- , LOG_ABAND
- , REMOVE_ABAND
- , ABAND_TIMEOUT
- , VALIDATION_QUERY
- FROM KONA_SQL_DATASOURCE--4.0기준
- - insertJDBCResource, updateJDBCResource 수정 : password 컬럼에 XX1.ENC_VARCHAR2_INS(컬럼명,10,'SSNF') 함수 적용
- INSERT INTO KONA_SQL_DATASOURCE
- ( SQLDATASOURCE_ID
- , NAME
- , TYPE
- , DRIVER_CLASSNAME
- , URL
- , USERNAME
- , PASSWORD
- , INITIAL_SIZE
- , MAX_ACTIVE
- , MAX_IDLE
- , MAX_WAIT
- , LOG_ABAND
- , REMOVE_ABAND
- , ABAND_TIMEOUT
- , VALIDATION_QUERY)
- VALUES
- (#id:VARCHAR#
- , #name:VARCHAR#
- , #databaseType.code:VARCHAR#
- , #driverClassName:VARCHAR#
- , #url:VARCHAR#
- , #userName:VARCHAR#
- , XX1.ENC_VARCHAR2_INS(#password:VARCHAR#,10,'SSNF'), --수정
- , #initialSize:NUMERIC#
- , #maxActive:NUMERIC#
- , #maxIdle:NUMERIC#
- , #maxWait:NUMERIC#
- , #logAbandoned:VARCHAR#
- , #removeAbandoned:VARCHAR#
- , #removeAbandonedTimeout:NUMERIC#
- , #validationQuery:VARCHAR#)
- UPDATE KONA_SQL_DATASOURCE SET
- NAME =#name:VARCHAR#,
- TYPE =#databaseType.code:VARCHAR#,
- DRIVER_CLASSNAME =#driverClassName:VARCHAR#,
- URL =#url:VARCHAR#,
- USERNAME =#userName:VARCHAR#,
- PASSWORD =XX1.ENC_VARCHAR2_INS(#password:VARCHAR#,10,'SSNF'), --수정
- INITIAL_SIZE =#initialSize:NUMERIC#,
- MAX_ACTIVE =#maxActive:NUMERIC#,
- MAX_IDLE =#maxIdle:NUMERIC#,
- MAX_WAIT =#maxWait:NUMERIC#,
- LOG_ABAND =#logAbandoned:NUMERIC#,
- REMOVE_ABAND =#removeAbandoned:NUMERIC#,
- ABAND_TIMEOUT =#removeAbandonedTimeout:NUMERIC#,
- VALIDATION_QUERY =#validationQuery:VARCHAR#
- 5. 변경 된 kona-engine.jar 파일을 kona, droplets 양쪽에 적용(필요에따라 JDBCResource.xml 파일만 적용)
- 6. 기존 연결 확인, 수정, 신규등록 테스트
- - SELECT : 개발자서비스 디비연결 통해 등록된 연결 확인
- - INSERT : 신규연결 시 암호회된 데이터 디비저장확인
- - UPDATE : 기존 데이터 수정 확인
- 참고) 사전준비
- : 내부 오라클 디비에 암복호 화 펑션 구현(http://yangyag.tistory.com/303 참고)
- 1. SYSDBA 권한으로 해당 유저에게 권한을 부여한다.
- : CMD 창에서
- sqlplus "/as sysdba"
- 실행
- GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO [유저명];
- GRANT EXECUTE ON DBMS_CRYPTO TO [유저명];
- 2. Head 패키지를 선언
- CREATE OR REPLACE PACKAGE CRYPTO_AES256
- IS
- /********************************************************************************
- encryption
- ********************************************************************************/
- FUNCTION ENC_AES(input_string IN VARCHAR2)
- RETURN VARCHAR2;
- /********************************************************************************
- decryption
- ********************************************************************************/
- FUNCTION DEC_AES(encrypted_raw IN VARCHAR2)
- RETURN VARCHAR2;
- END CRYPTO_AES256;
- 3. BODY 패키지를 선언
- CREATE OR REPLACE PACKAGE BODY CRYPTO_AES256
- IS
- /********************************************************************************
- encryption
- ********************************************************************************/
- FUNCTION ENC_AES(input_string IN VARCHAR2)
- RETURN VARCHAR2
- IS
- return_base256 VARCHAR2(256);
- encrypted_raw RAW (2000); -- encryption raw type date
- key_bytes_raw RAW (32); -- encryption key (32raw => 32byte => 256bit)
- encryption_type PLS_INTEGER := -- encryption
- DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
- BEGIN
- IF input_string IS NOT NULL THEN
- key_bytes_raw := UTL_I18N.STRING_TO_RAW('WKAF3xv7y,d5SZpzT8ftJR).shEQn#%@', 'AL32UTF8');
- encrypted_raw := DBMS_CRYPTO.ENCRYPT ( src => UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8'), typ => encryption_type, KEY => key_bytes_raw );
- -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
- return_base256 := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));
- END IF;
- RETURN return_base256;
- END ENC_AES;
- /********************************************************************************
- decryption
- ********************************************************************************/
- FUNCTION DEC_AES(encrypted_raw IN VARCHAR2)
- RETURN VARCHAR2
- IS
- output_string VARCHAR2 (200);
- decrypted_raw RAW (2000); -- decryption raw type date
- key_bytes_raw RAW (32); -- 256bit decryption key
- encryption_type PLS_INTEGER := -- decryption
- DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
- BEGIN
- IF encrypted_raw IS NOT NULL THEN
- key_bytes_raw := UTL_I18N.STRING_TO_RAW('WKAF3xv7y,d5SZpzT8ftJR).shEQn#%@', 'AL32UTF8');
- decrypted_raw := DBMS_CRYPTO.DECRYPT (
- -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
- src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw)), typ => encryption_type, KEY => key_bytes_raw );
- output_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');
- END IF;
- RETURN output_string;
- END DEC_AES;
- END CRYPTO_AES256;
- 4. 사용법
- -- AES256 암호화
- SELECT CRYPTO_AES256.ENC_AES('컬럼명') FROM 테이블 명;
- -- AES256 복호화
- SELECT CRYPTO_AES256.DEC_AES('복호화 할 컬럼') FROM 테이블 명;
- 5. 생성된 펑션 접근권한추가
- - 계정명 : "TOGA_SSGDPT_4_0"
- grant EXECUTE, DEBUG ON "SSO_DEPT"."CRYPTO_AES256" TO "TOGA_SSGDPT_4_0" ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement