February 18, 2015 at 12:16 pm
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
February 18, 2015 at 1:02 pm
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
February 18, 2015 at 4:45 pm
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?
February 19, 2015 at 2:10 am
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
February 19, 2015 at 5:00 am
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.
February 19, 2015 at 5:29 am
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
February 19, 2015 at 9:29 am
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.
February 19, 2015 at 9:53 am
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
February 19, 2015 at 3:10 pm
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
February 19, 2015 at 9:22 pm
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?
February 20, 2015 at 1:46 am
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
February 21, 2015 at 9:02 am
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