MS-SQL 2005이상에서 사용이 가능한 데이타 암호화 복호화 방법입니다.

요즘 워낙 암호화가 많이 강조가 되다 보니 activeX형태로 나온것도 많고 다양한 소스들도 많이 나와 있습니다

그러나 DB자체적으로 암호화 복호화가 가능하다는것을 아는 사람은 그렇게 많지 않은것 같습니다

그래서 정리를 해봤습니다

AES 128비트 암호화 방법입니다

그외 방법들도 있지만 나머지는 공부하시면서 찾으시면 쉽게 찾을수 있을것입니다...

 

 

------------------------------------------------------------------------
--1.마스터 키 생성
------------------------------------------------------------------------
Create Master Key Encryption By PassWord = 'Pwd_Create_Master' -- ⓐ 데이터베이스의 마스터키를 암호화하는데 사용되는 암호

 

-- 마스터 키 백업
Open Master Key Decryption By PassWord = 'Pwd_Create_Master';


BackUp Master Key To File = 'C:\MasterKey'       -- 생성된 파일을 잘 보관해 두어야 한다.(★)
Encryption By PassWord    = 'Pwd_Backup_Master'  -- ⓑ 파일의 마스터키를 암호화하는데 사용되는 암호입니다.

 

-- 마스터 키 삭제
Drop Master Key

 

-- 마스터 키 복원
Restore Master Key From File = 'C:\MasterKey'        -- 백업해 두었던 마스터 키 파일
Decryption By PassWord       = 'Pwd_Backup_Master'   -- ⓑ
Encryption By PassWord       = 'Pwd_Create_Master'   -- ⓐ

 

-- 마스터키조회
Select * From sys.symmetric_keys

------------------------------------------------------------------------
--2.인증서 생성
------------------------------------------------------------------------
-- 인증서초기생성
Create Certificate Cert_JUMIN                      -- 인증서이름
       Encryption By PassWord = 'Pwd_Create_Cert'  -- ⓒ인증서비밀번호
       With Subject = '주민번호인증서',            -- 인증서제목
       Start_Date   = '01/01/2011',                --시작일
       Expiry_Date  = '12/31/2050'                 -- 인증서유효기간('mm/dd/yyyy')

 

-- Drop Certificate Cert_JUMIN           -- 인증서이름

 

-- 인증서조회
Select * From sys.certificates


-- 인증서백업
BackUp Certificate Cert_JUMIN To File = 'C:\CERT'  -- 인증서, 생성된 파일을 잘 보관해 두어야 한다.(★)
       With Private Key  (
       File = 'C:\PRI' ,                           -- 개인키, 생성된 파일을 잘 보관해 두어야 한다.(★)
       Encryption By PassWord = 'Pwd_Backup_Cert', -- ⓓ키를 백업파일에 작성하기 전에 개인키를 암호화하는데 사용되는 암호
        Decryption By PassWord = 'Pwd_Create_Cert' -- ⓒ키를 백업하기전에 개인키의 암호를 해독하는데 사용되는 암호입니다
        )
   
-- 인증서삭제
Drop Certificate Cert_JUMIN

 

-- 인증서복구
Create Certificate Cert_JUMIN
       From File = 'C:\CERT'
       With Private Key (
       File = 'C:\PRI',
       Encryption By PassWord = 'Pwd_Create_Cert', -- ⓒ개인키를 암호화 하는데 사용되는 암호를 지정합니다
       Decryption By PassWord = 'Pwd_Backup_Cert' -- ⓓ파일에서 검색한 개인키의 암호를 해독하는데 필요한 암호를 지정합니다
 );

 

