GRANT UNMASK ON SCHEMA?

Question

GRANT UNMASK ON SCHEMA?

Kaye Cahs

SSCarpal Tunnel

Points: 4135
More actions
November 9, 2017 at 2:53 pm

#409759

Is there a way to grant UNMASK privileges to a user at a schema level?
My use case is this: There is a user who can query both the HR schema and the Sales schema. It is OK for him to see the masked (sensitive) client data in sales, but it is not OK him to see the masked (sensitive) employee data in HR. It is OK for him to see the unmasked (non-sensitive) employee data in HR.
This is essentially how we have it configured now.
   Create table HR.Employee (         EmpID INT IDENTITY(1,1) NOT NULL,         EmpName VARCHAR(20) NULL,         EmpSal DECIMAL (8,2) MASKED WITH (FUNCTION = 'default()')         )
    Create table Sales.Client(         CliID INT IDENTITY(1,1) NOT NULL,         CliName VARCHAR(20) NULL,         CliSSN CHAR(11) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)')         )
    INSERT INTO HR.Employee         (EmpName, EmpSal)     VALUES         ('Rowan',456879.72),         ('Worley',18386.88),         ('Gibson',25468.11)
    INSERT INTO Sales.Client         (CliName, CliSSN)     VALUES         ('Rowan','111-AA-1250'),         ('Farkle','222-EE-8921'),         ('Martin','333-FF-5315')
    GRANT SELECT ON schema::HR TO BobbyTables;     GRANT SELECT ON schema::Sales TO BobbyTables;
    GRANT UNMASK TO BobbyTables;
What I'd really like is something like this.
   GRANT UNMASK ON schema::Sales TO BobbyTables;
When I try this, I get the error "Incorrect syntax near 'UNMASK'"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24363 More actions · Answer 1

From my understanding, UNMASK is a database level permission.
I think what would likely be the best way to manage this would be to create a user without login that has unmask permissions. Then have a stored procedure created to pull the data from the tables in question and have it execute as the newly created UNMASK-ed user account. Then give permissions to that stored procedure to that user.

Here are some solutions that people found:
https://www.experts-exchange.com/questions/28994736/How-to-set-UNMASK-permission-only-to-only-one-table-in-SQL-Server-2016.html

but they all relate to using EXECUTE AS LOGIN or creating special unmasked versions of the table(s) and granting permissions on those.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 2

bmg002 - Thursday, November 16, 2017 8:26 AM
From my understanding, UNMASK is a database level permission.

It is the database level permission indeed:
https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Write a stored procedure that runs with EXECUTE as DBO and only give that user privs to run it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

As a bit of a sidebar, I think your use of masking is a bit out of line. A relatively unprivileged user can easily break it without much effort or understanding at all. It's posted all over the internet. If you have something worth protecting, like certain HR data, it should be encrypted, not masked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 5

Thanks for the comments, everybody.

@jeff Moden, I agree with your comment about masking versus encryption. On the surface, masking looked like a promising feature. In the days since I posted the original question, I've been reading about and experimenting with the masking feature. I'm disappointed with how easily it can be circumvented.

It is not well-suited for such sensitive information.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Kaye Cahs - Friday, November 17, 2017 7:15 AM
Thanks for the comments, everybody.
@jeff Moden, I agree with your comment about masking versus encryption. On the surface, masking looked like a promising feature. In the days since I posted the original question, I've been reading about and experimenting with the masking feature. I'm disappointed with how easily it can be circumvented.
It is not well-suited for such sensitive information.

Excellent. Thank you for the feedback. Heh... I worry about the security of everyone's data because the data you have might be MY data. 😉 And thanks for doing something about it even if it's not. Too many people just kick the can down the street on these types of things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jenda SSC Enthusiast Points: 174 More actions · Answer 7

If you have granted SELECT on schema only and UNMASK, both for certain db role, only role member can read unmasked data from tables in this schema. Others with read permission have masked data. It works on MSQL 2017.

Use [YourDB]; 
IF DATABASE_PRINCIPAL_ID('dbrole_reader') IS NULL CREATE ROLE [dbrole_reader] ;
IF SCHEMA_ID('YourSchema') IS NOT NULL GRANT SELECT ON SCHEMA::[YourSchema] TO [dbrole_reader] AS [dbo];
GRANT UNMASK TO [dbrole_reader] AS [dbo];
Use [YourDB]; 
EXEC sp_addrolemember @rolename = 'dbrole_reader',@membername = 'DOMAIN\UserName';