See "Dynamic Masking".
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?
6 answers
Sort by: Most helpful
-
-
db042190 1,516 Reputation points
2022-05-18T15:52:17.033+00:00 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.
-
Erland Sommarskog 100.1K Reputation points MVP
2022-05-18T21:26:06.057+00:00 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.
-
Olaf Helper 40,156 Reputation points
2022-05-19T06:20:50.7+00:00 new column based on permissions
Create views fo each permission group which returns only that columns the user group are allowed to see.
-
Seeya Xi-MSFT 16,426 Reputation points
2022-05-19T09:38:36.79+00:00 Hi @db042190 ,
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.