DBA의 정석

DB Admin Knowhow

DB 보안 & 암호화

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

Byhaisins

1월 27, 2018

타 사이트에서 사용 했던 오라클 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

<span style="font-size: 8.7pt;">ENCRYPT_DES</span>

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

<span style="font-size: 8.7pt;">ENCRYPT_3DES_2KEY</span>

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

<span style="font-size: 8.7pt;">ENCRYPT_3DES</span>

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.

<span style="font-size: 8.7pt;">ENCRYPT_RC4</span>

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

By haisins

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

Related Post

DB 보안 & 암호화

실시간으로 TCP 와 UDP 포트를 감시하는 방법

11월 4, 2021 haisins
23C+19C+18C DB 보안 & 암호화

TDE in Oracle CDB

7월 1, 2018 haisins
23C+19C+18C DB 보안 & 암호화

마스킹 on Oracle 12c

7월 1, 2018 haisins
37 thoughts on “[DB Security] 공짜로 DB 암호화 하는 방법”

  4. 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!

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

  9. 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.

  10. 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.

  11. 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.

  15. 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.

  16. 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.

  18. 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.

  19. 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.

  21. 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.

  22. 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.

  23. 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.

  25. 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!

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

  28. 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.

  29. 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.

  30. I seriously love your site.. Pleasant colors & theme. Did you develop this
    web site yourself? Please reply back as I’m trying to create my own blog and want to know where you got this from or just
    what the theme is called. Thank you!

  31. One can don’t ever consider becoming successful in his work
    if he does not possess these qualities. whether
    you have instruction, a higher degree, lives in the
    foreign country, regardless of situation. It is considered these effort is one of the most successful means of creating
    money online.

  32. Spot on with this write-up, I absolutely feel this amazing site needs much more attention. I’ll probably be back again to read through more,
    thanks for the advice!

  33. It’s very easy to find out any matter on web as compared to books, as I found this paragraph at this web page.

  34. Hi I am so delighted I found your site, I really found you by accident,
    while I was browsing on Askjeeve for something else, Nonetheless I am here now and would just like to say kudos for a fantastic post and a all round entertaining blog (I also love the theme/design), I don’t have time to read it all
    at the minute but I have book-marked it and also included your RSS feeds,
    so when I have time I will be back to read a lot more,
    Please do keep up the great job.

  35. Greetings from Ohio! I’m bored to death at work so I decided to browse your site on my
    iphone during lunch break. I really like the info you present here and can’t
    wait to take a look when I get home. I’m surprised at
    how quick your blog loaded on my phone .. I’m not even using WIFI, just 3G ..
    Anyhow, good blog!

Comments are closed.

You missed

Oracle News

[NEWS] 오라클, ‘오라클 데이터베이스 23c 개발자용 무료 버전’ 공개

5월 8, 2023
Oracle DB Admin

Oracle Block change tracking 기능

5월 8, 2023
Oracle Up,Mig

DB Migration Using TTS

4월 20, 2023
DB 설치 & 구축

Windows 용 Oracle Database 19c 설치 필요 조건

4월 19, 2023