[DB Security] 공짜로 DB 암호화 하는 방법


타 사이트에서 사용 했던 오라클 DB의 기본 제공 암호화 함수를 이용해서

추가 암호화 솔루션 구매 없이 개인정보 데이터 를  컬럼 단위 암호화 하는  방법 입니다.

양방향 암호화

< 요약 >

아래 방식으로 키 테이블과 암호화 / 복호화 함수를 만든 다음 개인정보 데이터를

입력 , 수정 , 조회 하는 부분을 모두 변경 ( SQL , SP , java 등등 ) 해야 합니다.

 

< 방식설명 >

1.    Key 테이블을 일단 만들고 key 데이터를 한건 넣습니다. ( 임의로 )

2.    Encode param 함수 와 decode param 함수파일을 보면 오라클의 기본 함수를 이용합니다.

3.    Encode param 함수는 암호화 할 컬럼 데이터를 입력 또는 수정하는 쿼리에 사용합니다.

4.    Decode param 함수는 암호화 한 컬럼 데이터를 조회 하는데 사용 하는 쿼리에 사용 합니다.

 

< 주의 >

만약 사내 보안 규칙상 키 테이블을 같은 머신에 놓으면 안되는 경우 디비링크를 써서 다른 DB에 넣고 조회 하게 됩니다.
(주의 : 트래픽이 몰릴경우 성능 보장이 안됩니다. )

 

1. Key 테이블 생성 및 Key Data Insert

CREATE TABLE SY_SECURE_KEY
(
KEY              LONG RAW
)
TABLESPACE USERS
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
LOGGING ;

 

INSERT INTO SY_SECURE_KEY VALUES (‘4E5065664D73337546314………..0000 임의 키값 입력 ’) ;

 

2. 암호화 함수 생성

CREATE OR REPLACE function encodeParam
(
p_in    in varchar2
)
return varchar2 is
l_return_val varchar2(2048) ;
l_enc_val raw (2048);
l_mod     number := DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_ECB
+ DBMS_CRYPTO.PAD_PKCS5;
p_key raw (65) ;
begin

if p_in is not null and (isEncoded(p_in)=0) then

select key into p_key from sy_secure_key ;

l_enc_val := DBMS_CRYPTO.encrypt( UTL_RAW.cast_to_raw(p_in), l_mod, p_key );
l_return_val := UTL_RAW.cast_to_varchar2( utl_encode.base64_encode(l_enc_val) ) ;

else
l_return_val := p_in ;
end if ;

return l_return_val;

exception

when others then
l_return_val := p_in ;

return l_return_val;
end;
/

 

3. 복호화 암호 함수

CREATE OR REPLACE function decodeParam
(
p_in    in varchar2
)
return varchar2 is
l_return_val varchar2(2048) ;
l_dec_val raw (2048);
l_mod     number := DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_ECB
+ DBMS_CRYPTO.PAD_PKCS5;
p_key raw (65) ;
begin

if p_in is not null and (isEncoded(p_in)>0) then

select key into p_key from sy_secure_key ;

l_dec_val := utl_encode.base64_decode(utl_raw.cast_to_raw(p_in)) ;
l_dec_val := DBMS_CRYPTO.decrypt( l_dec_val, l_mod, p_key );
l_return_val := UTL_RAW.cast_to_varchar2( l_dec_val) ;

else
l_return_val := p_in ;
end if ;

return l_return_val;

exception

when others then
l_return_val := p_in ;

return l_return_val;
end;
/

 

 

4. 암호화 테이블 데이터 조회 시 ( 복호화 과정 )

select  decodeParam(COL1), decodeParam(COL2)
from 개인정보테이블
where ID=’haisins’;

 

5. 암호화 테이블 데이터 추가 시 ( 암호화 과정 )

