Security

<<<<보안>>>>

사용자가 SQL Server에
연결할 수 있는 방법                                       SQL Server의 인증모드

Window 인증방법           ─────────    Window인증모드
                                         │
                                         └──────                                                 
SQL Server 인증방법      ─────────     혼합모드

보안 -> 서버역할
- sysadmin : 모든 권한 가짐

WITH CREDENTIAL = OS관리자

Administrator
SQL Server(~2005)
: administrators 그룸의 내장된 구성원, 운영체제(OS)관리, SQL(Builtin\Administrators자동생성)서버 관리(sysadmin), 모든 DBO 관리(DB_Owner)

SQL Server(2008)
: administrators 그룸의 내장된 구성원, SQL서버 관리자 등록(sysadmin), 모든 DBO 관리(DB_Owner)
- 노출위험 방지


테이터 암호화

CREATE CERTIFICATE Cert_JUMIN                                         
   ENCRYPTION BY PASSWORD = 'P@ssw0rd'
   WITH SUBJECT = '주민번호 인증서',
   EXPIRY_DATE = '2010/12/31'
GO
CREATE SYMMETRIC KEY JUMIN_KEY                                      
 WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Cert_JUMIN
GO   
OPEN SYMMETRIC KEY JUMIN_KEY                                         
 DECRYPTION BY CERTIFICATE Cert_JUMIN
 WITH PASSWORD = 'P@ssw0rd'
GO 
CREATE TABLE 고객 (고객번호 INT IDENTITY PRIMARY KEY,               
       고객이름 VARCHAR(20) NOT NULL,
       주민번호 VARBINARY(128) )
GO
INSERT INTO 고객(고객이름, 주민번호)                                     
VALUES ( '박태환', ENCRYPTBYKEY(Key_GUID('JUMIN_KEY'), '890927-0000001'))
GO
SELECT * FROM 고객                                                    
GO
SELECT 고객번호, 고객이름,                                             
 CONVERT (VARCHAR, DECRYPTBYKEY(주민번호)) AS '주민번호'
FROM 고객
GO
CREATE TABLE 고객2 (고객번호 INT IDENTITY PRIMARY KEY,             
        고객이름 VARCHAR(20) NOT NULL,
        주민번호 CHAR(14))
GO
INSERT INTO 고객2(고객이름, 주민번호) VALUES ( '박태환', '890927-0000001')
GO
SELECT * FROM 고객2                        
GO
ALTER TABLE 고객2                                                   
 ADD 주민번호암호 VARBINARY(128)
GO 
UPDATE 고객2                                                        
SET 주민번호암호
    = ENCRYPTBYKEY(Key_GUID('JUMIN_KEY'), 주민번호);  -- 암호화
GO
SELECT * FROM 고객2
GO
ALTER TABLE 고객2 DROP COLUMN 주민번호    
EXEC SP_RENAME '고객2.주민번호암호', '주민번호', 'COLUMN'
GO
SELECT * FROM 고객2                                                    
GO
SELECT 고객번호, 고객이름, 주민번호,
 CONVERT (VARCHAR, DECRYPTBYKEY(주민번호)) AS '주민번호'
FROM 고객2
GO
CLOSE SYMMETRIC KEY JUMIN_KEY           
                           
--- 새로운 세션                                                      
SELECT 고객번호, 고객이름,
CONVERT (VARCHAR, DECRYPTBYKEY(주민번호)) AS '주민번호'
FROM 고객2
GO
OPEN SYMMETRIC KEY JUMIN_KEY                                      
 DECRYPTION BY CERTIFICATE Cert_JUMIN   -- 복구
 WITH PASSWORD = 'P@ssw0rd'
GO
SELECT 고객번호, 고객이름,
CONVERT (VARCHAR, DECRYPTBYKEY(주민번호)) AS '주민번호'
FROM 고객2

- 각 필드 암호화