• 로그인

메뉴

카테고리1	카테고리1 선택프로그래밍
카테고리2	카테고리2 선택AICSSDBGitJAVAJAVASCRIPTMCPmysqlNESTJSREMIX도커리액트리액트 네이티브미들웨어브라우저서버스벨트킷스프링스프링부트오라클자바자바스크립트플러터
제목
작성자
[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;

저장뒤로가기