db_datareaders can decrypt data?

Hi... I want to encrypt data using a symmetric key + certificate, but it appears anyone with simply "db_datareader" can view the decrypted data? Is this correct?

Recreation steps:

1. As a System Admin, log into a SQL 2005 Database Engine.
2. Create some login that you also have access to. Ensure it does not already exist, or inherit any permissions from some existing NT-group. (From here on out, I will refer to it as the "underpriveleged user".)
3. Create a new database of any name.
4. Run the following query against the database:

CREATE TABLE [dbo].[MyTable](
[MyColumn] [varbinary](50) NULL
) ON [PRIMARY]
GO
CREATE USER [DOMAIN\UserName]
FOR LOGIN [DOMAIN\UserName] WITH DEFAULT_SCHEMA=[dbo]
sp_addrolemember 'db_owner', 'DOMAIN\UserName'

5. Now open another SSMS and connect as that underpriveleged user.
6. Change the database to the name you provided in Step 3.
7. Run these commands as the underpriveleged user...

CREATE MASTER KEY ENCRYPTION BY PASSWORD='DbMK_pwd'

CREATE CERTIFICATE MyCertificate WITH SUBJECT='Some Text'
CREATE SYMMETRIC KEY MyKeyName
WITH ALGORITHM = DESX
ENCRYPTION BY CERTIFICATE MyCertificate

OPEN SYMMETRIC KEY MyKeyName
DECRYPTION BY CERTIFICATE MyCertificate
INSERT INTO MyTable(MyColumn)
VALUES (EncryptByKey(Key_GUID('MyKeyName'), 'MyValue'))
SELECT Convert(varchar,DecryptByKey(MyColumn)) FROM MyTable
CLOSE SYMMETRIC KEY MyKeyName

8. Now switch back to the System Admin session, and revoke most of the underpriveleged account's permissions...

sp_droprolemember 'db_owner', 'DOMAIN\UserName'
GRANT SELECT ON dbo.MyTable TO [DOMAIN\UserName]

9. Switch once again to the underpriveleged session, and run the following:

OPEN SYMMETRIC KEY MyKeyName DECRYPTION BY CERTIFICATE MyCertificate
SELECT *, Convert(varchar,DecryptByKey(MyColumn)) FROM MyTable
CLOSE SYMMETRIC KEY MyKeyName

Why does this work? The user only has select access against the table, and no explicit permissions to the certificate or key. I have even disconnected as this user, tried again, restarted SQL Server... it is still able to decrypt the text.

Or what should I be doing to ensure only those with access to the key/certificate can decrypt the cipher text?

Based on your script, the behavior you are seeing is expected. When the low-privileged user was a high-privileged one (member of db_owner), she created both the certificate and symmetric key without specifying an explicit owner (AUTHORIZATION clause), therefore the owner of both objects became the creator (DOMAIN\userName).After the sysadmin removed the privileged role membership (sp_dropmember), the ownership of the objects remained the same. You can verify by running the following query:

SELECT name, user_name(principal_id) as key_owner FROM sys.symmetric_keys

SELECT name, user_name(principal_id) as cert_ownerFROM sys.certificates

To change the ownership, you can use the ALTER AUTHORIZATION statement, after you change the ownership you should notice that the low-privileged user can no longer open the symmetric key:

-- Running as sysadmin/dbo

ALTER AUTHORIZATION ON SYMMETRIC KEY::[MyKeyName] TO [dbo]

ALTER AUTHORIZATION ON CERTIFICATE::[MyCertificate] TO [dbo]

go

I hope this information helps.

-Raul Garcia

SDE/T

SQL Server Engine

|||Yes, that helps. Thanks!