Best 6 masking functions example.
Data Masking SQL: In Today’s Word Data Privacy and security is very important factor. our data and information stored in the database tables should be Confidentials and protected, to make data secure and Confidentials we have to mask our data to preview.
Table of Contents
Data Masking
Data Masking in SQL : Original data should not be shows to unauthorized person. only authorized person can see all information from the tables, to avoid data sensitive data to not preview to anyone who is not authorized person you must have to do data masking.
In some organizations data like PAN , Aadhar,Passwords Emails and other financial data like account_numbers etc should be masked.
To prevent your private data you can use data masking techniques, In SQL Server Data Masking is robust and secure mechanism to mask your data.
In This Tutorial we will see how to mask data in SQL Server.and various data masking techniques with example.
Data Masking is a technique which is used to secure original data to preview and show something for specific characters like ‘548XX8’.
Types Of Data Masking in SQL
- Dynamic Data Masking (DDM)
- Static Data Masking
- Row-Level Security (RLS)
Let us discuss in detail types of data masking with example one by one.
Dynamic Data Masking (DDM):
Dynamic Data Masking is technique that allows you to mask specific columns dynamically for specific user using SQL query
In Dynamic data masking user can mask data as per user requirement like default masking , partial masking and random masking.
Masking data is an effective way to secure and hide original data without changing original values in the table.
Example:
IF you want to mask data without modifying any columns data in the table then you can use Dynamic Data Masking technique.
Suppose you have a table named EmpMaster with a column named Mobile. You can mask the Mobile column using Dynamic Data Masking (DDM):
Default Masking Function
Now, when you select the EmpMaster table, the ‘Mobile’ will be masked, without changing original data in the ‘Mobile’ column.
Default Masking Function
Query with DDM masking.
---Execute in 'sa' user---
----create new user----
create user testuser
----give permission to user----
GRANT SELECT ON [dbo].EmpMaster to testuser
----Default Masking Function----
To apply any datamasking function on column you must have to login your system or user defined sql user like ‘sa’ or any user.
alter table dbo.EmpMaster
alter column Mobile nvarchar(10) masked with (Function='default()');
----use new created user to show masking data----
To see masked value in database you must have to login as testuser which we have created and applied permission to read a EmpMaster table.
execute as user = 'testuser'
----Execute in testuser----
select * from EmpMaster
select ID,Name,Email,Mobile,Tel,Address from EmpMaster
Result for Masked column ‘Mobile’ will looks like below.
Here, You need to create user for who can show only masked data,we have created testuser and given data read permission to testuser,when you want to see masked data you must have to login with testuer the you can only see masked data for that table.
To mask data you must have to login your original user or system user like ‘sa’, to apply data masking for column you must be logged in as ‘sa’ user. you can see original data using your ‘sa’ user and masked dt using testuser which you were created.
Email Masking Function
Email Function is Dynamic masking function which mask the email id. Email Function Mask all data Except First Letter and constant .com suffix from Email Address.
This function shows only first letter of email address and constant domain suffix like ‘.com’,’.in’ etc.
Example with Create Table syntax:
Create Table Table_name (
Email varchar(100)
MASKED WITH (FUNCTION = 'email()') NULL)
Example with alter table syntax:
Alter Table EmpMaster
ALTER COLUMN Email ADD
MASKED WITH (FUNCTION = 'email()')
Email column Masking Example
Alter table dbo.EmpMaster
Alter column Email varchar(25) masked with (FUNCTION = 'email()');Here , you don’t need to create user again and assign the permissions to read the table because you have already did it, you can use testuser to preview masked data for email column.
----Execute in 'sa' user----
--- create new user---
create user testuser
---give permission to user---
GRANT SELECT ON [dbo].EmpMaster to testuser
To check masked values in EmpMaster Table Login as testuser and execute the query on EmpMaster Table
--Use new created user to show masking Email Column---
execute as user = 'testuser'
----Login as testuser or Execute in testuser----
select ID,Name,Email,Mobile,Tel,Address from EmpMaster Result for Masked column ‘Email’ and with existing masked column ‘Mobile’ will looks like below.
Here you have used Default masking function and Email masking functions to mask the column, if you want to mask partial data in column then you can use Partial Masking function.
Database Administration Archives – Developers Tutorial (itdevelopement.com)
Partial Masking Function
In Partial Masking function you can mask the column partially like ’15XXXX99′ in partial masking column you will see half value as masked and half value as origional.
Here in Partial Making Function, we will mask data in another table ‘Employee’.
so you need to give permission to your testuser to read the Employee Table. Then you can view Employee masked data in testuser sql user.
Partial Masking Example
-----Execute in 'sa' user------
select * from dbo.Employee
---Partial Data Masking---
----mask data while creating table----
Create table dbo.Employee (
EmployeeSalary nvarchar(10) masked with (Function='partial(2,"xxx",2)'));
----Mask data in existing table----
alter table dbo.Employee
alter column EmployeeSalary nvarchar(10) masked with (Function='partial(2,"xxx",2)');
----give permission to user for specific table to view
GRANT SELECT ON [dbo].Employee to testuser
--use new created user to show masking data
execute as user = 'testuser'
----Execute in testuser-----
select EmployeeName,EmployeeAddress,EmployeeSalary,
EmployeeCity,EmployeeBasicSal from dbo.Employee
You can see here EmployeeSalary column is masked with partial values, here we have provided three parameters in Partial masking function
2,xxx,2 here have masked middle three values in EmployeeSalary column except first two and last two values in it. In this way you can mask partial data in column using partial masking function.
Random Masking Function
In Random Masking Function used to mask values in specific range,
In this random function we can mask any numeric original values with random values within given range.
You can mask data in ranges from first character to specific character you can give range to mask the values from start range and end range.
Random masking syntax for New table:
Create Table Table_name (
Column_name bigint MASKED WITH (FUNCTION = 'random([start range], [end range])'))Random masking syntax for existing table:
Alter table Tabel_Name
Alter column Column_name datatype masked with (Function= 'random([start range], [end range])')
Random Masking Example
Alter table Employee
Alter column EmployeeDASal float masked with (Function='random(2,4)');
---use new created user to show masking data---
execute as user = 'testuser'
----Execute in testuser-----
select EmployeeName,EmployeeAddress,EmployeeSalary,
EmployeeCity,EmployeeBasicSal,EmployeeDASal from dbo.Employee
Static Data Masking:
Static Data masking creates strong and less harmful version of your sensitive information or data.
Masking Static data permanently replace original values with masking values, Static data masking can be used in Development, UAT and Testing environment databases. Don’t use static masking on Production environment otherwise you will not be able to see your original data.
Static data masking can be used when you want to share your data for research a, development or migration that time you can mask your data with static data masking technique.
Giving data to any third-party without masking may be non-secure, you should mask the data before giving to other, in this scenario you can use Static data masking technique.
Consider Example:
You can set static values to column using Update statement, when you set Valeu to column then your new value will be staic for example
Update Employee_1 set Salary=’xxxxx’ then all employess’s salary willl be masked with statoc value ‘xxxx’.
For Static data masking you need to create masking function “MASKED WITH FUNCTION”
---Masked With Function-----
-- Create a masked copy of the Employee table
CREATE MASKED TABLE Employee_Masked
WITH (MASKING POLICY = 'PartialMaskingPolicy');
-- Define masking function for Salary column
CREATE FUNCTION PartialMaskingPolicy() RETURNS nvarchar(100)
WITH SCHEMABINDING
AS
BEGIN
RETURN 'XXX,XXX';
END;Now here, the _Masked table will have a masked version of the salary column.
Now the table have masked values for column.
Row-Level Security (RLS)
Row-Level Security allows you to control access to rows in a table based on the characteristics of the user executing a query.
This is particularly useful when you want to restrict access to specific rows for certain users.
RLS Example
Consider a example where you have a table named Employee_1 with a column named Salary
Using RLS, you can limit access to rows based on user roles:
— Create a security policy for the Employee_1 table
CREATE SECURITY POLICY Employee_1Ploicy
ADD FILTER PREDICATE dbo.Employee_1()
ON dbo.Employee
WITH (STATE = ON);
-- Create a function to filter records based on user role
CREATE FUNCTION Employee_1Policy() RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE (suser_sname() = 'testuser');
Now, only users with the ‘testuser’ role can access rows from the table that meet the specified condition.
Conclusion
Data masking is the technique to hide or secure protect personal or original data from others to preview.
using different masking function, we can mask the data in SQL server dynamically.
Masking data in SQL server is today’s clients need for everyone to mask the sensitive data for data protection.