August 2, 2012 at 1:29 am
Good Day ,
our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?
August 2, 2012 at 1:38 am
lianvh (8/2/2012)
Good Day ,our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?
I think its overkill from an auditors perspective. There is no real way to prevent a DBA from viewing data without potentially restricting their ability to do their job.
Having worked in the strictly regulated finance industry I can say that viewing data is different to fully understanding data. Segregation of duties provides this distance where required and possible.
Alternatively you should be auditing all DBA access to data and/or systems so that an explanation of why they did this and when can be traced to a change request.
August 2, 2012 at 1:39 am
Another thought. Sensitive data could also be encrypted.
August 2, 2012 at 3:04 am
If you don't want your DBA to view data, you could restrict his permission, not grant sysadmin or database role.
If couldn't then use encryption and audit tools.
August 2, 2012 at 6:11 am
DBA328 (8/2/2012)
If you don't want your DBA to view data, you could restrict his permission, not grant sysadmin or database role.If couldn't then use encryption and audit tools.
And how does one administer the server without sysadmin? OP, you have to explain to the auditor that the DBA must have access to everything. Sensitive data should always be encrypted, but who will do the encryption? I think your auditor doesn't know what he/she is doing. You must explain to them that the DBA is the top of the chain for data. If it is sensitive data that shouldn't be encrypted (HR stuff for example), then the DBA must be a trusted individual in the company. The auditor shouldn't care who has access to the data as long as the person having access is in the DBA or HR role. When I managed this type of data, I had a brief session with HR to fully understand my role in protecting the data and the sensitivity of it. Imagine telling someone that they are responsible for guarding what is in a room, but the room is locked and they don't have the key. When someone with a key comes, how do you know what they are taking in and out?
Jared
CE - Microsoft
August 2, 2012 at 6:28 am
SQLKnowItAll (8/2/2012)
Imagine telling someone that they are responsible for guarding what is in a room, but the room is locked and they don't have the key. When someone with a key comes, how do you know what they are taking in and out?
i like that analogy.
Lowell
August 2, 2012 at 7:02 am
Oracle has a SYSOPER privilege allows opration such as:
instance startup,mount & database open;
alter database backup archive log and restore
This privilege allows the user to perform basic operational tasks without the ability look user data.
I know this SYSOPER could limit DBA powers, but some company has different DBA roles. And it better not allow DBA to access database directlly after application implemantation.
August 2, 2012 at 7:09 am
DBA328 (8/2/2012)
Oracle has a SYSOPER privilege allows opration such as:instance startup,mount & database open;
alter database backup archive log and restore
This privilege allows the user to perform basic operational tasks without the ability look user data.
I know this SYSOPER could limit DBA powers, but some company has different DBA roles. And it better not allow DBA to access database directlly after application implemantation.
SQL server has the same equivilents...if i need someone who only is going to do operating system backups, i can assign the proper roles. The issue is the sysadmin role itself. someone needs to be in that role, even if it is unused, in order to actually do administration.
kind of like saying the person who is the president of the company should not have access to the financial information of the company, because he could abuse it.
As said in thousands of similar posts here: if you cannot trust the person in the role, remove the person.
Lowell
August 2, 2012 at 2:11 pm
lianvh (8/2/2012)
Good Day ,our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?
DBA/Database administrator is a very loosely defined term these days. What particular duties do the database administrators you're trying to restrict need to perform?
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 2, 2012 at 2:16 pm
lianvh (8/2/2012)
...our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?
It all depends on the technology. As far as I know it is not possible to do it (yet) on SQL Server but Oracle has a working solution for it, Oracle vault.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2012 at 4:30 pm
Between partially contained databases and being able to create your own server roles in 2012 I'd imagine you could do what you're asking.
Though you'll want to have someone somewhere with access to the sysadmin role. Who would you trust with that if not the dba?
I did a quick search and found this.
http://www.mssqltips.com/sqlservertip/2299/sql-server-separation-of-duties/
I have 0 experience using this framework.
Also as suggested in this article:
You can always encrypt the data before it gets to sql server.
August 2, 2012 at 4:38 pm
benjamin.reyes (8/2/2012)
Between partially contained databases and being able to create your own server roles in 2012 I'd imagine you could do what you're asking.Though you'll want to have someone somewhere with access to the sysadmin role. Who would you trust with that if not the dba?
+1
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 3, 2012 at 8:22 am
A good start to this would be to not grant sysadmin privileges to the DBA's every day ID's, but require them to use a special ID to perform sysadmnin tasks. That makes auditing easier, since you don't have to track the routine tasks the user does with thier normal ID. It also helps reduce the "oops" potential, especially if the privileged ID's don't have any access to dev/acceptance environments.
August 3, 2012 at 8:26 am
benjamin.reyes (8/2/2012)
Between partially contained databases and being able to create your own server roles in 2012 I'd imagine you could do what you're asking.Though you'll want to have someone somewhere with access to the sysadmin role. Who would you trust with that if not the dba?
I did a quick search and found this.
http://www.mssqltips.com/sqlservertip/2299/sql-server-separation-of-duties/
I have 0 experience using this framework.
Also as suggested in this article:
You can always encrypt the data before it gets to sql server.
Let's make it easier for people to check out these urls:
http://www.mssqltips.com/sqlservertip/2299/sql-server-separation-of-duties/
August 3, 2012 at 12:54 pm
lianvh (8/2/2012)
Good Day ,our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?
A "database administrator" may not need membership in the full SYSADMIN role.
Server-Level Roles
http://msdn.microsoft.com/en-us/library/ms188659.aspx
Database-Level Roles
http://msdn.microsoft.com/en-us/library/ms189121.aspx
SQL Server Agent Fixed Database Roles
http://msdn.microsoft.com/en-us/library/ms188283.aspx
Query accounts, domain groups, and members who have admin membership
http://qa.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply