카테고리1프로그래밍
카테고리2DB
제목오라클 암,복호화
작성자고성훈
작성일2022-10-13 13:05:03
[DBMS_CRYPTO 사용 권한 부여]
GRANT EXECUTE ON DBMS_CRYPTO TO 아이디;


[패키지 생성]
CREATE OR REPLACE PACKAGE scspkg_encdec
IS
FUNCTION enc(
input_string IN VARCHAR2
)RETURN RAW;

FUNCTION dec(
input_string IN RAW
)RETURN VARCHAR2;
END scspkg_encdec;

CREATE OR REPLACE PACKAGE BODY scspkg_encdec
IS
FUNCTION enc(
input_string IN VARCHAR2
)RETURN RAW
IS
encrypted_raw RAW (2000);
key_bytes_raw RAW (32);
encryption_type PLS_INTEGER :=
DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678123456781234567812345678', 'AL32UTF8');
encrypted_raw := DBMS_CRYPTO.ENCRYPT(
src=>UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8')
, typ=>encryption_type
, KEY=>key_bytes_raw
);
RETURN encrypted_raw;
END enc;



FUNCTION dec(
input_string IN RAW
)RETURN VARCHAR2
IS
decrypted_raw RAW (2000);
key_bytes_raw RAW (32);
output_string VARCHAR2 (200);
encryption_type PLS_INTEGER :=
DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678123456781234567812345678', 'AL32UTF8');
decrypted_raw := DBMS_CRYPTO.DECRYPT(
src=>input_string
, typ=>encryption_type
, KEY=>key_bytes_raw
);
output_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');
RETURN output_string;
END dec;

END scspkg_encdec;

CREATE OR REPLACE FUNCTION FN_GET_FILE_NO(
PARAM_DATA_NO IN NUMBER
)RETURN NUMBER
IS
RETURN_FILE_NO NUMBER;
BEGIN
SELECT FILE_NO INTO RETURN_FILE_NO
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY FILE_NO ASC) AS RNUM
, FILE_NO
FROM SCS_BOARD_FILE
WHERE DATA_NO = PARAM_DATA_NO
) A
WHERE RNUM = 1;

RETURN RETURN_FILE_NO;
END FN_GET_FILE_NO;


CREATE OR REPLACE FUNCTION FN_GET_ORI_FILE_NAME(
PARAM_DATA_NO IN NUMBER
)RETURN VARCHAR2
IS
RETURN_ORI_FILE_NAME VARCHAR2(255);
BEGIN
SELECT
ORI_FILE_NAME INTO RETURN_ORI_FILE_NAME
FROM(
SELECT
ROW_NUMBER() OVER(ORDER BY FILE_NO ASC) AS RNUM,
ORI_FILE_NAME
FROM
SCS_BOARD_FILE
WHERE
DATA_NO = PARAM_DATA_NO
) A
WHERE RNUM = 1;

RETURN RETURN_ORI_FILE_NAME;
END FN_GET_ORI_FILE_NAME;



SELECT scspkg_encdec.enc('암호화') FROM DUAL;
SELECT scspkg_encdec.dec('복호화') FROM DUAL;
수정목록