SQL Server 2016 동적 데이터 마스킹

SQL Server 2016 동적 데이터 마스킹

   

• Version : SQL Server 2016

   

SQL Server 2016의 새로운 보안기능인 동적 데이터 마스킹 (Dynamic Data Masking, DDM)에 대해서 알아본다.

   

SQL 데이터베이스 동적 데이터 마스킹에서는 권한이 없는 사용자에 대해 중요한 데이터를 마스킹해 표시함으로써 데이터 노출을 제한한다. 동적 데이터 마스킹을 수행하면 고객이 응용 프로그램 레이어에 대한 영향을 최소화하면서 표시할 중요한 데이터의 양을 지정할 수 있도록 하여 중요한 데이터에 대한 무단 액세스를 방지할 수 있다.

   

동적 데이터 마스킹은 지정된 데이터베이스 필드에 대한 쿼리의 결과 집합에서 중요한 데이터를 숨기는 정책 기반 보안 기능이다. 이때 데이터베이스의 자체는 변경되지 않는다.

   

[동적 데이터 마스킹 사용 권한]

Azure 데이터베이스 관리자, 서버 관리자 또는 보안 관리자 역할이 동적 데이터 마스킹을 구성할 수 있다.

   

[동적 데이터 마스킹 정책]

• 권한 있는 로그인 - SQL 쿼리 결과에서 마스킹되지 않은 데이터를 가져올 수 있는 로그인 집합
• 마스킹 규칙 - 마스킹하도록 지정되는 필드와 사용할 마스킹 기능을 정의하는 규칙 집합이다. 데이터베이스 테이블 이름과 열 이름을 사용하거나 별칭 이름을 사용하여 마스킹하도록 지정되는 필드를 정의할 수 있다.
• 마스킹 기준 - 원본 또는 대상에서 수행할 수 있다. 마스킹은 테이블 이름 및 열 이름을 확인하여 원본 수준에서 구성할 수도 있고 쿼리에 사용되는 별칭을 확인하여 결과 수준에서 구성할 수도 있다. 데이터베이스의 데이터 아키텍처를 잘 알고 있는 경우 모든 쿼리 결과가 표시되지 않도록 제한하려면 원본 마스크 규칙을 사용하는 것이 좋다. 데이터베이스 데이터 아키텍처를 분석하지 않고 쿼리 결과 표시를 제한하려는 경우 또는 다른 원본의 필드를 지정하려는 경우에는 결과 마스크 규칙을 추가할 수 있다.
• 확장된 제한 - 중요한 데이터의 표시를 제한합니다. 이때 일부 응용 프로그램의 기능을 사용하지 못하게 될 수 있다.

   

[마스킹 기능]

마스킹 기능	마스킹 논리
기본값	지정된 필드의 데이터 형식에 따라 모든 데이터를 마스킹한다. l  문자열 데이터 형식(nchar, ntext, nvarchar)의 필드 크기가 8자 미만이면 XXXXXXXX개 이하의 X를 사용한다. l  숫자 데이터 형식(bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real)의 경우 0 값을 사용한다. l  날짜/시간 데이터 형식(date, datetime2, datetime, datetimeoffset, smalldatetime, time)의 경우 현재 시간을 사용한다. l  SQL 변형의 경우 현재 형식의 기본값이 사용된다. l  XML의 경우 문서가 사용된다. l  특수 데이터 형식(타임스탬프 테이블, hierarchyid, GUID, 이진, 이미지, varbinary 공간 형식)의 경우 빈 값을 사용한다.
신용 카드	지정된 필드의 마지막 4자리를 표시하고 신용 카드 형식 접두사로 상수 문자열을 추가하는 마스킹 방법이다. EX) XXXX-XXXX-XXXX-1234
주민 등록 번호	지정된 필드의 마지막 2자리를 표시하고 주민 등록 번호 형식 접두사로 상수 문자열을 추가하는 마스킹 방법이다. EX) XXX-XX-XX12
전자 메일	전자 메일 주소 형식 접두사로 상수 문자열을 사용하여 첫 글자와 도메인을 표시하는 마스킹 방법이다. EX) aXX@XXXX.com
난수	선택한 경계 및 실제 데이터 형식에 따라 난수를 생성하는 마스킹 방법이다. 지정된 경계가 같으면 마스킹 기능으로 상수가 사용된다.
사용자 지정 텍스트	첫 글자와 마지막 글자를 표시하고 가운데에 사용자 지정 안쪽 여백 문자열을 추가하는 마스킹 방법이다. EX) 접두사[안쪽 여백]접미사

   

기능이 어떻게 작동하는지 살펴보기 위해 간단히 테스트 환경을 만들어 본다.

USE tempdb; GO SELECT TOP (10) ID = IDENTITY(INT, 1, 1), FirstName = RIGHT(o.name, 8), LastName = LEFT(o.name, 12), Email = LEFT(o.name, 9) + '@' + RIGHT(o.name, 11) + '.net', SSN = STUFF(STUFF(RIGHT('000000000' + RTRIM(ABS(CHECKSUM(NEWID()))),9),4,0,'-'),7,0,'-'), BirthDate = DATEADD(DAY, -ABS(CHECKSUM(NEWID())%10000), o.modify_date) INTO dbo.DDM FROM sys.all_objects AS o ORDER BY NEWID();     select * from DDM

   

 

DDM 기능을 활성화 하기 위해 TF209, 219를 활성화 한다.

DBCC TRACEON(209,219,-1)

   

일반(비 시스템 관리자) 사용자로부터 데이터의 개인 정보를 보호하기 위해 여러 열 중 일부에 마스크를 추가할 수 있다.

ALTER TABLE dbo.DDM ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION = 'partial(1, "XXXXX", 0)'); -- show only the first character of the first name     ALTER TABLE dbo.DDM ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2, "XXXXXXXX", 1)'); -- show the first two characters and the last character of the last name     ALTER TABLE dbo.DDM ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()'); -- all addresses will show as nXXX@XXXX.com     ALTER TABLE dbo.DDM ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'partial(1,"XX-XX-XXX",1)'); -- SSNs will become nXX-XX-XXXn     ALTER TABLE dbo.DDM ALTER COLUMN BirthDate ADD MASKED WITH (FUNCTION = 'default()'); -- all Birthdates will show as 2000-01-01

   

테이블을 조회해 보면 변경되지 않는 모습으로 나타난다.(현재 나는 관리자 계정이기 때문이다.)

   

일반 사용자 계정을 테스트하기 위해 blat라는 계정을 생성하고 DDM 테이블에 조회 권한을 부여한다.

CREATE USER blat WITHOUT LOGIN;     GRANT SELECT ON dbo.DDM TO blat;

   

Blat 계정으로 DDM 데이터를 조회해 보면 마스킹 된 것을 확인할 수 있다.

EXECUTE AS USER = N'blat'; GO     SELECT * FROM dbo.DDM; GO     REVERT;

   

   

   

[참고자료]

• Dynamic Data Masking : https://msdn.microsoft.com/library/mt130841(sql.130).aspx
• https://www.mssqltips.com/sqlservertip/3679/use-dynamic-data-masking-in-sql-server-2016-ctp-21/
• SQL 데이터베이스 동적 마스킹 시작(Azure) :

  https://azure.microsoft.com/ko-kr/documentation/articles/sql-database-dynamic-data-masking-get-started/

   

   

   

2015-07-20 / 강성욱 / http://sqlmvp.kr