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: Friday, June 12, 2015 1:31 PM
Points: 142, Visits: 286
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: Yesterday @ 12:51 PM
Points: 13,620, Visits: 34,779
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: Friday, June 12, 2015 1:31 PM
Points: 142, Visits: 286
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
Posted Tuesday, March 17, 2015 3:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 25, 2015 12:14 PM
Points: 38, Visits: 76
Hi Orlando,

If I need to give access more than 300 users only on two views, these views going to be used in SSRS and Tabular Model. So I need to implement security in Tabular level for more than 300 users, the script you have given is suitable in this case as well?
Many Thanks

Sangeeth
Post #1669066
Posted Tuesday, March 17, 2015 8:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 7,266, Visits: 13,087
Sangeeth878787 (3/17/2015)
Hi Orlando,

If I need to give access more than 300 users only on two views, these views going to be used in SSRS and Tabular Model. So I need to implement security in Tabular level for more than 300 users, the script you have given is suitable in this case as well?
Many Thanks

Sangeeth

Sure, follow the 3 steps I showed above.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1669163
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse