Protect Data

Question

I don't want someone to see the whole data. Especially for credit cards' numbers, telephone and so on.

How to protect these data.

Thanks.

Answer 1

There are multiple ways to do that.

You can use "Dynamic Data masking":

https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017

If you are storing credit card numbers, you should also encrypt them:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017

Answer 2

Hi,

 

From your description, my understanding the data has been stored in the table and you want to mask part of these data when querying.

 

It is suggested to using the Dynamic Data Masking. Dynamic data masking can limit exposure of sensitive data and prevent users who should not have access to the data from viewing it. For other users you want them to see the unmasked data, you just grant UNMASK permission to them. For example:

 

 

CREATE TABLE example
  (ID int IDENTITY PRIMARY KEY,  
   Name varchar(20),  
   Phone varchar(12) MASKED WITH (FUNCTION = 'partial(3,"XXX",4)') NULL,  
   Card varchar(100) MASKED WITH (FUNCTION = 'partial(3,"----",0)') NULL);  
  
INSERT into example VALUES   
('Tom', '180018001800', '34567890231')

CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON example TO TestUser;  
  
EXECUTE AS USER = 'TestUser';  
SELECT * FROM example;  
REVERT;

/**Results
ID          Name               Phone        Card
-------------------------------------------------------------------------------------
1           Tom                180XXX1800   345----
*/

GRANT UNMASK TO TestUser;  
EXECUTE AS USER = 'TestUser';  
SELECT * FROM example;  
REVERT; 

/*Results

ID          Name              Phone        Card
----------- -------------------------------------------------------------------------
1           Tom               180018001800 34567890231

**/

 

 

 

Best Regards,

Emily

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]