May 7, 2007 at 7:58 am
OK, so we're going public. Got to put together the plan on managing access to our production servers.
1. First, what do auditors look at when auditing a db environment?
2. More specifically, as a team of infrastructure folks, most of us here have local admin rights on the box which authenticates via BuiltIn\Administrators. Also, our service account is a sysadm. What is the acceptable #of 'sysadmins' on a prod box (or is there any?) Do we remove the BuiltIn\Admin account?
3. With respect to logons, is turning on Logon Failures sufficient to log?
4. public role - what is expected here?
I have more q's, but I'll wait for some reply to the above. TIA!
May 8, 2007 at 6:34 am
I also have to address some SOX issues. I plan to look into the Idera "SQL Compliance Manager" software. I haven't evaluated it yet, so really can't offer any opinion.
http://www.idera.com/Products/SQLcm/
There is also an excellent post in this thread by Junk Mail Victim:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=161&messageid=212223
May 8, 2007 at 8:34 am
we are going thru that also... for internal and external audit response.
auditor is gonna get you on using builtin\administrator... remove it (with the exception of cluster server), and limit the number of people have access to your server, limit the number of people who have privilege as sa and remove if possible any developer who own (dbo or database_owner role) from your prod environment.
There are to get arround the number of SA by putting those people who does sa work in a window's group and their main job is admin those server.
limit the number of people who have access to your server room as well. Also you need to document the baseline and write lots of security policy for everything. Here is one of the sample we used as a guideline.
http://csrc.nist.gov/checklists/repository/1079.html
good luck.
mom
May 8, 2007 at 8:36 am
I haven't done this in awhile, but there really isn't any "acceptable number" of admins. It should be the people that need it and no one else. No extras.
The thing with SOX is that we treated it very much like ISO and the creed was "say what you do and do what you say". So document what the access rights are and who needs them and then prove you've done it by having a record of someone being granted access. It can be email, but it should be some log, print or digital, that notes when you grant access.
The same thing applies for the rest of the systems. Document the backup strategies needed and then have a doc that shows you checked to be sure they were working. We used to keep an Excel doc and have a report run of backups each day. We reviewed the report and initialed in the Excel file to show we were checking backups. Our Excel file had all sorts of columns for daily and weekly checks that were just the tasks we regularly performed.
SOX isn't hard, but it is work. And it requires tedious documenting and following your documentation. Once you get in the habit, it's not bad.
May 16, 2007 at 6:41 am
Having been through this about a billion times, I would say the suggestions the folks above have given is are very good. SQL Security is tough to get right given some of the inherent security issues with SQL server. Some suggestions on effective SQL security implementation.
1. Limiting all users with the ability to update data to only those with a valid business need. That said, I find it hard for most to justify the use of Builting\Admins when it almost by default has domain admins, backup admins, service IDs which aren't properly locked down etc. If you don't need, you should consider removing it. SQL server does not need it to run.
2. Learn the stored procedures in SQL which allow you to audit system config and security. For example, sp_configure, sp_helplogins, sp_helpusers, sp_helproles, sp_rolesecurity...etc. There are about 10 that help you understand SQL security and how it is rolled out.
3. In line with item #1, make sure you understand object security and who has access to run those stored procedures. For example, those who can update SQL configuration table through sp_configure could take the database down or enable bogus config, that would be bad. Likewise, access to xp_ stored procedures like xp_cmdshell could give a user who has access to just connect to the database (think public role) ability to issue OS commands and get access to data via that mechanism.
4. If you do implement logging (which you may or may not be asked to by your external auditors; I belive this request changes based upon the size / skill of the DBA group and limitations presented by applications), you need to consider logging both users updating or deleting data and also changes to configurations, and stored procedures. If you can turn off the log or update the audit log, it's not effective. If you can turn off auditing (also, available in sp_configure, turn off the backup log, or change a scheduled task), not really effective.
5. Make sure you check the public role and remove the guest account from databases where you can. By default, guest has access to nothing but it does allow you to view data if it is given a view role on a database instance. While SOX isn't concerned about view only (that is, of course, if the darn application doesn't give those who can view data (e.g. select) ability to query the password table for the app))
6. I know this is new in 2005, but if you can implement strong password security, I would consider it.
As the folks in this thread have echoed, having documentation that addresses how your relevant system security settings are set, who has access to roles that can update data, what tools are used to access data, what types of passwords must be used to access data, and how access to teh system is administered and reviewed periodically.
If you have any specific compliance questions, let me know.
May 22, 2007 at 6:23 am
I would also add that as well as securing the database as much as you can in terms of removing unnecessary accounts as has been suggested and applying acls as appropriate you should also consider the following:
1. Separation of duties - You should ensure that any audit data is held securely, is tamper proof, and cannot be modified by any privileged users
2. Metrics - Look at specific metrics within the Database to assess the overall health (ideally this should be automated in some way). For example number of failed logons, one user one ip, access to sensitive stored procedures. In this way you will overtime be able to build up a picture of the health of the database.
3. Audit - Automate the audit process so that you can get a clear picture of who, what, when and how in terms of DB access. This needs to be granular right down to field level within the DB.
4. Keep as much of the auditing process separate from the DB (external to the Database) and DBA's as possible. This could be achieved using third party tools in conjunction with the auditing functions of the database environment (as a backup).
I'm not sure if all of these are relevant to SOX etc but they are desirable / good practice from a security perspective at least.
My 2 cents.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply