Does anybody know what I mean by this? Security question.

Question

Does anybody know what I mean by this? Security question.

Beatrix Kiddo

SSC-Dedicated

Points: 32407
More actions
September 3, 2014 at 7:52 am

#289746

This is a bit of a vaguely-worded question, but I am sure that at a training course I attended recently, either for SQL Server 2014 or 2012, the instructor said that there was a new feature (?) that allowed a user to operate with all of the sa/sysadmin permissions except the ability to see the data within the databases.
Does anybody know what I mean? I've tried to Google it but I'm drawing a blank. Basically, we (my employer) would like to be able to reassure our clients whose data we hold that in the course of a normal day, the DBAs who manage their databases aren't able to see their data (unless for a very specific reason, which would be audited).
Any ideas please? Is it just a question of setting up a user-defined server role with the right 'deny', or is there actually some new functionality above and beyond that?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Nope. A member of the Sysadmin role can see everything, cannot be denied any rights. A Windows Admin can be slowed down, but cannot be prevented from accessing the databases if they're really determined.

I suppose you could set up a login that has CONTROL SERVER permissions and DENY SELECT on all tables, but they'd be able to revoke the deny if they wanted to, if it'll even work. You can audit the permission changes, but then make sure the DBA/Sysadmin can't get at the audit data to change it

About the only way you can absolutely do that is to encrypt the data *before* it reaches the database and make sure that the encryption keys aren't stored on the server. Makes it harder to search data (can't search the encrypted columns)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 2

Damn, I did think it might be a bit too good to be true.

GilaMonster (9/3/2014)
I suppose you could set up a login that has CONTROL SERVER permissions and DENY SELECT on all tables, but they'd be able to revoke the deny if they wanted to, if it'll even work. You can audit the permission changes, but then make sure the DBA/Sysadmin can't get at the audit data to change it

This is what I was thinking we could do. I know it's not absolutely watertight but it would be an improvement on what we've got at the moment, which is pretty terrible. If the audit files go to share we (DBAs) don't have access to, it might be good enough.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I don't even know if it'll work though. Also may cause problems when you go to deploy scripts. SQL treats 'table doesn't exist' and 'permission denied' the same way. You may find that scripts you have to run (index maintenance or creation for example) fail.

Test carefully before you think about going that route. Maybe rather just audit all selects that the DBAs run (SQL Audit)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 4

Yes, I think it's going to be a full-on project rather than a quick fix. Could be interesting though.

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

I remember hearing something similar too when SQL 2014 was announced.

SQL 2014 introduces 3 new permissions:

CONNECT ANY DATABASE

IMPERSONATE ANY LOGIN

SELECT ALL USER SECURABLES

Which it's quoted in many places as saying will allow administrators to do their job without seeing the underlying data.

I've yet to see anywhere that actually explains how to do it though!

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

Found this: http://www.dbi-services.com/index.php/blog/entry/sql-server-2014-select-all-users-securables-a-db-admins

Short story, Grant Control Server & Deny Select All User Securables.

But the deny can be overridden by the grantee.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 7

With "CONNECT ANY DATABASE" we can basically let users to have visibility to all the databases but not the underlying data inside the databases. As Gail mentioned, there is no way you can "restrict" syadmins. Hurdles can be created, but syadmins can easily overcome the hurdles If they are knowledgeable on how and what to do.

Here is an useful article explaining couple of use case scenarios of new permissions which got introduced in 2014.

http://www.sqlservercentral.com/articles/Security/111116/

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

SreeSql (9/3/2014)
With "CONNECT ANY DATABASE" we can basically let users to have visibility to all the databases but not the underlying data inside the databases. As Gail mentioned, there is no way you can "restrict" syadmins. Hurdles can be created, but syadmins can easily overcome the hurdles If they are knowledgeable on how and what to do.
Here is an useful article explaining couple of use case scenarios of new permissions which got introduced in 2014.
http://www.sqlservercentral.com/articles/Security/111116/

Make it easier for others: http://www.sqlservercentral.com/articles/Security/111116/

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 9

Thanks, all. That's really helpful. I've got some reading to do but I'll report back when/if we implement this.