Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- Drop the table and user(if they exist)
DROP TABLE IF EXISTS dbo.Customer
DROP USER IF EXISTS TestUser
--------------------------------------------------------------------------------------------------
-- Create table with different masking functions
CREATE TABLE dbo.Customer
(
CustomerID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50) MASKED WITH (FUNCTION = 'default()'),
Age INT MASKED WITH (FUNCTION = 'random(80,90)'),
PhoneNumber VARCHAR(12) MASKED WITH (FUNCTION = 'partial(2,"X-XXX-X",3)'),
Email VARCHAR(50) MASKED WITH (FUNCTION = 'email()'),
SSN VARCHAR(16) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",2)'),
CreditCardNumber VARCHAR(16) MASKED WITH (FUNCTION = 'default()')
)
-------------------------------------------------------------------------------------------------
-- Insert test data into the Customer table
INSERT INTO dbo.Customer
(FirstName, LastName, Age, PhoneNumber, Email, SSN, CreditCardNumber) VALUES
('Samir', 'Behara', '20', '1234567890', 'samirbehara@gmail.com', '987-123-3241', 'AWE123UJK5438765'),
('Peter', 'Denner', '30', '0987654321', 'peterdenner@yahoo.com', '765-234-9812', '1928EYHJ758492UE'),
('Dennis', 'Knox', '40', '1122334455', 'dennisknox@hotmail.com','124-432-8965', '1234UEHW5678TEUJ');
--------------------------------------------------------------------------------------------------
-- View the unmasked data in Customer table
SELECT * FROM dbo.Customer;
--------------------------------------------------------------------------------------------------
-- Create a new user and provide it with only Read access to Customer table
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON dbo.Customer TO TestUser;
EXECUTE AS USER = 'TestUser';
-- Verify that you are now seeing masked data
SELECT * FROM dbo.Customer;
--------------------------------------------------------------------------------------------------
-- View the columns where the Masking function is applied
SELECT c.name 'ColumnName', tbl.name 'TableName', c.is_masked 'IsMasked', c.masking_function 'MaskingFunction'
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;
--------------------------------------------------------------------------------------------------
@giupardeb

This comment has been minimized.

Copy link

giupardeb commented Jun 12, 2018

Hi!, I use mysql Server version: 5.7.22-0ubuntu0.16.04.1, and when I execute the following code:
CREATE TABLE dbo.Customer ( CustomerID INT IDENTITY PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50) MASKED WITH (FUNCTION = 'default()'), Age INT MASKED WITH (FUNCTION = 'random(80,90)'), PhoneNumber VARCHAR(12) MASKED WITH (FUNCTION = 'partial(2,"X-XXX-X",3)'), Email VARCHAR(50) MASKED WITH (FUNCTION = 'email()'), SSN VARCHAR(16) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",2)'), CreditCardNumber VARCHAR(16) MASKED WITH (FUNCTION = 'default()') )
mysql return the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE dbo.Customer (CustomerID INT IDENTITY PRIMARY KEY, FirstName VARCHAR(50), '

Why?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.