Insert into 개인정보테이블  values (encodeParam(‘주민번호’) ;

 

 

*.참고

DBMS_CRYPTO Encryption Algorithms

Name Description

ENCRYPT_DES

Data Encryption Standard. Block cipher. Uses key length of 56 bits.

ENCRYPT_3DES_2KEY

Data Encryption Standard. Block cipher. Operates on a block 3 times with 2 keys. Effective key length of 112 bits.

ENCRYPT_3DES

Data Encryption Standard. Block cipher. Operates on a block 3 times.

ENCRYPT_AES128

Advanced Encryption Standard. Block cipher. Uses 128-bit key size.

ENCRYPT_AES192

Advanced Encryption Standard. Block cipher. Uses 192-bit key size.

ENCRYPT_AES256

Advanced Encryption Standard. Block cipher. Uses 256-bit key size.

ENCRYPT_RC4

Stream cipher. Uses a secret, randomly generated key unique to each session.

https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_crypto.htm


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

[DB Security] 공짜로 DB 암호화 하는 방법”의 29개의 댓글

  • 2018-08-03 5:23 오후
    Permalink

    That is a very good tip particularly to those fresh to the blogosphere.
    Brief but very accurate information… Many thanks for sharing this one.
    A must read post!

    댓글달기
  • 2018-08-18 12:47 오전
    Permalink

    Within the past a long period, outstanding majority of organizations have moved their individuals into a cubicle environment.

    댓글달기
  • 2018-08-18 9:44 오전
    Permalink

    Its like you read my mind! You seem to understand a lot approximately this, like you wrote the book in it or something.
    I feel that you simply can do with a few
    percent to power the message house a bit, but instead of that, this is excellent blog.
    A fantastic read. I will certainly be back.

    댓글달기
  • 2018-08-25 2:51 오후
    Permalink

    Hi, I do think this is an excellent blog. I stumbledupon it 😉 I will revisit
    yet again since I book-marked it. Money and freedom is the greatest way to change, may
    you be rich and continue to guide other people.

    댓글달기
  • 2018-09-19 11:44 오후
    Permalink

    Thank you for any other wonderful post. The place else could anyone get that type
    of info in such a perfect method of writing? I have a presentation next week, and I’m at the search for such information.

    댓글달기
  • 2018-11-16 1:09 오전
    Permalink

    Hmm is anyone else experiencing problems with the images
    on this blog loading? I’m trying to determine if its
    a problem on my end or if it’s the blog. Any suggestions would be greatly appreciated.

    댓글달기
  • 2018-11-16 10:34 오전
    Permalink

    I’m gone to inform my little brother, that he should also pay a visit this website on regular basis to get updated from newest news.

    댓글달기
  • 2018-11-24 12:48 오후
    Permalink

    That’s the following most vital thing we are able to
    do. At greatest, fights about infrastructure are sideshows;
    at worst, distractions from issues that actually matter in the struggle towards air pollution and world warming.

    댓글달기
  • 2018-12-08 2:26 오후
    Permalink

    May I simply just say what a relief to uncover someone who really knows what they’re talking about on the internet.

    You actually realize how to bring a problem to light and make it important.
    A lot more people really need to read this and understand this side of the story.
    It’s surprising you’re not more popular since you certainly have the gift.

    댓글달기
  • 2019-02-01 8:02 오후
    Permalink

    It is common to discover the ornamental painting and sculptures with shapes depicting a unique blend
    of different components from the artist’s religious, physical and cultural background.
    After the Bourbon Restoration, as the trial participant of Louis XVI, David was deprived of his civil right and property, and was forced to leave his homeland to in Brussels where David also completed many works, and finally died in a very strange
    land. The memorial also serves enormous events all
    areas of the globe.

    댓글달기
  • 2019-02-01 10:55 오후
    Permalink

    Leonardo lived in the own measured rhythm, and always cared about
    the caliber of his paintings completely ignoring some time it
    will require to perform the task. in April 22, 1560,
    he explained:” Your Majesty, you’re invincible and contain the world in awe. Matisse also became the king of the Fauvism and was famous inside the art circle.

    댓글달기
  • 2019-02-02 5:59 오전
    Permalink

    It is common to find the ornamental painting and sculptures with shapes depicting a unique blend of different aspects of the artist’s religious, physical and
    cultural background. A vector path, no matter what
    the twists and turns are, is often more elastic and scalable.
    It is maybe the most worldwide of mediums, in the its practice along with its range.

    댓글달기
  • 2019-03-10 4:37 오전
    Permalink

    Right here is the right webpage for anyone who really wants
    to find out about this topic. You know a whole
    lot its almost hard to argue with you (not that I actually will need
    to…HaHa). You definitely put a brand new spin on a subject which
    has been discussed for many years. Excellent stuff, just great!

    댓글달기
  • 2019-07-15 3:03 오후
    Permalink

    Thanks for finally talking about >[DB Security] 공짜로 DB 암호화 하는 방법 – DBA의
    정석 <Liked it!

    댓글달기
  • 2019-07-18 12:56 오전
    Permalink

    Checklist summing up what you should do to
    ensure freelancing success:o Join a couple of freelancing sites at maximum and focus
    their terms carefully. One way to make sure you’re looking at
    a real opportunity is to seek advice from BBB or perhaps the FTC and discover if you discover any records or complaints.
    It is claimed these jobs are one of the most successful ways
    of creating money online.

    댓글달기
  • 2019-07-20 5:45 오전
    Permalink

    My family members every time say that I am
    wasting my time here at net, except I know
    I am getting experience everyday by reading thes fastidious posts.

    댓글달기

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다