January 25, 2021 at 7:02 pm
Hi,
I want to encrypt certain columns in various tables in a SQL Server 2019 database.
I would like to do this, for five groups of users - or five roles. Some of these users actually use SSMS and run queries themselves, while others access data through interactive reports, and some link to the database through Excel.
However I would like to achieve this:
So the users, up front, have no idea/no clue that anything is encrypted. They just run their reports or run their queries like they always do, and then only if they happen to return the results with encrypted data, completely based on their role, they see the actual or they see the encrypted data. Solely based on their role.
The idea here is the users have to know NOTHING, or have to do any extra work in regards to knowing where or which tables/to query or what SQL to write - they just do everything as they did, just like before encryption happened.
I don't think SQL server supports role based encryption like this - correct????
Thanks
Vmusic
January 25, 2021 at 7:44 pm
You would do that with view(s). The ENCRYPT / DECRYPT would be only within the view(s).
The users don't have to query anything different, as you can make the original table name a view name (indeed, users should only ever query from a view name, never from the real table name). SQL will treat the view the same as a table as far as queries go.
SQL also has masking capability, if you don't really need encryption but just need a way to prevent users from seeing certain columns / data.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 25, 2021 at 8:20 pm
Thank you!!
January 26, 2021 at 7:36 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply