August 13, 2014 at 3:34 pm
Are there ways in SQL server to deny connecting to a instance? Basically i am looking for a way where data is accessed only through application , even the DBA's shouldn't have access to the data though they are sysadmins? Someone told me this is possible in SQL 2014? This is only for individual access, i know i can put some encryption in app layer.
August 13, 2014 at 3:40 pm
curious_sqldba (8/13/2014)
Are there ways in SQL server to deny connecting to a instance? Basically i am looking for a way where data is accessed only through application , even the DBA's shouldn't have access to the data though they are sysadmins? Someone told me this is possible in SQL 2014? This is only for individual access, i know i can put some encryption in app layer.
You can't lock sysadmins out of the database.
Why try to lock the DBAs out of the data? That makes no sense. Either you trust them or you don't. If you don't trust them, replace them.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2014 at 3:46 pm
An alternative may be to use the auditing features? You can't making it possible to fully administer the database while making it impossible to read the data, but can you at least make it impossible to look at data they shouldn't look at without leaving a trail?
August 13, 2014 at 4:27 pm
When you refer to a new fature of SQL2014 you might refer to the new server level permission "CONNECT ANY DATABASE" in combination with "VIEW SERVER STATE" and "DENY SELECT ALL USER SECURABLES" AND "DENY EXECUTE" to prevent the user to look at any data but still being able to perform "health checks".
But that's totally different to lock out sysadmins. You might want to reduce the sysadmin privileges for specific users and use the new permission. But you can't lock out a sysadmin. And you shouldn't either.
If you only allow access through an application, who would be responsible to write (and tune) any data access (e.g. stored procedure, views etc.)?
The only option to prevent admins to see the data is to encrypt the data at the application layer. But I'd vote against using that for each and every column of each and every table. It'll simply make the database almost impossible to maintain/tune. For sensitive data it's more than recommended. But not for the entire database.
This article might give you some ideas on how to assign tasks to dedicated user permissions.
August 13, 2014 at 4:48 pm
Anything you turn off on a sysadmin, they can turn back on. Any restriction you give them they can remove. That's the point of being a sysadmin. It's god level. The only way to deny someone's access to the database is to deny them sysadmin or deny them any form of connection to the server.
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
August 13, 2014 at 5:28 pm
Do you know the answer to my question? If auditing of all access is implemented can a sysadmin block tracking of their own activity?
August 13, 2014 at 5:52 pm
dan-572483 (8/13/2014)
Do you know the answer to my question? If auditing of all access is implemented can a sysadmin block tracking of their own activity?
Yes, they can. They can shut off the CDC2, which will be logged, their activities will then be hidden, and the CDC2 reactivation will be logged. You can also, theoretically, adjust the CDC log itself. You can hack a lot of a system when you've got sysadmin access to it.
It comes back to one of the above statements... if you don't trust your sysadmins, fire them and hire new ones. Someone's gotta have the keys. Either that, or you set it up and pray nothing ever goes wrong, because noone's going to be able to fix it besides simply reinstalling from scratch.
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
August 13, 2014 at 6:04 pm
Just an additional note on this: Any seriously competent sysadmin with physical access to the box, unless it has no interfaces (USB, CD Roms, etc), can bypass just about anything given time. I mean an hour or so, not months or something trying to hack in with decryption algorithms or the like.
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
August 13, 2014 at 8:30 pm
As a bit of a sidebar, I'm always amazed at questions like this. Why is it that so many people don't trust the people they specifically hired to be the ultimate guardians of the data? :blink:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2014 at 8:45 pm
Evil Kraig F (8/13/2014)
Just an additional note on this: Any seriously competent sysadmin with physical access to the box, unless it has no interfaces (USB, CD Roms, etc), can bypass just about anything given time. I mean an hour or so, not months or something trying to hack in with decryption algorithms or the like.
Ding ding ding +10
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply