CODEWITHSUNDEEP
sql-servert-sqlpermissionsuser-permissions
Bluuu
Bluuu

Reputation: 481

Block access to column without changing query

I have a Microsoft SQL Server with data that needs to be protected (certain sensitive columns of some tables) and an application that queries that database like this:

SELECT BoringColumn, SensitiveColumn FROM Table

I have the following restrictions:

  1. I have multiple users (3-4) each with different columns visible or not. In this example SensitiveColumn should not be accessible.
  2. I can not directly update the queries that the application sends

What did I try already:

I tried to use SQL Servers Dynamic Data Masking feature. However it's not granular enough, you can just turn it on or off per user but not just for some columns. And its can leak data in queries, the link above explains that as well.

I know I can just deny the user SELECT on Table.SensitiveColumn. However then any existing query asking for the table just breaks with permission errors.

What other options do I have left?

Ideally I would like something that replaces the query on the serverside and executes something like this:

SELECT BoringColumn, 'N/A' as SensitiveColumn FROM Table

Upvotes: 0

Views: 712

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

I think I found a possible solution:
Change the table structure - Rename the SensitiveColumn to a different name, and add a computed column with the old name of the SensitiveColumn, that will show results based on current_user.

CREATE TABLE tblTest
(
    boringColumn int,
    SensitiveBase varchar(10), -- no user should have direct access to this column!
    SensitiveColumn as 
        case current_user 
            when 'Trusted login' then SensitiveBase
            else 'N/A' 
        end
)

The one thing I'm not sure about is if you can deny access to the SensitiveBase column but grant it to the SensitiveColumn.
I'll leave you to test it yourself.
If that can't be done, you can simply grant select permissions on the SensitiveBase column only to trusted login and deny them for everyone else.

Upvotes: 1

Related Questions