locked
DENY SELECT to ALL except few granted account RRS feed

  • Question

  • Summary:

    I want to create a view which needs to be restricted to some service accounts only. No other users should have access to that view even if they have database level access (through roles etc.) to select tables. These service accounts must not be removed from existing roles, as these accounts must retain all existing permissions.

    Context:

    I got a table with some encrypted columns (using passcode). The view I am trying to create is going to be encrypted (to avoid exposing passcode) and will have decryption function. This view will be restricted to all, except some service account. Another procedure (encrypted) will be used to insert and update data.

    I am open for alternative design suggestions, provided I could provide data in a view to service accounts (to avoid extensive changes to application infrastructure).



    • Edited by Mukesh BP Monday, October 3, 2016 9:33 AM
    Saturday, October 1, 2016 3:41 AM

Answers

  • Hello Mukesh,

    If you grant access a login t SQL Server / a databaseo the users still don't have any permissions to select any data. If they have, then because they have granted permission on an other way, e.g. because they are member of a database role like "db_datareader". Check the effective permissions of the login first.

    Yes, you can use DENY permissions, but believe me: At the end of all you don't want to do this, it will result in an opaque security system; who can and who can not access data?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, October 1, 2016 8:26 AM
  • Keep in mind that WITH ENCRYPTION does not mean encryption. It means obfustication. For anyone with sufficient permission can retrieve the byte pattern and translate the encoded text to clear text. So it buys you nothing.

    The arrangement with the decrypted columns adds sense to the idea of hiding the view while exposing the base tables. This was not clear from your previous post. (There was a similar question recently, and I assume that you asked that one as well.)

    And if only trusted users can get access to the view definition, the arrangement make sense, WITH ENCRYPTION or not.

    The solution for the permissions is the same as before: put the view in a separate schema. Do not grant permissions on this schema to other users. Do not use the roles db_datareader/writer, but make sure that these roles are empty. Instead define your customer roles that you grant permission on the dbo schema only.

    DENY is going to take you a quagmire. You need to be very careful to keep the service accounts out from the groups that are denied access, since DENY takes precedence.

    I assume that your view uses decryptbypassprhase(). An alternative might be to use decryptbykey, and make sure that the service account opens the key before accessing they view. The key should be protected with a ceritifcate to which the service account has been granted CONTROL permission. You can also use decryptbycert(). In this case, too, there is a password in the call, but since the user needs CONTROL permission on the certificate, knowing the password is not enough. Beware though the decrypting by certificate or an asymmetric key is a lot more expensive.

    If you would happen to be on SQL 2016 Enterprise Edition, you could use Always Encrypted, which moves the decryption outside of the database, for highest security.

    Saturday, October 1, 2016 10:58 AM
  • Since some users already have select rights as they are part of database roles. I can't DENY those roles access as svc_acc is also part of it.

    Correct. This why you should not use DENY.

    You put the view in the schema. You only grant access to this schema to test_role.

    Then you must make sure that users do not have permissions on database level. That is, they must not be member of db_datareader or db_datawriter. But they be granted SELECT or whatever they need on the dbo schema (and other schemas you may use).

    Monday, October 3, 2016 9:40 PM

All replies

  • Hello Mukesh,

    If you grant access a login t SQL Server / a databaseo the users still don't have any permissions to select any data. If they have, then because they have granted permission on an other way, e.g. because they are member of a database role like "db_datareader". Check the effective permissions of the login first.

    Yes, you can use DENY permissions, but believe me: At the end of all you don't want to do this, it will result in an opaque security system; who can and who can not access data?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, October 1, 2016 8:26 AM
  • Keep in mind that WITH ENCRYPTION does not mean encryption. It means obfustication. For anyone with sufficient permission can retrieve the byte pattern and translate the encoded text to clear text. So it buys you nothing.

    The arrangement with the decrypted columns adds sense to the idea of hiding the view while exposing the base tables. This was not clear from your previous post. (There was a similar question recently, and I assume that you asked that one as well.)

    And if only trusted users can get access to the view definition, the arrangement make sense, WITH ENCRYPTION or not.

    The solution for the permissions is the same as before: put the view in a separate schema. Do not grant permissions on this schema to other users. Do not use the roles db_datareader/writer, but make sure that these roles are empty. Instead define your customer roles that you grant permission on the dbo schema only.

    DENY is going to take you a quagmire. You need to be very careful to keep the service accounts out from the groups that are denied access, since DENY takes precedence.

    I assume that your view uses decryptbypassprhase(). An alternative might be to use decryptbykey, and make sure that the service account opens the key before accessing they view. The key should be protected with a ceritifcate to which the service account has been granted CONTROL permission. You can also use decryptbycert(). In this case, too, there is a password in the call, but since the user needs CONTROL permission on the certificate, knowing the password is not enough. Beware though the decrypting by certificate or an asymmetric key is a lot more expensive.

    If you would happen to be on SQL 2016 Enterprise Edition, you could use Always Encrypted, which moves the decryption outside of the database, for highest security.

    Saturday, October 1, 2016 10:58 AM
  • Thank you Erland, blame me my inexperience in security implementation, I am still not clear on how this scheme will work.

    Assume I create a new role - test_role (contains a service account svc_acc), a new schema - test_schema (schema owner test_role), and a view test_schema.test_view.

    Since some users already have select rights as they are part of database roles. I can't DENY those roles access as svc_acc is also part of it.

    Monday, October 3, 2016 10:04 AM
  • Since some users already have select rights as they are part of database roles. I can't DENY those roles access as svc_acc is also part of it.

    Correct. This why you should not use DENY.

    You put the view in the schema. You only grant access to this schema to test_role.

    Then you must make sure that users do not have permissions on database level. That is, they must not be member of db_datareader or db_datawriter. But they be granted SELECT or whatever they need on the dbo schema (and other schemas you may use).

    Monday, October 3, 2016 9:40 PM