July 1, 2011 at 12:33 pm
raistlinx (6/25/2011)
You sold me the rights to use your software. I can't resell it, but I'm damned well sure I'm not going to let it blindly thrash around in my server like a drunken eel if, like most vendors, I've gotten a half-tested piece of trash that barely performs as advertised.
Loved that...
This thread sounds to me like a shop where the "DBA's" are not DBA's but script monkeys at the whim of the development team, but they give them the DBA title anyway.
Ouch ... that was border line rude but I have to agree it describes many real world scenarios 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2011 at 12:39 pm
MuskaanGoyal (7/1/2011)
I agree on this but my senior is not understanding the same thing. He told me that same is possible in Oracle using TOAD. Meaning we can restrict DBA from accessing database objects in oracle through TOAD which I don't think is 100% possible....
...and you are correct, TOAD is a third party product and no serious Oracle DBA uses it for critical production work.
Oracle's approach to this issue is encapsulated on "Vault" technology - lots of info about it vault on the net. As far as I know SQL Server has nothing resembling Vault - please correct me if I'm wrong.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2011 at 12:43 pm
MuskaanGoyal (6/24/2011)
I have already applied cell level encryption. But I don't want DBA to see the definition or modify any object. We are blocking DBA access for the security of the product.
Can't be done.
All it takes is stopping the SQL service, copying the mdf and ldf files to another instance of SQL Server, and connecting to that instance as a sysadmin to attach them there. Login triggers, et al, won't even slow someone down from doing this, much less stop them.
You can encrypt the stored procedure definitions, but even Microsoft says that's not a really valid security procedure. FAR too easy to bypass by anyone who has even half a clue about SQL Server.
And none of that will hide table structures, column names, column data types, index definitions, primary and foreign keys, constraints, et al. Not from anyone who knows what they're doing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2011 at 12:55 pm
MuskaanGoyal (6/24/2011)
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
That sounds like an amazing project - please tell us the name of your product so that we can keep an eye out for it. Not to avoid it at all costs, no, no, no!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 1, 2011 at 12:58 pm
mister.magoo (7/1/2011)
That sounds like an amazing project - please tell us the name of your product so that we can keep an eye out for it. Not to avoid it at all costs, no, no, no!
😀 You made my day Mr Magoo.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2011 at 1:00 pm
PaulB-TheOneAndOnly (7/1/2011)
mister.magoo (7/1/2011)
That sounds like an amazing project - please tell us the name of your product so that we can keep an eye out for it. Not to avoid it at all costs, no, no, no!😀 You made my day Mr Magoo.
Well, honestly - I have truly never heard anything so ridiculous outside of the mobile phone market - which by the way winds me up massively - but that's another story.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 20, 2011 at 12:56 pm
GSquared (7/1/2011)
MuskaanGoyal (6/24/2011)
I have already applied cell level encryption. But I don't want DBA to see the definition or modify any object. We are blocking DBA access for the security of the product.Can't be done.
All it takes is stopping the SQL service, copying the mdf and ldf files to another instance of SQL Server, and connecting to that instance as a sysadmin to attach them there. Login triggers, et al, won't even slow someone down from doing this, much less stop them.
You can encrypt the stored procedure definitions, but even Microsoft says that's not a really valid security procedure. FAR too easy to bypass by anyone who has even half a clue about SQL Server.
And none of that will hide table structures, column names, column data types, index definitions, primary and foreign keys, constraints, et al. Not from anyone who knows what they're doing.
Don't even need to stop the server. Backup / restore.
Or use the san to copy the files :-D.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply