SQL Server 2016 – Dynamic Data Masking

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- master database
USE master
GO

CREATE TABLE PhoneTable (
   cid INT IDENTITY(1,1) NOT NULL,
   PhoneNumber CHAR(12)
)

INSERT INTO PhoneTable
VALUES ('238-555-0197'), ('664-555-0112')

-- unmasked for user with better than SELECT permissions on the table
SELECT * FROM PhoneTable

-- now alter table to mask the PhoneNumber column
ALTER TABLE PhoneTable ALTER COLUMN PhoneNumber CHAR(12) MASKED WITH (FUNCTION = 'partial(4, "xxx-xxxx", 0)') NULL;
--ALTER TABLE PhoneTable ALTER COLUMN PhoneNumber char(12) MASKED WITH (FUNCTION = 'default()') NULL;

-- create a user without login who has only SELECT on the table
  CREATE USER user1 WITHOUT LOGIN;
  GRANT SELECT ON OBJECT::dbo.PhoneTable TO user1

-- masked for user who has only READ access on the table
EXECUTE AS USER = 'user1';
  SELECT *
  FROM PhoneTable;
  REVERT;

-- verify the DDM applied in the entire database
SELECT OBJECT_NAME(object_id) TableName,
    name ColumnName,
    masking_function MaskFunction
  FROM sys.masked_columns
  ORDER BY TableName, ColumnName;

-- cleanup
DROP TABLE PhoneTable
DROP USER user1

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>