locked
Prevent execution of ALTER MASTER KEY RRS feed

  • Question

  • Hi All,

    This should be trivial, but much hunting through BOL and experimenting with account permissions has not found the answer.

    How do I restrict execution of ALTER MASTER KEY and ALTER SERVICE MASTER KEY to a single account, while allowing other accounts to perform all the usual DBA/sysadmin actions?

    Likewise can I restrict creation and alteration of certificates to a single account?


    Apart from the regulatory requirements to limit who can change keys, without this restriction there is a hole in the SQL Security:
    A DBA who does not know the password for the master key can use ALTER MASTER KEY REGENERATE to change it to a key he does know. The rest of the encryption hierarchy in the database is unaltered and the database continues to function.  He then backs up the db, restores it to another machine, and because he knows the password for the MASTER Key, can open that key and decrypt the data from then on.  Requiring the MASTER KEY to be secured with a password rather than the SERVICE MASTER KEY creates a bigger security hole, as then functions and procedures which need to decrypt the data need to have this password coded into them.

    Any help would be much appreciated,


    Thanks,

    Richard

    Monday, March 16, 2009 5:07 PM

Answers

  •   Since you are using a sysadmin account, there is no way to deny any access to any object. Sysadmins have, by definition, ownership of the system and are not subject to nay permission checks. CONTROL and CONTROL SERVER permission actually cover any other permission under the scope (DB/server respectively), denying CONTROL will effectively deny every single possible permission on the database.

      For addressing compliance concerns, I would prefer to refer you to the SQL Server Compliance Portal (http://www.microsoft.com/sqlserver/2008/en/us/compliance.aspx) and to an upcoming webcast on the SQL Server 2008 capabilities for meeting PCI compliance needs (http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032404173&EventCategory=4&culture=en-US&CountryCode=US).

      Since part of what you are trying to achieve is separation of duties, I would also like recommend the following technical article: Engine Separation of Duties for the Application Developer (http://msdn.microsoft.com/en-us/library/cc974525.aspx).

      I hope this information helps and that the information we are providing on these specialized portals will also be helpful.

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 18, 2009 7:14 PM

All replies

  •   The permissions required to alter the SERVICE MASTER KEY (SMK) & database MASTER KEYs (DBMK) are quite elevated: CONTROL SERVER and CONTROL on the database respectively. These permissions pretty much imply full control of the scope (server/DB) and that they have management access to the respective scopes.

       When a DBMK is created, there will be two protecting mechanisms: The SMK and the password specified at creation time. The SMK will allow any user in the DB to automatically open and use the DBMK, if this protection is removed, it would be necessary to explicitly open the DBMK with the password in order to use it if the DBMK was not already opened in the session (i.e. SELECT * FROM sys.openkeys). Typically the password protection of the DBMK is used to be able to attach the DB in a different server, open the DBMK by a password and then reestablish the SMK protection for ease of use.

      The ALTER MASTER KEY REGENERATE DDL requires that the key is opened in the session. If the DBMK is protected by the SMK or the key is already opened in the session and the user has CONTROL permission on the DB, the ALTER MK DDL will succeed. This is by design.

      Please let us know if you still have any questions or feedback on the SMK/DBMK model, we will be glad to help.

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 17, 2009 11:00 PM
  • Hi Raul,

    Many thanks for the feedback. I think you've answered the question, but let me check my understanding and the implications:

    My database protects the DBMK using the SMK, so that stored procedures can encrypt data without developers knowing anything other than which symmetric key to use. So, as I found, a user with CONTROL permission can ALTER the DBMK without knowing the current password.  I was logged in with an account with sysadmin rights on the developement machine.
    ** In order to deny a user the right to regenerate the database master key I have to deny them CONTROL permisson on the database.  **

    What else would this prevent them from doing?
    Would this effectively prohibit them from performing the normal DBA duties for that database: e.g. backup/restore, monitor, create and drop indexes add tables & procedures? 
    Presumably if they retain CONTROL SERVER then they could give themselves the CONTROL rights on the database?
    I can log any of this activity with SQL Audit, but that raises another set of questions.


    One of the PCI/DSS requirements (and best practice for encryption) is seperation of duty, so I'm trying to establish if I can create an effective DBA role who can do what he/she needs to, but who can't change the keys. This may be especially important if my organisation chooses to employ outsourced remote DBA.  

    Regards,

    Richard

    Wednesday, March 18, 2009 10:28 AM
  •   Since you are using a sysadmin account, there is no way to deny any access to any object. Sysadmins have, by definition, ownership of the system and are not subject to nay permission checks. CONTROL and CONTROL SERVER permission actually cover any other permission under the scope (DB/server respectively), denying CONTROL will effectively deny every single possible permission on the database.

      For addressing compliance concerns, I would prefer to refer you to the SQL Server Compliance Portal (http://www.microsoft.com/sqlserver/2008/en/us/compliance.aspx) and to an upcoming webcast on the SQL Server 2008 capabilities for meeting PCI compliance needs (http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032404173&EventCategory=4&culture=en-US&CountryCode=US).

      Since part of what you are trying to achieve is separation of duties, I would also like recommend the following technical article: Engine Separation of Duties for the Application Developer (http://msdn.microsoft.com/en-us/library/cc974525.aspx).

      I hope this information helps and that the information we are providing on these specialized portals will also be helpful.

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 18, 2009 7:14 PM

  • Thanks Raul,

    Looks like the answer to the original question is effectively "No". 

    I can achieve compliance in other ways though, we just have to be careful, make sure any changes are logged using SQL Audit, and that there is a daily automated log reader to find the ALTER statement in amongst the thousands of regular events.

    The additional information looks very comprehensive - I'll have to read that as well.

    Thanks again

    Richard
    Thursday, March 19, 2009 11:48 AM