locked
Sql "Control" permission and Cert\Sym Key visibility RRS feed

  • Question

  • As dbo I encrypted a column in a table using a Cert and Symmetric key. Then I created a new user with only read\write permissions to that table. I logged in as the new user and found that I could not open the Symmetric key and "select * from sys.certificates" returned nothing. Then (as dbo) I granted the new user "Control" permission and found the new user could open the key, decrypt data in the table, etc.


    What is the Control permissions and is use of it an acceptable way to restrict an account's ability to encrypt\decrypt data?

    TIA,

    barkingdog



    Friday, March 27, 2009 5:33 AM

All replies

  •   Do you refer to CONTROL  on a specific CERTIFICATE? or CONTROL permission (DB scoped)?

      For encryption/decryption calls, the user requires as a minimum VIEW DEFINITION on the SYMMETRIC KEY and CONTROL on the CERTIFICATE protecting the SYMMETRIC KEY (NOTE: Any given SYMMETRIC KEY can be protected by more than one mechanism, including multiple CERTIFICATE objects).
     
      CONTROL on a CERTIFICATE means that the user has all privileges on the CERTIFICATE object, including access to the private key. Because of the implications of this permission, it is recommended to avoid sharing control of a single CERTIFICATE across several users, instead, it is recommended to create separate CERTIFICATE objects for each user.

      If you are referring to CONTROL (i.e. no ON clause)  permission, the scope is the whole DB, and it is giving full control (think DBO)  to the user. Following the least-privilege principle, I wouldn't recommend using this permission for the scenario you described.

      I hope this information helps.
      -Raul Garcia
       SDE/T
       SQL Server Engine

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, March 30, 2009 6:37 PM
  • Raul,

    Raul,

    I didn't realize there were two "Control" options (Certificate or permission.).

    I was ignorantly referring to CONTROl permisison (DB scoped) and, indeed, that seems to confer many rights on the user with that permission.


    Regarding

    "CONTROL on a CERTIFICATE means that the user has all privileges on the CERTIFICATE object,"


    How do I assign a user CONTROL on a CERTIFICATE? The CREATE CERTIFICATE command

    (http://msdn.microsoft.com/en-us/library/ms187798.aspx)

    does not appear to involve and user names and I don't think of a CERTIFICATE as an object one can assign permisisons to.

    TIA,

    Barkingdog
    Tuesday, March 31, 2009 2:56 AM
  • Please visit http://msdn.microsoft.com/en-us/library/ms186278.aspx . This link contains all information about how to assign permissions on Certificates to any database principal.

    Regards,
    Sumesh


    Please Indicate ("Mark as Answer") if a Post has Answered the Question. And this posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 1, 2009 8:18 AM
    Answerer