Post reply

Opaque Encryption - with No Views, or Special SQL

  • January 25, 2021 at 7:02 pm

    #3837311

    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:

    • Without using views  [extra work, and requires users to know where to query]
    • Without users to having to write any special words in their SQL like encrypt/decrypt

    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

     

    • This topic was modified 3 years, 10 months ago by  Vmusic.

  • January 25, 2021 at 7:44 pm

    #3837332

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 25, 2021 at 8:20 pm

    #3837340

    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