views:

273

answers:

1

I'm trying to create a symmetric key in SQL Server 2005 and restrict it to just the sa and application user accounts. All other accounts should get an error when trying to open it.

Here's how I'm creating it. I changed the names for the purposes of this question.

CREATE CERTIFICATE My_Certificate
  WITH SUBJECT = 'My Cert Subject';
GO

CREATE SYMMETRIC KEY My_Key
  WITH ALGORITHM = AES_256
  ENCRYPTION BY CERTIFICATE My_Certificate;
GO

At this point, the key can be opened by my app user even though I haven't given it permission. If I DENY the [public] group, I can't open the key even after I later GRANT it directly to the app user. I don't want to DENY each user individually because I don't know what users exist in Production. I also want any new users that get created to not have access to this key, either.

The DENY and GRANT lines I'm using are:

DENY VIEW DEFINITION ON SYMMETRIC KEY :: My_Key TO [public]
GRANT VIEW DEFINITION ON SYMMETRIC KEY :: My_Key TO [AppUser]
A: 

Who owns the keys? How does your app accesses the keys? Is ownership chaining playing any role by any chance? What groups and roles does AppUser belong to?

What permission does fn_my_permissions('My_Key', 'SYMMETRIC KEY') return when executed by the AppUser (right after the key is created) ?

Update: fixed typo in fn_my_permissions securable type

Remus Rusanu
I created the key with my own account. I'm opening the key at the beginning of the stored procedure and either encrypting or decrypting as seen here: http://msdn.microsoft.com/en-us/library/ms179331.aspxAppUser belongs to the public server role and is db_owner,public on the database itself. fn_my_permissions() returns no results.
AndyMcKenna
So AppUser is db_owner? That would grant him access to everything in the db, wouldn't it?
Remus Rusanu
Yeah and I want him to have access to it. I just need to lock everyone else out. I'm going to try to create the key as the AppUser and see if my personal account has access to it. I'll make sure I'm also not a db_owner.
AndyMcKenna
That was it. All of my testing was with db_owners. D'oh!
AndyMcKenna
And double D'oh! on my part too, the fn_my_permissions takes 'SYMMETRIC KEY', not 'SYMMETRIC_KEY', that's why the output was empty. I fixed my post.
Remus Rusanu