SQL server table encryption

  • Hello All,

    I'm new to database encryption. Is there a way to achieve table level encryption in SQL server 2008 R2? Any inputs on this will be greatly appreciated.

    Thanks

  • column level encryption is what you are asking about., not table level. each individual field in a row would be encrypted.

    effectively each column that specifically needs to be protected would change it's datatype from whatever it is now to varbinary(max), and encryption decryption would be used to select / update the data on a per column basis

    so a table that was like this:

    CREATE TABLE [dbo].[Patient] (

    [PatientID] BIGINT IDENTITY(1,1) NOT NULL,

    [HospitalAccountNumber] NARCHAR(50) NULL,

    [FirstName] VARCHAR(50) NULL,

    [LastName] VARCHAR(50 NULL,

    [MiddleName] VARCHAR(50 NULL)

    CREATE TABLE [dbo].[Patient] (

    [PatientID] BIGINT IDENTITY(1,1) NOT NULL,

    [HospitalAccountNumber] VARCHAR(50) NULL,

    [FirstName] VARBINARY(max) NULL,

    [LastName] VARBINARY(max) NULL,

    [MiddleName] VARBINARY(max) NULL)

    --i might insert with this?

    select EncryptByCert(Cert_ID('MyDataSecurityCertificate'), CONVERT(VARCHAR(50),@FirstName)) AS FirstName

    --i might decrypt like this

    SELECT Cast(DecryptByCert(Cert_ID('MyDataSecurityCertificate'), P.FirstName) AS VARCHAR(50)) AS FirstName, FROM Patient

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (2/18/2015)


    column level encryption is what you are asking about., not table level. each individual field in a row would be encrypted.

    effectively each column that specifically needs to be protected would change it's datatype from whatever it is now to varbinary(max), and encryption decryption would be used to select / update the data on a per column basis

    so a table that was like this:

    CREATE TABLE [dbo].[Patient] (

    [PatientID] BIGINT IDENTITY(1,1) NOT NULL,

    [HospitalAccountNumber] NARCHAR(50) NULL,

    [FirstName] VARCHAR(50) NULL,

    [LastName] VARCHAR(50 NULL,

    [MiddleName] VARCHAR(50 NULL)

    CREATE TABLE [dbo].[Patient] (

    [PatientID] BIGINT IDENTITY(1,1) NOT NULL,

    [HospitalAccountNumber] VARCHAR(50) NULL,

    [FirstName] VARBINARY(max) NULL,

    [LastName] VARBINARY(max) NULL,

    [MiddleName] VARBINARY(max) NULL)

    --i might insert with this?

    select EncryptByCert(Cert_ID('MyDataSecurityCertificate'), CONVERT(VARCHAR(50),@FirstName)) AS FirstName

    --i might decrypt like this

    SELECT Cast(DecryptByCert(Cert_ID('MyDataSecurityCertificate'), P.FirstName) AS VARCHAR(50)) AS FirstName, FROM Patient

    What a coincidence. We are just discussing about this topic at work. And a question was brought to me or the group, which I think is a good one.

    Why encrypt data inside a database or column, if anyone with proper permissions can still query the data and retrieve actual values?

  • Because you're protecting it against people who don't have proper permissions.

    Consider, all HR people have access to the Employees table but only the HR head for each department should be able to see the salaries for the staff for that department. Yes, you can do it with views and denying permission on the table, or you can create an encryption key for each dept, encrypt the salary with the appropriate key and grant rights on the key only to the relevant dept head.

    Or, you can encrypt and decrypt in stored procs with the EXECUTE AS clause and have none of the actual users having any rights on the key, etc.

    It also means I don't have to worry too much about giving a developer/QA temporary read permissions on the Employees table, because all the sensitive data is encrypted and the developer does not have permission to open the keys

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/19/2015)


    Because you're protecting it against people who don't have proper permissions.

    Consider, all HR people have access to the Employees table but only the HR head for each department should be able to see the salaries for the staff for that department. Yes, you can do it with views and denying permission on the table, or you can create an encryption key for each dept, encrypt the salary with the appropriate key and grant rights on the key only to the relevant dept head.

    Or, you can encrypt and decrypt in stored procs with the EXECUTE AS clause and have none of the actual users having any rights on the key, etc.

    It also means I don't have to worry too much about giving a developer/QA temporary read permissions on the Employees table, because all the sensitive data is encrypted and the developer does not have permission to open the keys

    I guess that I did not expand on my question enough.

    I totally get it that if your app has two or more users with different permissions that's the case. But in cases where the application or design uses a single user which of course, already has SELECT permissions against that table or column and Dev already uses same user with same permissions. What would be the benefit? If a hacker grant himself sysadmin, he will be able to see the sensitive data anyway; encryption will not stop him from doing that. In fact, if a hacker is able to get the password for that single user, can also query the sensitive data because the user already has the ability to query that sensitive data.

    In other words, for a monolithic or single user model I fail to see how encryption will protect from "other user" if that other user simple does not exist.

  • the purpose of encryption is to prevent third parties from recovering the raw data, right? so encrypting at the field level is to at least make interception vectors more difficult.

    just encrypting a column is just a piece of the puzzle. that alone it protects from casual review by people who have db_datareader.

    it's the level of sophistication required to get in that deep, as well as the level of permissions together with penetrating the network itself that makes layered defense.

    penetrate the network, penetrate the application, penetrate the sql server, figure out the encryption method,use that method,and pull the data.

    easier for an insider with sysadmin, much more difficult for an uninformed but technically savvy outsider.

    if someone grabs a copy of the database, can the data be decrypted(ie credit card data?)

    You can protect the data in the database from people with sysadmin by doing the encryption in the application, instead of the database.

    then the vector at risk is can the application be compromised, and data extracted on a massive scale.

    you can't tell what type of encryption was used, just by looking at the varbinary data, so it takes at least some specific knowledge that might not be available to the nefarious person;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (2/19/2015)


    just encrypting a column is just a piece of the puzzle. that alone it protects from casual review by people who have db_datareader.

    If the only user account that hits the database has read access already via app and that's it, it will not. Yes, you can encrypt it anyway, but then what? I will have to give permissions to decrypt to same user again so the app won't break. So what's the point?

    I am not trying be polemic but wanted to hear comments on that case (single user for the whole app) which in my opinion, may need a 2nd type of user with lower permissions to make it more robust or move to a .NET encryption method.

    Yeah, I totally get the point that will protect if the whole database is moved, restores, etc, but I am talking in terms of app level access.

  • well that's a design issue you have to think about.

    in my case, our app user doesn't have access to the certificate.

    the sql user we created has db_datareader, db_datawriter, and execute. that's it.

    anything that is accessing the encrypted data gets sent through a procedure. the end user can execute it, but can't read the code or even identify WHICH certificate or encryption method is being used.

    so at least someone who is a user of the system, if they were to read the config file or get hold of the username and password of tthe application uses,could not clear text the data, but could execute the procedures, i guess.

    compromising a developer login on the dev box would get someone full access, of course. then you could create a query that uses the same encryption method, and yank everything out as clear text.

    here's a code example i slapped together.

    if not exists(SELECT * FROM sys.databases WHERE name = 'EncryptionExamples')

    EXEC('CREATE DATABASE EncryptionExamples;')

    GO

    IF NOT EXISTS(SELECT * FROM master.sys.server_principals WHERE type_desc = 'SQL_LOGIN' AND name = 'PlainOldTestUser')

    BEGIN

    PRINT 'Creating Login for PlainOldTestUser'

    CREATE LOGIN [PlainOldTestUser]

    WITH PASSWORD=N'59D5F192-5633-4E2B-B216-314FA6DE65B6',

    DEFAULT_DATABASE=[master],

    CHECK_EXPIRATION=OFF,

    CHECK_POLICY=ON;

    END;

    ELSE

    --exists, modify password!

    BEGIN

    PRINT 'modifying password for PlainOldTestUser'

    ALTER LOGIN [PlainOldTestUser] WITH PASSWORD = N'59D5F192-5633-4E2B-B216-314FA6DE65B6'

    END

    USE EncryptionExamples

    IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = 'PlainOldTestUser')

    BEGIN

    CREATE USER PlainOldTestUser FOR LOGIN PlainOldTestUser;

    EXEC sp_addrolemember 'db_datareader','PlainOldTestUser';

    EXEC sp_addrolemember 'db_datawriter','PlainOldTestUser';

    GRANT EXECUTE TO PlainOldTestUser;

    END;

    --#################################################################################################

    --Make sure there is a database master key.

    --database master key ALWAYS has an Id of 101

    --#################################################################################################

    IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

    BEGIN

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S3kretAcc3ss!123'

    END

    --#################################################################################################

    --create our certificate.

    --#################################################################################################

    IF NOT EXISTS(SELECT *

    FROM sys.certificates

    WHERE name = 'MyDataSecurityCertificate')

    BEGIN

    CREATE CERTIFICATE MyDataSecurityCertificate WITH SUBJECT = 'My DataSecurity Certificate', EXPIRY_DATE = '12/31/2024'

    PRINT 'MyDataSecurityCertificate Created'

    END

    ELSE

    BEGIN

    PRINT 'MyDataSecurityCertificate Already Exists.'

    END

    GO

    --now a table.

    CREATE TABLE [dbo].[Patient] (

    [PatientID] BIGINT IDENTITY(1,1) NOT NULL,

    [HospitalAccountNumber] VARCHAR(50) NULL,

    [FirstName] VARBINARY(max) NULL,

    [LastName] VARBINARY(max) NULL,

    [MiddleName] VARBINARY(max) NULL)

    INSERT INTO Patient ([HospitalAccountNumber],[FirstName],[LastName],[MiddleName])

    SELECT '1001' AS HospitalAccountNumber,

    EncryptByCert(Cert_ID('MyDataSecurityCertificate'), 'William') AS [FirstName],

    EncryptByCert(Cert_ID('MyDataSecurityCertificate'), 'Pitt')AS [LastName],

    EncryptByCert(Cert_ID('MyDataSecurityCertificate'), 'Bradley')AS [MiddleName]

    GO

    CREATE PROCEDURE GetPatient(@PatientID int)

    WITH EXECUTE AS OWNER

    AS

    SELECT [HospitalAccountNumber],

    Cast(DecryptByCert(Cert_ID('MyDataSecurityCertificate'), FirstName) AS VARCHAR(50)) AS [FirstName],

    Cast(DecryptByCert(Cert_ID('MyDataSecurityCertificate'), LastName) AS VARCHAR(50)) AS [LastName],

    Cast(DecryptByCert(Cert_ID('MyDataSecurityCertificate'), MiddleName) AS VARCHAR(50)) AS [MiddleName]

    FROM Patient

    GO

    EXECUTE GetPatient 1

    GO

    so that sets up a decently encrypted example.

    and here's a test of that user: you will see that the user cna see the encrypted rows, but can't decrypt without the procedure.

    this user could be locked down tighter by just granting SELECT,INSERT,UPDATE,DELETE on the dbo schema, instead of db_datareader.

    EXECUTE AS USER='PlainOldTestUser'

    select * from sys.tables

    select * from sys.sql_modules

    select * from sys.procedures

    SELECT * FROM sys.certificates

    SELECT * FROM Patient

    SELECT Cast(DecryptByCert(Cert_ID('MyDataSecurityCertificate'), FirstName) AS VARCHAR(50)) AS FirstName FROM Patient

    exec sp_helptext GetPatient

    EXECUTE GetPatient 1

    REVERT;

    edit: cleanup code:

    USE master;

    GO

    if exists(SELECT * FROM sys.databases WHERE name = 'EncryptionExamples')

    EXEC('DROP DATABASE EncryptionExamples;')

    GO

    DROP LOGIN PlainOldTestUser

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • @lowell

    Thanks a lot.

    Above is a very interesting piece of code. I definitely would like to experiment and try myself. I will try this at home at my own lab.

  • Thank you all for your help. I'm still little confused. I need to encrypt few tables (10-15) from the database. How should I go with it?

    Thanks again

  • amolacp (2/19/2015)


    Thank you all for your help. I'm still little confused. I need to encrypt few tables (10-15) from the database. How should I go with it?

    Thanks again

    You must encrypt column by column. You can't encrypt the whole table at least not with column level encryption.

    But my question would be, why do you need the whole table encrypted? Do you realize that encrypting all columns will also increase CPU?

  • sql-lover (2/19/2015)


    But in cases where the application or design uses a single user which of course, already has SELECT permissions against that table or column and Dev already uses same user with same permissions.

    Then, to be blunt, you need to fix your security first if you have anything confidential, because the current model is not adequate.

    If a hacker grant himself sysadmin, he will be able to see the sensitive data anyway

    If a hacker can gain sysadmin, you have larger problems than encryption. Like a major security problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/20/2015)


    Then, to be blunt, you need to fix your security first if you have anything confidential, because the current model is not adequate.

    Agree on that but my comment was based on that specific situation.

    As a DBA I would prefer to not keep sensitive data in a database, encrypt it, or partially store it, but we all know that's not always possible. Sometimes DBA just receive an app and we have to live and deal with what we have. We can make recommendations but we cannot go beyond that, not without having some decision making authority already on which case, we will not be just mere DBAs.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply