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

How to Encrypt Database Objects in SQL Server 2008R2 Expand / Collapse
Author
Message
Posted Thursday, December 1, 2011 11:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 2,013, Visits: 1,584
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)


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1215146
Posted Friday, December 2, 2011 2:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,162, Visits: 13,305
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"
Post #1215203
Posted Monday, December 19, 2011 10:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174
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 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1223929
Posted Thursday, October 11, 2012 6:19 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:56 AM
Points: 112, Visits: 200
Use WITH Encryption Keyword with all the SQL objects while installing on the production system.
Post #1371407
Posted Thursday, October 11, 2012 6:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #1371418
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse