I’ve spent the last week or two looking at the encryption features of SQL Server 2005 and finally think I have a handle on how to use it to keep data secure. Its not the encryption or decryption thats the issue, its securing access to the keys that requires the effort.
At the moment I’ve only created a stored proc to insert some fake data into a table, next step is reading it out. But I think the approach is sound and has two main points:
- Permissions come from execution context, not ownership.
- Execution context elevates, temporarily, to perform actions that need additional permissions.
In addition to the encryption features I’ve also taken advantage of the ‘Execute As’ clause, module signing and ‘Without Login’ users. And I’ve created a pretty picture to show the architecture:
When a store procedure is called the execution context is the login (and mapped user account) the application used to connect to the server. The procedure is defined to run as another user, through the ‘Execute As’ clause changing the execution context. By signing the procedure with a Certificate, and creating a user in the DB to represent the certificate as a principle, the permissions of the certificate principle are added to the ‘Execute As’ user for the duration of the call.
In my example the permissions associated with the original call are replaced with those of a least privilege user who has no role memberships or permissions. These are then elevated by those of the signing user for the duration of the call.
The execution context is separated from ownership, I have separate (’Without Login’) users owning the Data, the Encryption and the access methods. Ownership of the data does not imply access to the keys needed to decrypt it, nor does ownership of the encryption keys confer access to the data. The access methods are further separated to avoid accidental ownership chaining granting access to data or keys.
There will be only one user context that will have access to both the data and the keys: the signing user. The signing user cannot login or be impersonated; and the signing certificate can be configured to require an external password to sign a module.
The first step is to setup the db, users, keys and the table:
create database secure
go
use secure
go
-- we need a db master key
create master key encryption by password = 'pword'
-- verify key is there
select * from sys.symmetric_keys
-- CREATE USERS
-- this user will be the one the application connects as
create login app_user with password = 'pword'
create user app_user from login app_user
-- this user will own all the encryption objects
create user encryption_owner without login
-- this user will own all of the data objects (tables and views)
create user data_owner without login
-- this user will own all of the access objects (procs, triggers, functions)
create user access_owner without login
-- this user will act as a least privilege user context
-- for code to execute as
create user lpu_code_context without login
-- this user will act as an elevated privilege user context we
-- can assign permissions directly to
create user elevated_code_context without login
-- CREATE KEYS FOR ENCRYPTING DATA
-- create an asymmetric key to protect symmetric keys in the system
-- the key is owned by the encryption_owner user not the sa
create asymmetric key
encryption_root
authorization
encryption_owner
with
algorithm = rsa_512
-- verify key is there
select * from sys.asymmetric_keys
-- create a symmetric key to encrypt data with
-- the symmetric key is protected by the asymmetric root key
-- the key is owned by the encryption_owner user not the sa
create symmetric key
cc_key
authorization
encryption_owner
with
algorithm = aes_256
encryption by
asymmetric key encryption_root
-- verify key is there
select * from sys.symmetric_keys
-- CREATE OBJECTS USED TO SIGN CODE
-- create a certificate to sign code with
create certificate
internal_code
authorization
encryption_owner
with
subject = 'Internal code signing certificate'
-- create a user to represent the certificate as a user,
-- we will need this so we can assign permissions to code signed by the cert
create user
signed_internal_code
from
certificate internal_code
-- CREATE SCHEMAS TO STORE OBJECTS IN
-- creating one each for data and access to more easily seperate the ownership between them
create schema
sales_data
authorization
data_owner;
create schema
sales_api
authorization
access_owner;
--
-- CREATE TABLES
create table sales_data.cc_trans
(
id int identity(1,1) not null primary key,
amount money not null,
occurred datetime not null,
name_on_card varchar(200),
card_number varbinary(max)
)
Now we have a table and some other objects in place, lets create the stored procedure needed to insert data into it and give the application user permission to call it:
-- create a stored procedure to insert a transaction record
-- that runs under the least privilege code context and elevates it's
-- permissions through signing.
-- note that encryption of the proc and using salt with the encryption would be a good idea
use secure
go
IF OBJECT_ID ( 'sales_api.usp_insertCCTrans', 'P' ) IS NOT NULL
DROP PROCEDURE sales_api.usp_insertCCTrans;
go
create procedure sales_api.usp_insertCCTrans
@amount money,
@occurred datetime,
@name_on_card varchar(200),
@card_number varchar(50)
with
execute as 'lpu_code_context'
as
begin
print 'user name =' + user_name()
--open the symmetric key to encrypt the card number
begin try
open symmetric key
cc_key
decryption by
asymmetric key encryption_root
insert into sales_data.cc_trans
(amount, occurred, name_on_card, card_number)
select
@amount,
@occurred,
@name_on_card,
EncryptByKey(key_guid('cc_key'), @card_number)
end try
begin catch
if (exists (select * from sys.openkeys where key_name = 'cc_key'))
begin
close symmetric key cc_key
end
print 'ERROR: ' + cast(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
end catch
end
go
-- grant the app_user permission to exec the proc
grant execute on object::sales_api.usp_insertCCTrans to app_user
go
Test calling the insert procedure and build up the permissions needed for the signing user:
use secure;
go
-- run the insert procedure as the app user
-- prints an error that the key cannot be opened
execute as user ='app_user'
exec sales_api.usp_insertCCTrans 10.23, '4 march 2008', 'bob', '5555-5555-5555-5555'
revert
-- sign the procedure using the code signing certificate
add signature to
sales_api.usp_insertCCTrans
by
certificate internal_code
-- grant the signed code user permission to open the
-- symmetric key which also requires control permission on the
-- asymmetric root key
grant
control
on
asymmetric key::encryption_root
to
signed_internal_code
grant
view definition
on
symmetric key::cc_key
to
signed_internal_code
-- run the insert procedure as the app user again
-- prints an error insert permission was denied on the table cc_trans
execute as user ='app_user'
exec sales_api.usp_insertCCTrans 10.23, '4 march 2008', 'bob', '5555-5555-5555-5555'
revert
-- grant the signed internal code user permission to
-- select, insert, update and delete from tables in the sales_data schema
grant
select, insert, update, delete
on
schema::sales_data
to
signed_internal_code
-- run the insert procedure as the app user again
-- this works
execute as user ='app_user'
exec sales_api.usp_insertCCTrans 10.23, '4 march 2008', 'bob', '5555-5555-5555-5555'
revert
-- view the data to confirm
select * from sales_data.cc_trans
-- test the access to the data the user roles have
-- the application user, permission denied
execute as user = 'app_user'
select * from sales_data.cc_trans
revert
-- the owner of the encryption objects, permission denied
execute as user = 'encryption_owner'
select * from sales_data.cc_trans
revert
-- the owner of the stored procs, permission denied
execute as user = 'access_owner'
select * from sales_data.cc_trans
revert
-- the owner of the data, permission allowed
-- but the owner cannot open the keys necessary to decrypt the data
-- (tested below)
execute as user = 'data_owner'
select * from sales_data.cc_trans
revert
-- the least privilege code context, permission denied
execute as user = 'lpu_code_context'
select * from sales_data.cc_trans
revert
-- the signed internal code user, error occurs:
-- Cannot execute as the database principal because the principal
-- "signed_internal_code" does not exist, this type of principal
-- cannot be impersonated, or you do not have permission.
execute as user = 'signed_internal_code'
select * from sales_data.cc_trans
revert
-- test the data_owner user cannot open the keys necessary to decrypt the data
-- fails as the user does not have permission
execute as user = 'data_owner'
open symmetric key
cc_key
decryption by
asymmetric key encryption_root
revert
-- remove the signature on the proc and see what happens
drop signature from
sales_api.usp_insertCCTrans
by
certificate internal_code
-- fails as the symmetric key cannot be opened
-- the code is now running as the least privilege user
execute as user ='app_user'
exec sales_api.usp_insertCCTrans 10.23, '4 march 2008', 'bob', '5555-5555-5555-5555'
revert
--even if we run this method as the sa/dbo user it does not have the permission needed
-- this call fails
execute as user = 'dbo'
print 'calling user name = ' + user_name()
exec sales_api.usp_insertCCTrans 10.23, '4 march 2008', 'bob', '5555-5555-5555-5555'
revert
-- put the signature back
add signature to
sales_api.usp_insertCCTrans
by
certificate internal_code
Rather than signing the procedure we could have defined it to Execute As a user who has the permissions needed, so lets try that:
use secure;
go
-- create a stored procedure to insert a transaction record
-- that runs under the elevated code context
-- note that encryption of the proc and using salt with the encryption would be a good idea
IF OBJECT_ID ( 'sales_api.usp_insertCCTrans2', 'P' ) IS NOT NULL
DROP PROCEDURE sales_api.usp_insertCCTrans2;
go
create procedure sales_api.usp_insertCCTrans2
@amount money,
@occurred datetime,
@name_on_card varchar(200),
@card_number varchar(50)
with
execute as 'elevated_code_context'
as
begin
print 'user name =' + user_name()
--open the symmetric key to encrypt the card number
begin try
open symmetric key
cc_key
decryption by
asymmetric key encryption_root
insert into sales_data.cc_trans
(amount, occurred, name_on_card, card_number)
select
@amount,
@occurred,
@name_on_card,
EncryptByKey(key_guid('cc_key'), @card_number)
end try
begin catch
if (exists (select * from sys.openkeys where key_name = 'cc_key'))
begin
close symmetric key cc_key
end
print 'ERROR: ' + cast(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
end catch
end
go
-- grant the app_user permission to exec the proc
grant execute on object::sales_api.usp_insertCCTrans2 to app_user
go
And now test it:
use secure;
go
-- run the insert procedure as the app user
-- prints an error that the key cannot be opened
execute as user ='app_user'
exec sales_api.usp_insertCCTrans2 10.23, '4 march 2008', 'fred', '5555-5555-5555-5555'
revert
-- grant the elevated user context permission to open the
-- symmetric key which also requires control permission on the
-- asymmetric root key
grant
control
on
asymmetric key::encryption_root
to
elevated_code_context
grant
view definition
on
symmetric key::cc_key
to
elevated_code_context
-- and give the user select, insert, update, delete perms on the schema
grant
select, insert, update, delete
on
schema::sales_data
to
elevated_code_context
-- run the insert procedure as the app user again
-- works now
execute as user ='app_user'
exec sales_api.usp_insertCCTrans2 10.23, '4 march 2008', 'fred', '5555-5555-5555-5555'
revert
-- view the data to confirm
select * from sales_data.cc_trans
-- test the access to the data the user roles have
-- the application user, permission denied
execute as user = 'app_user'
select * from sales_data.cc_trans
revert
-- the owner of the encryption objects, permission denied
execute as user = 'encryption_owner'
select * from sales_data.cc_trans
revert
-- the owner of the stored procs, permission denied
execute as user = 'access_owner'
select * from sales_data.cc_trans
revert
-- the owner of the data, permission allowed
-- but the owner cannot open the keys necessary to decrypt the data
-- (tested below)
execute as user = 'data_owner'
select * from sales_data.cc_trans
revert
-- the elevated code context, permission allowed
execute as user = 'elevated_code_context'
select * from sales_data.cc_trans
revert
-- test the access to the encryption keys the accounts that have access
-- to the data have
-- fails, data_owner cannot open the key
execute as user = 'data_owner'
open symmetric key
cc_key
decryption by
asymmetric key encryption_root
close all symmetric keys
revert
-- success, the elevated code context has access to the keys and the data
-- anyone with the ability to impersonate the user can
-- now access and decrypt the data
execute as user = 'elevated_code_context'
open symmetric key
cc_key
decryption by
asymmetric key encryption_root
close all symmetric keys
revert
That sort of works the same as a signed module, however its possible to impersonate the elevated user and get access to both the data and the encryption keys so I think code signing is a more secure solution. But there is a need to protect the code signing certificate, this can be done by encrypting it with a password that is not stored in the db or the master db as follows.
-- lets see what happens when the certificate private key
-- is protected with a password
alter certificate
internal_code
with private key
(encryption by password = 'pword')
-- check we can still run the usp
-- success
execute as user ='app_user'
exec sales_api.usp_insertCCTrans 10.23, '7 march 2008', 'bob', '5555-5555-5555-5555'
revert
select * from sales_data.cc_trans
-- check we can sign a proc without the signature
drop signature from
sales_api.usp_insertCCTrans
by
certificate internal_code
-- this fails, the password is needed
-- so the only way to get code signed by the certificate so that
-- it can run under the user with the has both data and encryption is
-- to know the password which is not in the db.
add signature to
sales_api.usp_insertCCTrans
by
certificate internal_code
-- this works
add signature to
sales_api.usp_insertCCTrans
by
certificate internal_code with password = 'pword'
alter certificate
internal_code
with private key
(decryption by password = 'pword')
There may be holes in this design but for now it seems ok to me.
Post a Comment