As we stated that dynamic data masking is
one of many new security features coming in SQL Server 2016 to protect your sensitive
data. Dynamic Data masking is nothing more than a new security feature to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed.
DDM is used to create a new representation of data with random characters or data that are structurally similar to original data. It is normally done for the protection of our sensitive data. This functional term is also known as data obfuscation.
To understand the functionality of dynamic
data masking, we can create a membership data table having some dynamic data
masking definitions as given below:
Use Demo
Go
----- create table
with data masking defination
CREATE TABLE MemberShip
(
MemberID int IDENTITY PRIMARY KEY,
---- Data Masking Type - Default
FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
LastName varchar(100) NOT NULL,
---- Data Masking Type - Custom String
PhoneNo varchar(12) MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)') NULL,
---- Data Masking Type - Email
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
---- Data Masking Type - Random
MembershipFee float MASKED WITH (FUNCTION = 'random(1, 12)') NULL
);
----- Insert values into MemberShip
table
INSERT MemberShip (FirstName, LastName, PhoneNo, Email, MembershipFee) VALUES
('Ryan', 'Arjun', '555-123-4567', 'ryan.arjun@gmail.com',5400),
('Kimmy', 'Wang', '555-936-4568', 'kimmy,wang@yahoo.com.co',5400),
('Black', 'Smith', '932-123-0569', 'black.smith@aol.com',5400),
('John', 'Ray', '932-025-0523', 'john.ray@rediffmail.com',5400);
---- Pull values
from MemberShip table
SELECT MemberID As Id, FirstName, LastName, PhoneNo, Email, MembershipFee as Fee FROM dbo.MemberShip;
|
In the above table, we have defined all
the Data Masking type associated with various data columns such as –
- FirstName column is using the default data masking type where first character of the name will be display only and other information will be hide with X characters.
- PhoneNo column is using the custom string data masking type where starting five character will display only and other information will be hide with X characters.
- Email column is using the Email data masking type where starting five character will display only and other information will be hide with X characters.
- MemberShipFee column is using the Email data masking type where starting five character will display only and other information will be hide with X characters.
Create
a user and Granting Permissions to View masked Data
Now, we have the Membership data table
and we need to verify the data masking functionality on this table. For this,
we need to create a user and authorize him to get access on the above data tale
as given below:
Use Demo
Go
----- Create User
CREATE USER RyanArjun WITHOUT LOGIN;
----- Give
permission
GRANT SELECT ON Membership TO RyanArjun;
|
We need to pull the data for the testing
user as given below:
Use Demo
Go
----- Pull the
data with the respective user
EXECUTE AS USER = 'RyanArjun';
SELECT Id=MemberID, FirstName, LastName, PhoneNo, Email, Fee= MembershipFee
FROM dbo.MemberShip;
REVERT;
|
For the testing user ‘RyanArjun’, Dynamicdata masking is working perfectly and the requested sensitive data is not
available for him.
How
to identify the Masked Columns in your database?
In SQL Server 2016, we can use the sys.masked_columns view to query for
table-columns that have a masking function applied to them. Basically, sys.masked_columns view inherits from
the sys.columns view which is capable to returns all columns in the sys.columns
view, plus the is_masked and masking_function columns, indicating if the column
is masked, and if so, what masking function is defined. This view only shows
the columns on which there is a masking function applied as given below:
Use Demo
Go
----- Pull the
data from sys.masked_columns
SELECT C.NAME, TBL.NAME AS TABLE_NAME, C.IS_MASKED, C.MASKING_FUNCTION
FROM SYS.MASKED_COLUMNS AS C
JOIN SYS.TABLES AS TBL
ON C.[OBJECT_ID] = TBL.[OBJECT_ID]
WHERE IS_MASKED = 1;
|
Adding
Data Mask on an new Column
In this section, we will add new column called
EndDate to MemberShip data table and also add data masking functionality also
as given below:
Use Demo
Go
---- Add EndDate
Column to MemberShip
ALTER TABLE dbo.MemberShip
ADD EndDate DateTime MASKED WITH (FUNCTION = 'default()') NULL
----UpDate EndDate
value
Update MemberShip
set EndDate='2016-12-31'
---- Pull values
from MemberShip table
SELECT MemberID As Id, FName =FirstName, LName= LastName,
PhoneNo, Email, MembershipFee as Fee, EndDate
FROM dbo.MemberShip;
----- Pull the
data with the respective user
EXECUTE AS USER = 'RyanArjun';
SELECT MemberID As Id, FName =FirstName, LName= LastName,
PhoneNo, Email, MembershipFee as Fee, EndDate
FROM dbo.MemberShip;
REVERT;
|
Editing
a Mask on an Existing Column
If we want to apply data masking on the
existing column then we need to use the alter command to apply the data masking
as given below:
Use Demo
Go
---- ALTER
LastName Column to MemberShip
ALTER TABLE dbo.MemberShip
ALTER COLUMN LastName varchar(100) MASKED WITH (FUNCTION = 'default()') NULL
----- Pull the
data with the respective user
EXECUTE AS USER = 'RyanArjun';
SELECT MemberID As Id, FName =FirstName, LName= LastName,
PhoneNo, Email, MembershipFee as Fee, EndDate
FROM dbo.MemberShip;
REVERT;
|
Now, we can see that we have applied data
masking on almost all columns and testing user is not capable to see the actual
data and sensitive data is secured.
How
to remove Dynamic data masking?
If we want to remove data masking from
any column then we simply need to use drop command just before masked clause as
given below:
Use Demo
Go
---- Drop Data
Mask from the column
ALTER TABLE dbo.MemberShip
ALTER COLUMN LastName DROP MASKED;
----- Pull the
data with the respective user
EXECUTE AS USER = 'RyanArjun';
SELECT MemberID As Id, FName =FirstName, LName= LastName,
PhoneNo, Email, MembershipFee as Fee, EndDate
FROM dbo.MemberShip;
REVERT;
|
No comments:
Post a Comment