Feedback needed in designing security architecture for database containing encrypted data

• Marios Philippopoulos

  SSC Guru

  Points: 57190

  More actions

  April 9, 2009 at 2:06 pm

  #202910

  I have been asked to come up with architecture recommendations for a database that will be housing encrypted data. Only a handful of columns in several tables will be encrypted. The rest of the data will be stored in unencrypted form.

  Here is how I think the overall security architecture, pertaining to data encryption, should look like. I would appreciate any feedback on this.

  (1) Create database master key with strong password:

  - to encrypt the private key of the asymmetric keys and certificates, since they are created

  without a passwd - see below

  (2) Create data-access certificate - ct_data_access - that will protect the data-access symmetric keys in the system

  (3) Create symmetric key - sk_data_access - to encrypt data with

  - the symmetric key is protected by the certificate (step 2)

  (4) Create code-signing certificate - ct_code_signing - with strong password.

  This will be used for signing data-access code that will be performing the data encryption/decryption.

  (5) Create a user from the code-signing certificate - user_code_signing.

  (6) Create 2 schemas: one for the tables containing the encrypted data (data_schema) and one for the encryption/decryption stored procedures (code_schema); at the same time, create the underlying tables and stored procedures:

  - the schemas will ease management of access permissions

  - the stored procedure code will use the certificate (step 2) and symmetric key (step 3) to incrypt/decrypt the data and insert data into/retrieve data from the table(s)

  - the stored procedures will execute in the context of a least-privilege user, least_priv_user, created for this purpose (using the WITH EXECUTE AS clause)

  - least_priv_user will be created as a database-only user with minimal permissions on the database (public role)

  - this is what the code of a data insertion stored procedure, insertIncryptedDataProc, will look like in pseudocode:

  CREATE PROC insertIncryptedDataProc

  WITH EXECUTE AS least_priv_user

  BEGIN

  OPEN sk_data_access WITH ct_data_access

  INSERT INTO tblName (encrypted data) USING sk_data_access

  CLOSE sk_data_access

  END

  (7) Grant EXECUTE permissions on the stored procedures (code_schema - step 6) to a database role: app_role

  (8) Add a signature to the access stored procedures through the code-signing certificate, ct_code_signing (and certificate password)

  - this will enable stored procedure execution to run at an elevated security context (that of user_code_signing, step 5) as opposed to that of least_priv_user (see next step)

  (9) To achieve the objective of step (8) grant user user_code_signing:

  (a) control permissions on the data-access certificate, ct_data_access (step 2)

  (b) view definition permissions on the data-access symmetric key, sk_data_access (step 3)

  - these privileges will enable the opening of the certificate and symmetric key during sproc execution, so that data can be encrypted/decrypted.

  (c) SELECT-INSERT-UPDATE-DELETE permissions on schema data_schema (step 6)

  This security scheme will ensure that data encryption/decryption will take place only through the signed data-access stored procedures, and only by users belonging to app_role.

  Are there any holes in the above scheme? Anything I have missed?

  Links that have helped me on this so far:

  http://msdn.microsoft.com/en-us/library/cc837966.aspx

  http://www.thelastpickle.com/2008/01/15/how-i-think-sql-encryption-is-meant-to-be-used/

  http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/3058/

  __________________________________________________________________________________
  SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
  Persisting SQL Server Index-Usage Statistics with MERGE[/url]
  Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]