SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Checking Permissions for Keys–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I got a call from someone wanted to check how permissions were stored for encryption objects. I ran a quick double check for them and decided to write this short post.

Let’s say that you create a few encryption keys. In my case, I’ll use this code to create a symmetric key, an asymmetric key, and a certificate.

CREATE SYMMETRIC KEY MySalaryProtector
WITH ALGORITHM = AES_256,
    IDENTITY_VALUE = 'Salary Protection Key',
    KEY_SOURCE = N'Keep this phrase a secr#t'
ENCRYPTION BY PASSWORD='Us#aStrongP2ssword';
GO

CREATE ASYMMETRIC KEY HRProtection
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'Use4SomeStr0ngP@ssword%^';

GO

CREATE CERTIFICATE MySalaryCert
ENCRYPTION BY PASSWORD = N'UCan!tBreakThis1'
WITH SUBJECT = 'Sammamish Shipping Records',
    EXPIRY_DATE = '20161231';
GO

I do this, I have these objects.

2016-11-29 14_21_19-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

Let’s now grant rights to these objects. I’ll use this code to grant CONTROL to a user.

GRANT CONTROL ON SYMMETRIC KEY::MySalaryProtector TO JoeDBA

GRANT CONTROL ON ASYMMETRIC KEY::hrprotection TO JoeDBA

GRANT CONTROL ON CERTIFICATE::MySalaryCert TO JoeDBA

Once I do this, I should see permissions, right? Let’s check.

2016-11-29 14_25_30-Database User - JoeDBA

I don’t see any permissions in the dialog above. That’s not exactly what I’d want to see. After all, if I’m trying to determine why a user can’t access a certificate, I’d want to know if they had rights here.

Instead of this, I need to use T-SQL, and check for specific classes in sys.database_permissions. Here’s the query looking for class 24 (symmetric keys), 25 (certificates) and 26 (asymmetric keys).

2016-11-29 14_27_57-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

You can see that I have permissions in here, and if I check the principal_id, I’ll find these are for my user. I could also join to database_principals and get specific information for my user.

2016-11-29 14_30_41-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

#SQLNewBlogger

This took a bit longer as someone asked me a question and I didn’t know the answer. I had to dig and read some documentation, but I found some answers and documented things myself.

Learned something, showed it, and hopefully will remember it from now on.


Filed under: Blog Tagged: encryption, SQLNewBlogger, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...