Hiding payroll data from admin

Question

Post reply

Hiding payroll data from admin

jsqldba

SSChampion

Points: 11257
More actions
April 21, 2010 at 7:20 am

#234125

we have a new payroll system coming online using SQL 2005 and questions have come up regarding security, specifically securing the sysadmin(s) from seeing the salary data.
what is the best practice to hide payroll database information while giving admins the access they need to do their work?
thanks

Viewing 15 posts - 1 through 15 (of 17 total)

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

GregoryF SSCertifiable Points: 6296 More actions · Answer 1

Use SQL 2008 and use TDE.

But since you have settled to 2005, use a third party tool to encrypt it.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Toby White SSCertifiable Points: 6187 More actions · Answer 2

What system, application, and/or users DO need to see the salary data? If you rely on the SQL engine to decrypt the data, then you cannot avoid sysadmin from being able to decrypt the data. However, if you rely on a 3rd party to encrypt the data then you can control which applications can decrypt the data.

My suggestion is to place the data on a SQL server that you can take everybody out of the sysadmin role on except for the dba and then rely on SQL Server to encrypt/decrypt the salary data. This leaves the dba with the ability to read the salary data as well as grant access to it. DBA's are in charge of data so it's a necessary risk that your company should take.

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

Toby White (4/21/2010)
What system, application, and/or users DO need to see the salary data? If you rely on the SQL engine to decrypt the data, then you cannot avoid sysadmin from being able to decrypt the data. However, if you rely on a 3rd party to encrypt the data then you can control which applications can decrypt the data.
My suggestion is to place the data on a SQL server that you can take everybody out of the sysadmin role on except for the dba and then rely on SQL Server to encrypt/decrypt the salary data. This leaves the dba with the ability to read the salary data as well as grant access to it. DBA's are in charge of data so it's a necessary risk that your company should take.

The individual hired to be a DBA should be a trusted resource. If you can't trust the DBA (and they can sometimes be untrustworthy, but the majority of us are quite trustworthy), who can you trust?

Toby White SSCertifiable Points: 6187 More actions · Answer 4

The individual hired to be a DBA should be a trusted resource. If you can't trust the DBA (and they can sometimes be untrustworthy, but the majority of us are quite trustworthy), who can you trust?

Well said Lynn.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

GregoryF (4/21/2010)
Use SQL 2008 and use TDE.
But since you have settled to 2005, use a third party tool to encrypt it.

TDE does not hide it from the admins.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

Can't be done.

Or more accurately, you can't be sure that it's working. You can fumble through security to prevent sysadmins from seeing a table, but they can change that security, so you're back where you started. You can set up auditing, but they can remove it, and put it back after they've seen the table.

If the application encrypts the data, sends it to SQL encrypted, and decrypts it, likely the sysadmins won't easily get it, but when someone asks for support, or help, often they give up their pwd instantly to a DBA.

Stop worrying about it, tell your boss it can't be done. Tell the DBA if he talks about it, he's fired.

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

Lynn Pettis (4/21/2010)
Toby White (4/21/2010)
What system, application, and/or users DO need to see the salary data? If you rely on the SQL engine to decrypt the data, then you cannot avoid sysadmin from being able to decrypt the data. However, if you rely on a 3rd party to encrypt the data then you can control which applications can decrypt the data.
My suggestion is to place the data on a SQL server that you can take everybody out of the sysadmin role on except for the dba and then rely on SQL Server to encrypt/decrypt the salary data. This leaves the dba with the ability to read the salary data as well as grant access to it. DBA's are in charge of data so it's a necessary risk that your company should take.
The individual hired to be a DBA should be a trusted resource. If you can't trust the DBA (and they can sometimes be untrustworthy, but the majority of us are quite trustworthy), who can you trust?

It might be for something like SOX compliance which, depending on the nature of business, can castrate even the most trustworthy, bondable DBA's in the world.

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

GregoryF SSCertifiable Points: 6296 More actions · Answer 8

