Decrypt permissions?

Howdy all. I just did the BOL example of encrypting a column of data:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7
6cda0adc.htm
and it worked great. However, Ive been trying to figure out how to let an
end user (someone with just read permissions) decrypt the data for a while
now to no avail. Can someone please assist? Also, I had to use "
WITH ALGORITHM = DES"
instead of what BOL said to to my version of SQL Server.
TIA, ChrisRChrisR wrote:
> Howdy all. I just did the BOL example of encrypting a column of data:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2
d7
> 6cda0adc.htm
> and it worked great. However, Ive been trying to figure out how to let an
> end user (someone with just read permissions) decrypt the data for a while
> now to no avail. Can someone please assist? Also, I had to use "
> WITH ALGORITHM = DES"
> instead of what BOL said to to my version of SQL Server.
> TIA, ChrisR
The end user can access the data through a stored proc that decrypts
the data for him. Stored procs are the preferred method for data access
whether or not the data is encrypted. Execute permission is all that
the user will need.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you David. Do you happen to know if it can be done without an SP or
not? We replicate a lot of data to a "reporting box", with the sole purpose
in mind of developers and power users being able to query their own data.
Thanks again.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1164063291.160874.187260@.b28g2000cwb.googlegroups.com...
> ChrisR wrote:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7[vbcol=seagreen]
an[vbcol=seagreen]
while[vbcol=seagreen]
> The end user can access the data through a stored proc that decrypts
> the data for him. Stored procs are the preferred method for data access
> whether or not the data is encrypted. Execute permission is all that
> the user will need.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||The whole ide of using an SP is to provide access to the data without
granting permissions to the base table. Can you avoid using an SP? Sure but
that means you will end up providing access to the encryption key and table
which is usually not a good thing to do if you are really concerned about
security.
You could tweak the SP to have parameter for the calling userID (assuming
you have some way to determine the rows for "permitted" users) and filter
the results with a where clause but that can be a sizeable perf hit if
you're dealing with large tables and/or many concurrent users. If they don't
mind since it's a reporting server, lucky you.
joe.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OVIC2SRDHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Thank you David. Do you happen to know if it can be done without an SP or
> not? We replicate a lot of data to a "reporting box", with the sole
> purpose
> in mind of developers and power users being able to query their own data.
> Thanks again.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1164063291.160874.187260@.b28g2000cwb.googlegroups.com...
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2
d7
> an
> while
>|||Thanks Joe. I understand the importance of security, but (unfortunately) the
powers that be want to be able to directly query their data. You brought up
a good point though that I hadn't thought of, passing out the encryption key
is a strong arguement, and won I can probably win.
Thanks!
"Joe Yong" <NO_jyong@.SPAM_scalabilityexperts.com> wrote in message
news:excTblRDHHA.4404@.TK2MSFTNGP03.phx.gbl...
> The whole ide of using an SP is to provide access to the data without
> granting permissions to the base table. Can you avoid using an SP? Sure
but
> that means you will end up providing access to the encryption key and
table
> which is usually not a good thing to do if you are really concerned about
> security.
> You could tweak the SP to have parameter for the calling userID (assuming
> you have some way to determine the rows for "permitted" users) and filter
> the results with a where clause but that can be a sizeable perf hit if
> you're dealing with large tables and/or many concurrent users. If they
don't
> mind since it's a reporting server, lucky you.
>
> joe.
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:OVIC2SRDHHA.4832@.TK2MSFTNGP06.phx.gbl...
or[vbcol=seagreen]
data.[vbcol=seagreen]
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d7[vbcol=seagreen]
let[vbcol=seagreen]
>|||Windows 200 Pro SP4.
SQL 2005 SP1.
I can get the decryption to work as me, but if I open up a new connection
with my test user, it returns NULL values instead of the decrypted data.
This is even if I create a proc and grant exec rights to the test user. Here
is precisely what I did:
USE AdventureWorks;
GO
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [AdventureWorks];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OF
F
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Can someone please assist?
TIA, ChrisR|||You're missing the "EXECUTE AS OWNER" or execute as something-or-other
clause in your create procedure statement. If you don't have that clause, it
will default to execute as caller which means any user executing that sproc
will be checked for permissions and if it isn't granted, you get null values
in your SELECT.
joe.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:ONry7jYDHHA.3524@.TK2MSFTNGP06.phx.gbl...
> Windows 200 Pro SP4.
> SQL 2005 SP1.
> I can get the decryption to work as me, but if I open up a new connection
> with my test user, it returns NULL values instead of the decrypted data.
> This is even if I create a proc and grant exec rights to the test user.
> Here
> is precisely what I did:
>
> USE AdventureWorks;
> GO
>
> --If there is no master key, create one now
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd1
17
> r$$#1946kcj$n44ncjhdlj'
> GO
>
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
>
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
>
> USE [AdventureWorks];
> GO
>
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
>
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
>
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
>
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
>
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> create procedure getDecryptedIDNumber
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
>
> /*works for me*/
> exec getDecryptedIDNumber
>
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=
OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
>
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
>
> /*Now, open up a "file/new/DB Engine Query" and login with the test
> login*/
>
> exec getDecryptedIDNumber
>
> /*This returns NULL values where it should show the decrypted data*/
>
> Can someone please assist?
>
> TIA, ChrisR
>
>|||Thanks Joe, but I just tried:
ALTER procedure [dbo].[getDecryptedIDNumber]
with execute as 'dbo'
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
use [AdventureWorks]
GO
GRANT IMPERSONATE ON USER::[dbo] TO [test]
GO
exec as user = 'dbo'
exec getDecryptedIDNumber
and it still comes back as NULL. Any other ideas?
"Joe Yong" <NO_jyong@.SPAM_scalabilityexperts.com> wrote in message
news:ONp1UqeDHHA.1196@.TK2MSFTNGP02.phx.gbl...
> You're missing the "EXECUTE AS OWNER" or execute as something-or-other
> clause in your create procedure statement. If you don't have that clause,
it
> will default to execute as caller which means any user executing that
sproc
> will be checked for permissions and if it isn't granted, you get null
values
> in your SELECT.
>
> joe.
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:ONry7jYDHHA.3524@.TK2MSFTNGP06.phx.gbl...
connection[vbcol=seagreen]
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd117[vbc
ol=seagreen]
CHECK_POLICY=OFF[vbcol=seagreen]
>