Restricting DBA from accessing SQL DB objects

Question

Restricting DBA from accessing SQL DB objects

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Oliiii (6/24/2011)
We've had the same question here a few month ago.
A provider asked us how they could hide all their design from us (dba), the short answer is they can't.
A dba motivated enough will see all your secrets.
As the other said, if you want to protect some IP then shove that piece in the application code.
If you want to protect your data from a DBA then encrypt it in the application.
The more you try to hide things the harder the life of your DBA, which might simply start recommending your soft be replaced by something else (exactly what happened here).

I have to agree with Oliii here. If you want to shove it on my servers, I get to see what it does. Otherwise, you host it and leave us out of it. You encrypt it, I'll unencrypt. You compile, I'll decompile.

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. Host it yourself if I can't make sure it's running clean. Equipment that vendors constantly tell us to throw at badly performing applications isn't cheap.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

hollynor256 (6/24/2011)
I'm a little bit confused I hope you can give me link for basic tutorial.

Sure my little marketing annoyance: www.HowNotToBeAShmuck.com

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004499 More actions · Answer 3

Craig Farrell (6/24/2011)
hollynor256 (6/24/2011)
I'm a little bit confused I hope you can give me link for basic tutorial.
Sure my little marketing annoyance: www.HowNotToBeAShmuck.com

Heh... I get a 403 error for that link. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

raistlinx Ten Centuries Points: 1345 More actions · Answer 4

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.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

bhawna.jain (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.

good luck, most developer controlled databases i've seen were a complete mess

MuskaanGoyal SSC Veteran Points: 277 More actions · Answer 6

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

Oliiii SSCertifiable Points: 5328 More actions · Answer 7

How would a DBA debug problem without having access to the structure?

If you want to keep your structure secret, make your application works over the internet and host the servers or provide a black box server you'll maintain yourself.

MuskaanGoyal SSC Veteran Points: 277 More actions · Answer 8

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. there would definitely be ways for DBA to access database objects. Anyways, I am just trying to either get to solution or to find something on net which proves that restricting DBA access is not possible in any database. Please help me out.

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 9

For PCs

CREATE TRIGGER [CONNECTION_LIMITED]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (host_name() in('PC201','PC301')
ROLLBACK;
END;

For Combination of PC's name and Application

CREATE TRIGGER [CONNECTION_LIMITED]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (host_name() in('PC201','PC301') and app_name() in('Microsoft SQL Server Management Studio','Microsoft SQL Server Management Studio - Query'))
ROLLBACK;
END;

For PCS Or User and Application

CREATE TRIGGER [CONNECTION_LIMITED]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (host_name() in('PC201','PC301') or
((original_login() = 'app' )
and app_name() in('Microsoft SQL Server Management Studio','Microsoft SQL Server Management Studio - Query'))
ROLLBACK;
END;

My Blog
www.aureus-salah.com

MuskaanGoyal SSC Veteran Points: 277 More actions · Answer 10

Logon trigger wont allow the DBA to access any other database on the same server means i am blocking server access instead of database access which is not the requirement.

Oliiii SSCertifiable Points: 5328 More actions · Answer 11

And triggers can be disabled by a DBA.

In SQL Server there is not way to restrict a motivated sa from seeing anything he wants to see unless it's encrypted outside SQL Server.

You can setup some audit though, and the regularly monitor it to see what the DBA did.

Although your audit has to be a little bit clever or it'll be easy to avoid.

MuskaanGoyal SSC Veteran Points: 277 More actions · Answer 12

can you detail me on how to encrypt all database objects (especially table structure-i have already encrypted views/triggers/functions/SP) in sql server so that the DBA wont be able to see their definition?

I am not even clear on the Audit that you have mentioned. Could you please explain it a little more?

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 13

Encrypting definitions can easily be decrypted by a DBA. There are plenty of tools out there that will do it:

Just give up the idea of preventing a sysadmin access to object definitions. It's can't be done as far as I know.

Lowell SSC Guru Points: 323518 More actions · Answer 14

you cannot encrypt a "table definition" (CREATE TABLE.....).. the column types and table name are stored in sys.tables and sys.comluns., and all the related obejects like constraints and indexes are in similar tables.

using the WITH ENCRYPTION definition for procs/functions just prevents casual review; any DBA can decrypt those...

for protecting data you can create a table, that instead of having a column CreditCardNumber (varchar(16), you have CreditCardNumber(varbinary(max), and store an encrypted binary string in the instead. the encryption could happen outside of SQL in some applicaitons code, or within SQL using some of the built in Encryption routines in 2005 and up.

--http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

-- EncryptByPassPhrase(@password,@DataToEncrypt )

select EncryptedData = EncryptByPassPhrase('PEPE', '123456789' )

-- DecryptByPassPhrase(@password,@DataToDecrypt )

declare @val varbinary(max)

SET @val = 0x010000004A298120E629F7F4DC4AD09EB0E380C9E60040BDAE1DB6D8DA98C2A4D249EB71

select convert(varchar(100),DecryptByPassPhrase('PEPE',@val))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Oliiii SSCertifiable Points: 5328 More actions · Answer 15

You can't encrypt the structure in a way that would keep it from the DBA.

It's a bad way to protect something and the best way to annoy your DBA.

For the audit you can get started here: http://technet.microsoft.com/en-us/library/cc280386.aspx