prevent sysadmin for access

Question

Post reply

prevent sysadmin for access

nuth

SSC-Addicted

Points: 473
More actions
June 10, 2015 at 5:31 pm

#318771

How to prevent sysadmin user for not to access the data in the database.?

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

snuthalapati (6/10/2015)
How to prevent sysadmin user for not to access the data in the database.?

You pretty much can't.

--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)

nuth SSC-Addicted Points: 473 More actions · Answer 2

Thank you for the reply...

How about any auditing kind???

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

snuthalapati (6/10/2015)
Thank you for the reply...
How about any auditing kind???

I believe you'd need something 3rd party like Guardium (if I got the name right).

--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)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

You encrypt the data client-side, and the users owns the encryption key out of reach for sysadmin. Values that needs to be keys, you hash.

As for auditing, there is SQL Server audit, but sysadmin can turn it off, although i believe it can be configured, so that it's difficult to hide the fact that it was turned off.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 5

You hire a trust worthy sysadmin and pay him well considering he has access to everything you do.

Sanz Hall of Fame Points: 3412 More actions · Answer 6

Why would you want to deny access to sysadmin ?

Sanz

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Sanz (6/17/2015)
Why would you want to deny access to sysadmin ?

Because a lot of companies make the huge mistake of allowing users and developers to have sysadmin privs. Only DBAs should have that priv and not all DBAs need the priv on larger teams. After all, "DBA" stands for "Database Administrator", not "System Administrator".

The other reason is because of the ever increasing rules placed on companies by various agencies for accountability, etc, etc, etc. "You can administer it but you shouldn't be able to even look at it, never mind change it" seems to be the mantra.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

So that they can't see data they shouldn't see. We have been seeing this question quite a bit more late ly on the forums.

IIRC, starting with SQL Server 2014 you can actually create custom roles that allow sys admins to do their work but not see the data. I haven't installed SQL Server 2014 yet so I haven't played with this yet.

Unfortunately, this doesn't help with earlier versions of SQL Server prior to SQL Server 2014.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Still, I continue to think it odd that possibly unqualified users can make changes through a GUI that might not have audit capabilities but DBAs aren't allow to see what the changes are. DBAs should be the gatekeepers of the data because, in truth, they're really the only ones that can and know how to.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Jeff Moden (6/17/2015)
Still, I continue to think it odd that possibly unqualified users can make changes through a GUI that might not have audit capabilities but DBAs aren't allow to see what the changes are. DBAs should be the gatekeepers of the data because, in truth, they're really the only ones that can and know how to.

I actually agree. When you hire System Administrators, Network Administrator, and DBAs, these individuals are given a level of trust because they have the keys to the kingdom. If they abuse that trust, you fire them.

What I think is happening is that because of the data breaches that have occurred and will probably occur in the future is that people want to restrict who can see the data as much as possible, even making it necessary to view the data through the appropriate applications and reports.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 11

Lynn Pettis (6/17/2015)
IIRC, starting with SQL Server 2014 you can actually create custom roles that allow sys admins to do their work but not see the data. I haven't installed SQL Server 2014 yet so I haven't played with this yet.

If you are sysadmin, you are sysadmin. But there are new permissions in SQL Server, so that people who only have CONTROL SERVER can be denied certain permissions. You also grant a user CONNECT ANY DATABASE and deny that user SELECT USER SECURABLES to permit an operator to backup a database.

The desire to hide data from an admin is certainly legit. If you have trade secrets in a database, why should a person see it, only because of technical skills?

The only solution is encryption with the encryption keys being held outside SQL Server.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

Lynn Pettis (6/17/2015)
Jeff Moden (6/17/2015)
Still, I continue to think it odd that possibly unqualified users can make changes through a GUI that might not have audit capabilities but DBAs aren't allow to see what the changes are. DBAs should be the gatekeepers of the data because, in truth, they're really the only ones that can and know how to.
I actually agree. When you hire System Administrators, Network Administrator, and DBAs, these individuals are given a level of trust because they have the keys to the kingdom. If they abuse that trust, you fire them.
What I think is happening is that because of the data breaches that have occurred and will probably occur in the future is that people want to restrict who can see the data as much as possible, even making it necessary to view the data through the appropriate applications and reports.

Agreed but I also agree with Erland... encryption someplace besides where the sysadmins have access to is really the best way.

--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)

nuth SSC-Addicted Points: 473 More actions · Answer 13

Thank you all for stopping by..

Need to check the feature in 2014..