Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Grant permission to access only 2 tables in a database Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 1:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Hi Lowell,

I created one database called encrypt_test1, in that I created one table called Customer_data.

I encrypted single column, called "AccountNumber" in table called Customer_data.

Now I want to restrict other users to run the following command.

These other users are my team mates, and they have same access as me on "sqlserver/sqlserver2008r2" server.

right now they can run the following command, and can see encrypted data into decrypted form.

USE encrypt_test1;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;

-- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO


i want to restrict them , so that they can not run the above select query and can not see the decrypted data/

Please Help.
thanks.
Post #1477224
Posted Wednesday, July 24, 2013 1:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 12,918, Visits: 32,085
Learner, it kind of sounds like these users are sysadmins? i don't think you can deny a sysadmin anything, so you have to take away their superpowers before you can do anything with them.

If you can't take away their superpowers, you are stuck, i think.

if they are not sysadmins, then some group they are in was given GRANT Symmetric Key Permissions (Transact-SQL), so you can simply explicitly deny them instead:


DENY Symmetric Key Permissions (Transact-SQL)


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477248
Posted Wednesday, July 24, 2013 1:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Thank Lowell!

Is it possible to restore the certifacate and symmetric key from backup location into the database's security folder into SQL SERVER 2008 R2?

thanks.
Post #1477250
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse