question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked db042190-2983 answered

off the shelf column level security

hi we run 2014 enterprise right now. We hope to be on 2019 in the coming months.

We are contemplating the addition of a column called "restricted access" to our dim account table. But even if an account has this flag turned on, it doesnt mean that users shouldnt be able to see the acct info. They should just be prevented from knowing its restricted. I know how weird that sounds.

Is there an off the shelf way in 2014 or 2019 to hide this column value from folks without special permissions? even in more mainstream scenarios, what if this was an employee record but we'd want to hide just the salary from folks not needing to know that info?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered TomPhillips-1744 commented

thx Tom, i didnt even think about obfuscation etc.

But i think what i'm looking for is a feature that totally hides the existence of the new column based on permissions. But allows it to remain on the most appropriate table otherwise.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The way you do that is put them in a "sensitive" table and then restrict access to that table.

1 Vote 1 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

You can grant permissions per column:

GRANT SELECT ON Accounts(col1, col2, selectedColumns) TO MostPeople
GRANT SELECT ON Accounts TO ThoseWhoMaySeeAll

But you may regret it.

I would be more inclined to solve this with a view that includes all columns but the secret one.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

new column based on permissions

Create views fo each permission group which returns only that columns the user group are allowed to see.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @db042190-2983,

Welcome to Microsoft Q&A!
Here is an example for your inference: https://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/

Best regards,
Seeya


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

thx all. the reality is that with self service bi, people are going to get access to tables and views.

its funny because column level security is so much easier in tabular cubes. But i cant justify taking our org in that direction for just this reason.

i think i like the "sensitive" table approach. I can put as many layers of security as i want on something like that. one of our cm guys even wants a separate account for accessing this part of the system. And a separate ssrs shared data source. and a separate ssrs folder. I'm guessing he wants pinch points where he can shut down misuse more easily. sounds a bit like overkill but not worth debating in my mind.

I'm guessing that erland says i might regret the grant approach because maintaining such a thing could get unwieldy.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.