know-how? know-way!!

/*----------------------------------------------
 대칭 암호화 구현
----------------------------------------------*/

-- 1. 테이블 생성
Create Table Employees 
(
 id int primary key
 , name varchar(100)
 , ssn varbinary(128)
)
GO

-- 2. ssn의 암호화를 위한 키 생성
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO
 
CREATE CERTIFICATE Juuun_test1
   WITH SUBJECT = 'Juuun_test1 Social Security Numbers'
GO
 
CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Juuun_test1;
GO

-- 3. 키확인 (해당세션만 허용됨.)
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE Juuun_test1
GO

SELECT * FROM sys.openkeys
GO

-- 4. 데이터 입력
INSERT INTO Employees 
VALUES (101,'Mary',EncryptByKey(Key_GUID('SSN_Key_01'),'7204042030213',1,'101'))

INSERT INTO Employees 
VALUES (102,'Mary',EncryptByKey(Key_GUID('SSN_Key_01'),'7007022043247',1,'102'))
GO

-- 5. ssn 칼럼의 암호화확인
SELECT * FROM Employees
GO

-- 6. 복호화를 위한 뷰생성 및 확인
Create View View_Employees
with encryption
as
Select id,name, convert(varchar(13), DecryptByKey(ssn, 1, Convert(varchar(30),id))) as ssn
From Employees
GO

Select * From View_Employees

-- 7. 키닫기
CLOSE SYMMETRIC KEY SSN_Key_01
GO

Select * From View_Employees