August 23, 2012 at 9:08 am
Hi,
I have recently started a new job as a companies only DBA, where they had no one who knew what they were doing before. I have been looking at the organisations existing database infrastructure and noted some very worrying issues which I need to fix. One of the major problems is a 2005 cluster instance which has numerous databases running on it. One of them is the companies HR database which holds all pay details, confidential grievance cases, even bank details. The application that uses this seems to be a built inhouse web/intranet application which isn't likely to be changed. None of the data is encrypted. There have been numerous accounts configured as sysadmins for people across the organisation and other multiple problems. It’s a real nightmare!
My question is this though. I cannot change the application, or add encryption to the sensitive data, (apart from TDE when we upgrade it to 2012), but what can I do to stop other users who may be set up as DBA’s, and therefore sysadmins, doing from looking at the data? I’m thinking firstly to set up proper domain level DBA management of access to the databases, so that means a restricted number of users will have access to the database system. Namely infrastructure support team members. Secondly, I want to implement some kind of auditing on the database to log who is looking at data and alert security personnel when they do – even dbas.
What else can I do to protect/secure this sensitive information?
Thanks.
August 23, 2012 at 9:32 am
First, let's accept the undeniable truth: you cannot prevent someone with sysadmin role membership from looking at the data.
Once you accept that, here's how to go about doing what you're looking to do:
1. Restrict who is a member of the sysadmin role, like you've indicated. You also want to restrict securityadmin, because securityadmin can escalate privileges to sysadmin starting with SQL Server 2005. This minimizes the who.
2. Since you're still dealing with SQL Server 2005, you do not have the Audit object available to you. That becomes avalable in SQL Server 2008 Enterprise Edition. If you can upgrade to that, then look into implementing Audit on the key tables you're worried. You can audit SELECT queries. If you can't, build a server-side trace and put it into a stored procedure. Mark that stored procedure so that it starts up when SQL Server starts using sp_procoption.
3. Build something to process the information you're collecting, whether it's the Audit object or the server-side trace. Report on it frequently.
K. Brian Kelley
@kbriankelley
August 23, 2012 at 9:36 am
Excellent. Thanks for the reply. It's good to know I was thinking along the same lines then.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply