SQL Server 2016: Hide column data from DBAs but specific users can view data through application

Question

I'm trying to enable access to team leaders of salary information through PowerBI, but encrypt this data from other users and the DBAs. Users denied access to this column data should still be able to execute the query but only see encrypted characters for the salary information.

I'm using SQL Server 2016.

I have tested the new 'Always Encrypted' functionality, and this works perfectly... but with the exception that I'm unable to pass the 'column encryption setting=enabled' parameter to the PowerBI connection string. By all accounts, PowerBI does not support this functionality at present.

I am currently testing the use of column encryption via the use of Column Level encryption and Symmetric Keys, but the problem with this is that I am hard coding the OPEN SYMMETRIC KEY SymmetricKey1 & DECRYPTION BY CERTIFICATE Certificate1 code into the SQL and if users do not have access then an error causes SQL to fail when tested by a user.

I'm new to certificates and encryption and I'm currently on a steep learning curve... so go easy on me.

Thanks

Answer 1

• We can make use of the concept called dynamic data masking

• Dynamic data masking works by masking column output to users, who don't have permissions. Here is an example:

--create a table

CREATE TABLE ClientInfo

  (ClientID int IDENTITY,

   FirstName varchar(65),

   LastName varchar(65),

   PhoneNum bigint 

      MASKED WITH (FUNCTION = 'default()'),

   EmailAddr varchar(100) 

      MASKED WITH (FUNCTION = 'email()'),

   CreditCardNum varchar(19) MASKED 

      WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)'),

   BirthDT date MASKED 

      WITH (FUNCTION = 'default()'));

INSERT Clientinfo (FirstName, LastName, PhoneNum, EmailAddr,CreditCardNum,BirthDT) VALUES 

('George', 'Washington', 5555814441, 

'[email protected]', '0123-4567-8901-2345','02/22/1732'),

('Thomas', 'Jefferson', 5559841298, 

'[email protected]', '9999-9999-9999-9999', '04/13/1743'),

('Abraham', 'Lincoln', 5554070123, 

'[email protected]','0000-1111-2222-3333', '02/12/1809');

• select and see the data

select * from clientinfo

• Restrict permissions to users for whom, you want to restrict viewing

CREATE USER user1 WITHOUT LOGIN;

GRANT SELECT ON ClientInfo TO user1;

• Execute as this user

EXECUTE AS USER = 'AppReader';

SELECT * FROM ClientInfo;

REVERT;

• After executing the above query, it will not show all data and will be masked differently based on masked functions.See below screenshot

• In order to provide access to users, you can use below query

CREATE USER AppAdmin WITHOUT LOGIN;

GRANT SELECT ON ClientInfo TO AppAdmin;

GRANT UNMASK TO AppAdmin;