Jeff Moden (4/21/2010)
Lynn Pettis (4/21/2010)
Toby White (4/21/2010)
What system, application, and/or users DO need to see the salary data? If you rely on the SQL engine to decrypt the data, then you cannot avoid sysadmin from being able to decrypt the data. However, if you rely on a 3rd party to encrypt the data then you can control which applications can decrypt the data.
My suggestion is to place the data on a SQL server that you can take everybody out of the sysadmin role on except for the dba and then rely on SQL Server to encrypt/decrypt the salary data. This leaves the dba with the ability to read the salary data as well as grant access to it. DBA's are in charge of data so it's a necessary risk that your company should take.
The individual hired to be a DBA should be a trusted resource. If you can't trust the DBA (and they can sometimes be untrustworthy, but the majority of us are quite trustworthy), who can you trust?
It might be for something like SOX compliance which, depending on the nature of business, can castrate even the most trustworthy, bondable DBA's in the world.

My last job and my current position have been in the banking and brokerage industries, and we do not encrypt account/SSN/name/account value, etc info...however, we do audit the hell out of sysadmins.

Once, we did decline to support a database because the regulatory requirements required that admins not have access to the data, but that was HIPPA regulations, not SOX.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

jsqldba SSChampion Points: 11257 More actions · Answer 9

I had this same issue years ago (I'm the DBA) when taking the old payroll system (SQL 2000) under responsibility and with the new system and new(er) version of SQL, wondering if things have changed.

I argued the same then - that the DBA by virtue of the job responsibilities has to be a trusted resource. Yet, management (with pressure from HR) decided to fully isolate the payroll server from all admins. Only an external consultant (from the payroll software company) has direct access. In order for me to check anything, I have to go through him to get access. Crazy huh?

Anyways, I will certainly revisit these trust issues with my new boss and HR again. Thank again for the replies.

Toby White SSCertifiable Points: 6187 More actions · Answer 10

Anyways, I will certainly revisit these trust issues with my new boss and HR again.

You can see you have some heavy hitters weighing in this subject (not so much myself, but the others). Judging by your words I expect you will receive significant push back, and my suggestion is don't fold. Make the case to your business that the SQL experts made on your thread. These are best practices that these SQL experts are talking about, and they are best practices for good business not for DBA control/power/anything else. I would challenge the business users/managers with this - your goal is good business, what is theirs?

I'm not saying lose your job over it, but rather do your best to make them understand why this is the best approach for the business. If they still choose something else then you can support that knowing you did your best.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 11

If they want to pay a consultant to handle this, let them. I think it's nice to not have access to sensitive information myself. Just prevents issues.

I'd work with the consultant, make sure he sets up the server according to your standards, and let HR be responsible. Works for me.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 12

It can't be done. Here's why...

If I have admin rights on a server, I have debugging rights. If I have debugging rights, I can see the memory and I can break in to processes. Therefore, if I have admin rights on the server where the data is being encrypted, I can see the data unencrypted. Likewise, if I have admin rights on a system where the data can be viewed unencrypted... like the HR workstation, I can see the data unencrypted. This all may make it more challenging for me, but it doesn't stop me. They did consider the fact that the workstation admins have control over the HR systems, right?

K. Brian Kelley
@kbriankelley

webrunner SSC-Dedicated Points: 31882 More actions · Answer 13

K. Brian Kelley (4/22/2010)
It can't be done. Here's why...
If I have admin rights on a server, I have debugging rights. If I have debugging rights, I can see the memory and I can break in to processes. Therefore, if I have admin rights on the server where the data is being encrypted, I can see the data unencrypted. Likewise, if I have admin rights on a system where the data can be viewed unencrypted... like the HR workstation, I can see the data unencrypted. This all may make it more challenging for me, but it doesn't stop me. They did consider the fact that the workstation admins have control over the HR systems, right?

Can all of this be done while also defeating any auditing? It seems to me from dipping into this thread that heavy auditing would handle most of the cases where a DBA is trusted to the data, and even cases where attempts are made to encrypt the data from a DBA who knows how to spy on the data anyway, but only if the auditing itself could not be tampered with. I'm curious if the scenario you describe above would be mitigated by having auditing in place that would allow a third party to detect the user issuing commands to view the memory or the unencrypted data.

I know that in most of these organizations, just knowing that the DBA was at fault would be small consolation if the data was stolen, but I'm just curious about it practically speaking - if a DBA with admin rights on one server can be successfully audited from another server where they don't have admin rights, then at least you have a likely deterrent and a very likely evidence trail should anything happen.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 14

I'm not sure about the debugging, but the issue is that most of the auditing requires sysadmin permissions, and they can disable auditing as well as enable it.