------------------------------------------------------------------------
--3. 대칭키 생성
------------------------------------------------------------------------
Create Symmetric Key Sym_KEY                               -- 대칭키이름
       With ALGORITHM = AES_128,                           -- 암호알골리즘 선택
       Identity_Value = 'Data encryption key 2012-08-28',  -- 사용자 형식에 맞게 수정
       Key_Source     = '예술경영 SYMMETRIC KEY'         -- 사용자 형식에 맞게 수정
       Encryption By Certificate Cert_JUMIN                -- 대칭키를 암호화하는데 사용되는 인증서 이름을 지정합니다

--마스트키와 인증서를 복구한 후 대칭키가 삭제되었을시에는
--이전 생성된 대칭키와 동일하게 다시 생성하면 된다
--이때 ALGORITHM,Identity_Value,Key_Source 가 반드시 존재해야 하며 값은 대소문자를 구분한다

-- 대칭키조회
Select * From sys.symmetric_keys

drop symmetric key Sym_KEY

------------------------------------------------------------------------
--4. 대칭키 오픈
------------------------------------------------------------------------
If Exists (Select * From sys.openkeys  Where Key_Name = 'Sym_KEY')
   Begin
       Print 'Already Open'
   End
ELSE
   Begin
      Open Symmetric Key Sym_KEY                 -- 대칭키이름
           Decryption By Certificate Cert_JUMIN  -- 인증서이름
           With PassWord = 'Pwd_Create_Cert'     -- ⓒ인증서비밀번호(대/소분자 같아야 함., 한개일때는 없어도 됨)


      Print 'Open'
   End

 

--암/복호화 할 때 항상 먼저 열어야 함

Select * From sys.symmetric_keys

 

------------------------------------------------------------------------
--5. 암/복호화
------------------------------------------------------------------------
-- 테이블 생성
Create Table 고객( 고객번호 Int Identity Primary Key,  고객이름 VarBinary(128) Not Null,
                   주민번호 VarBinary(128)  Not Null )  --  대칭키가Open되어있지 않으면 NULL값으로 대체된다.
GO

 

-- 암호화
Insert Into 고객(고객이름, 주민번호) Values(EncryptByKey(Key_Guid('Sym_KEY'), '가치노을'), EncryptByKey(Key_Guid('Sym_KEY'), '9912311234567'))

GO


--복호화
Select Convert(VarChar, DecryptbyKey(주민번호)), * From 고객

 

Select * From 고객
GO

 

------------------------------------------------------------------------
--6. 대칭키 Close
------------------------------------------------------------------------
If Exists (Select * From sys.openkeys  Where Key_Name = 'Sym_KEY')
   Begin
       Close Symmetric Key Sym_KEY
       Print 'Close'
   End
Else
   Begin
       Print 'Already Close'
   End

 

------------------------------------------------------------------------
--7. 해당 세션에서 여러개의 대칭키를 사용하고 이를 모두 닫아야 한다면
------------------------------------------------------------------------
Close All Symmetric Keys

------------------------------------------------------------------------

'개발팁( MS-SQL )' 카테고리의 다른 글

테이블에 대한 필드목록가져오기  (0) 2013.07.02
배열에 대한 처리  (0) 2013.07.02
top


/*****************************************************************************
  *  프로시저  명 : PB_TABLE_FIELD_FC
  *  단위  업무명 : 테이블에 대한 필드목록
  *  입   력   값 : 게시판 코드
  *  출   력   값 : 필드목록
  *  설        명 : 테이블에 대한 필드목록
  *  작   성   자 : 

  *  작   성   일 : 2011-06-14
  *  수 정  사 항 : 홍길동– 일자
  *                 수정내용-
*****************************************************************************/
CREATE FUNCTION [dbo].[PB_TABLE_FIELD_FC](
 @strTable  nvarchar(20)
)
RETURNS nvarchar(4000)
AS
BEGIN
 declare @strField nvarchar(4000)
 
 set @strField = ''

 select @strField= @strField + ',' + COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
 where TABLE_NAME = @strTable
 AND COLUMN_NAME not in(
  SELECT [name] FROM syscolumns
  WHERE [id] IN (SELECT [id] FROM sysobjects WHERE [name] = @strTable )
    AND colid IN (SELECT SIK.colid FROM sysindexkeys SIK JOIN sysobjects SO ON SIK.[id] = SO.[id] 
         WHERE SIK.indid = 1 AND SO.[name] = @strTable )
 )

 if @strField <> ''
 begin
  set @strField = substring(@strField,2,len(@strField))
 end
 RETURN RTRIM(LTRIM(@strField))

