September 27, 2013 at 6:14 am
I have a database which, among other items, has sensitive, Executive payroll tables. As the Developer, I have been asked to restrict these tables so only selected operators can view/update them. Although as the Developer, I am allowed access, the System Administrator should not be. I am not able to use traditional methods of limiting access by user group, because the SysAdmin can simply add himself to whatever group has access. I already have password protection within the application itself, so when an operator goes into Payroll, Executive Payroll isn't an option. However, if an authorized user uses Excel, Crystal Reports, etc. to connect directly to the database, then the security built into the application is bypassed.
Here are the ways I'm considering so far, although I don't know if any of them will work.
1. Encrypt or password protect selected tables.
2. Encrypt selected columns of the selected tables when the new rows are added or existing rows are modified. Decrypt the columns when the rows are accessed.
3. Create a separate database on the executive payroll operator's local drive (there is only one executive payroll operator). When the application opens, if would either copy the local tables to the common database or link to them. When the application closes it would either copy the tables back to the local drive and remove them or remove the link to the tables on the local drive.
Any other ideas are appreciated.
September 27, 2013 at 7:02 am
EddieN1 (9/27/2013)
Although as the Developer, I am allowed access, the System Administrator should not be.
Is it ???? then i totally discourage the rule/approach here .
Even you CANT restrict somebody who has sysadmin access to stop doing anything.
Better approach is .. put all the person (to whom you want to restrict) in a sql (AD) group and then manage their permission thats the best practice to manage multiple user having samekinf of permission
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 27, 2013 at 8:32 am
Firstly, I would question why you are allowed access to view this data. As a developer I would normally expect you to never touch production systems. That is how it has always worked in companies I have worked in.
Either, way that still leaves the risk of having sysadmins with the ability to view sensitive data, whether they are a developer or dba etc.
In a previous role I was the DBA and developer for a small organisation, developing the application and database for an application which stored very sensitive information. An audit requirement meant that only two people in the company could see this data and only through the application front end. To satisfy this, I secured the data using encryption at the column level with a password.
When the two users were set up on the system, they accessed part of the application which allowed them to set the encryption of the columns. They entered their password and this passed down the commands to encrypt and set the passwords for the columns. To view, add or edit the data, the users then used another part of the application where they had to type there password into a box, press a 'decrypt' button, which them performed the sql statements to retrieve the data from the database. After 5 minutes of inactivity the application locked the view of the data again.
This meant that only with the password could the data be decrypted, so even if I looked at the data using SQL, it was just scrambled. Even knowing the decryption commands performed via the application, I couldn't decrypt it either and neither could any reports or excel.
Not sure if this is something you could, do but it worked in our situation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply