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


How to Encrypt Database Objects in SQL Server 2008R2


How to Encrypt Database Objects in SQL Server 2008R2

Author
Message
Dev
Dev
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9376 Visits: 1602
azhar.iqbal499 (12/1/2011)
Thanks.
I just want to save my database from any user at live, In case if He would be able to reach the database then He must not be able to see the code of database objects.
So What security meausres should I take to acheive this goal.

Thanks


I believe you need a proper User Access management than encryption. I would restrict a user at following levels...
Server Login Level
Database User Level
Schema Level
Object Level
Object level Encryption (SP / View)
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56287 Visits: 17729
azhar.iqbal499 (12/1/2011)
In case if He would be able to reach the database then He must not be able to see the code of database objects.
So What security meausres should I take to acheive this goal.

Thanks

anybody granted access to the database will have public access which provides the ability to view certain objects. Exactly what is it you dont want users to see?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14579 Visits: 3904
The DBA of a production system will have rights to see all atabase objects.

If you use 'encrypted' stored procedures and functions, the DBA will not be able to view the contents of these in SSMS, but as Gail says this is just obfuscation and is trivial to reverse.

If this is an in-house application then you have next to zero justification for trying to hide your database objects from the production DBA. You just make your system less reliable to operate.

If this is a system that is to be sold, then normal practice is that you protect yourself with contractural agreements, not by trying to hide the internals of your system.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Sagesh
Sagesh
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 220
Use WITH Encryption Keyword with all the SQL objects while installing on the production system.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233005 Visits: 46361
Sagesh (10/11/2012)
Use WITH Encryption Keyword with all the SQL objects while installing on the production system.


As I mentioned earlier in this thread, that's not actually encryption and it's trivial to reverse.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


swoozie
swoozie
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 441
What you describe sounds more like you need to implement a strong role based security model not TDE encryption as a solution to your problem.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search