END

top


/*****************************************************************************
  *  프로시저  명 : PB_GET_ARRAY_ITEM_FC
  *  단위  업무명 : 배열에 대한 처리
  *  입   력   값 : 배열처리문장,구분자,위치
  *  출   력   값 : 처리문장
  *  설        명 : 배열에 대한 처리
  *  작   성   자 : 
  *  작   성   일 : 2011-06-14
  *  수 정  사 항 : 홍길동– 일자
  *                 수정내용-
*****************************************************************************/
CREATE FUNCTION [dbo].[PB_GET_ARRAY_ITEM_FC](@ArrStr VARCHAR(8000), @DIV VARCHAR(12), @Loc INT)
RETURNS VARCHAR(8000) AS
BEGIN
       -- 구분자로구분지어진스트링을어레이처럼사용하게하는함수,
       -- 문자열자체에문제가있는것을잡아주지는않는다.
       -- Loc는1부터시작
       -- Loc이개수보다크면$ERROR$ 반환
 
       DECLARE @arr VARCHAR(8000)  -- 작업스트링변수
       DECLARE @Tmp VARCHAR(8000) -- 작업스트링변수
       DECLARE @RSLT VARCHAR(8000)      -- 결과값
       DECLARE @i                 INT          -- 임시위치변수
       DECLARE @UpArray    INT          -- 배열의크기
       DECLARE @CNT        INT          -- 임시카운터
       DECLARE @DIV_LEN    TINYINT     
 
       SET @DIV_LEN = LEN(@DIV)
 
       IF @ArrStr <> '' BEGIN
 
             SET @RSLT = '$ERROR$'
              SET @arr = @DIV + @ArrStr
 
             SET @Tmp = @ArrStr
             SET @UpArray = ((LEN(@Tmp) - LEN(REPLACE(@Tmp, @DIV, ''))) / @DIV_LEN ) + 1
 
             IF @Loc = -1 BEGIN
                    SET @RSLT = CAST(@UpArray AS VARCHAR(15))
                    RETURN @RSLT
             END
 
             IF (@Loc > @UpArray)OR (@Loc < 1) BEGIN
                    SET @RSLT = '$ERROR$'
                    RETURN @RSLT
             END
 
             IF (@Loc > 0) AND (@Loc <= @UpArray) BEGIN
                    SET @i = 0
                    SET @CNT = 0
 
                    WHILE @arr <> '' BEGIN
                           IF @CNT = @Loc BEGIN
                                 IF CHARINDEX(@DIV, @arr, @i) <> 0 BEGIN
                                         SET @RSLT = SUBSTRING(@arr, @i, CHARINDEX(@DIV, @arr, @i) - @i)
                                 END
                                 ELSE BEGIN
                                        SET @RSLT = SUBSTRING(@arr, @i, LEN(@arr) - (@i - 1))
                                 END
                                 BREAK
                           END         
                          
                           SET @i = (CHARINDEX(@DIV, @arr, @i) + @DIV_LEN)
                   
                           IF @i > 0 BEGIN                 
                                 SET @CNT = @CNT + 1       
                           END
                           ELSE BEGIN  
                                 BREAK
                           END
                    END
             END
       END
       ELSE BEGIN
             IF @Loc = -1
                    SET @RSLT = '0'
             ELSE
                    SET @RSLT = '@ERROR$'
       END
 
       RETURN @RSLT
 
END

 

top