Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bhawna
Starting Member
2 Posts |
 Posted - 2011-06-24 : 06:12:18
|
| Hi, I have a requirement to restrict everyone (including DBA) from accessing user defined sql server database objects (like tables/function/views/stored procedures triggers etc). No one should be able to select/modify/delete/drop/disable these database objects from any sql client. These should only be accessible from application. I searched on net and found following:1. logon triggers : this is a server level trigger which blocks the user (you can modify to block dba well) to logon on the database server. But the same server may have other application database as well and using this user wont be able to login to other db as well. so this is out of scope.2. DENY permission: as per my R&D results, we cant apply deny permission on sa / dba. let me know if this is possible and how.3. DB level trigger: we can create database level triggers and roll back the action if any of above mentioned event occur. but DBA can disable the trigger can can access db objects after that.4. sp_addextendedproperty: using this stored procedure, we can hide user defined tables in SSMSE. but no control over command line. The DBA can login through sql prompt and can execute commands to view table structure and other objects. Does anyone have any idea on how to restrict any user (especially DBA) from accessing sql server database objects. PS: I am using SQL Server 2005/2008.Thanks,Bhawna |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-24 : 06:32:28
|
| What do you think the DBA's job is. Easiest option is to just sack them - they should resign anyway.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
bhawna
Starting Member
2 Posts |
Posted - 2011-07-01 : 07:07:38
|
| Can we encrypt table structure in Sql server? Encryption should be password based and admin should not be able to decrypt it without that password. if yes then how?Thanks,Bhawna |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-07-01 : 07:45:05
|
quote:
I have a requirement to restrict everyone (including DBA) from accessing user defined sql server database objects (like tables/function/views/stored procedures triggers etc). No one should be able to select/modify/delete/drop/disable these database objects from any sql client. These should only be accessible from application.
Wait. WHAT?The only way you want to modify anything is through your application????? seriously?What you are saying is:"I only want hackers to be able to modify my stuff. All sysadmin should be done through SQL injection." -- When something goes wrong and all my data is stolen I want it to be *AS HARD AS POSSIBLE* for the (presumably) highly paid professionals in my company to find out what's happened and fix it..........................................gahCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-01 : 07:46:54
|
| Yes you can encrypt data and the code held on the server.The issue is that applications will have to access the data and you would need to stop the admin getting access to the method used as it would be easy to replicate and view the data.Encrypting the database code (SPs, views) is an issue because of the risk of losing access - there have been a number of people who have encrypted code and then lost it because they lost acces to their code repository and couldn't access the objects from backups.Why do you want to prevent people who need to support the system from being able to see it?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Restricting DBA from accessing SQL DB objects