eet our requirements for strong internal IT controls. Due to Sarbanes-Oxley
rules, we have to be able to show auditors that either IT is prevented from
causing a data issue or ca
n be detected if they can't be prevented.
The specific concern is with the DBA account and it's ability within the Sys
tem Administrator role to do essentially anything, including perhaps writing
unauthorized SQL statements that could insert/update/delete financial data
in the production database.
I've been experimenting with some approaches but would like to know what Mic
rosoft is recommending for meeting these requirements. I'm also interested
in anyone else's take on it as well.
So far I've considered:
1) Remove DBA from SA role and add to other fixed server roles and fixed dat
abase roles (except db_owner). From what I've tested, it seems that the DBA
could still perform all functions except adding/removing users from the rol
es and if put in the db_den
ydatawriter role, cannot modify the data or remove the role. This means tha
t user security would have to be handled by someone else, which is not exact
ly ideal, since to my mind, it's just shifting the risk as all of our IT peo
ple know SQL to some extent
and there is no graceful way for a non IT person to manage it. We could add
a separate security login with a dual-keyed password, so that it would take
2 people, perhaps one IT and one non to administer security...
2) Remove DBA from SA role and add to other fixed server roles and fixed dat
abase roles (including db_owner). The DBA cannot add/remove users from the
fixed server roles, which should be fine, because there would not normally b
e any need for someone to b
e in them. The DBA could remove herself from the db_denydatawriter role, bu
t if we turn on tracing and monitor for that event, an internal controls rev
iewer could catch it, which is much easier than trying to monitor for transa
ctions all the time.
3) Leave DBA as-is in SA role and use 3rd party monitoring tool to track DBA
activity. So far, I've been looking into Entegra, but the problem is that
it doesn't provide transaction monitoring per se, although you can query the
data it collects for trans
actions. Does anyone know of a 3rd party tool that would do it all?
What other models are out there? What would you recommend? Thanks in advan
ce-
Shelley M. Maas
Development Manager
Apio, Inc.Hi Shelley,
From your descriptions, I understood that you would like to select a best
practice for your System Security. If there is anything I misunderstood,
please feel free to let me know
First of all, I think the most suitable one will be the best practice for
your system and I could not figure out which one will be the suitable out
of three. I think the following documents will be helpful to you in
decision:
Checklist: Securing Your Database Server
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/CL_SecDBSe.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.c...ql_security.asp
However, since this is a consultation type issue, you can contact Advisory
Services (AS) . Microsoft Advisory Services provides short-term advice and
guidance for problems not covered by Problem Resolution Service as well as
requests for consultative assistance for design, development and deployment
issues. You may call this number to get Advisory Services: (800) 936-5200.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
****************************************
*******************
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.|||Michael,
Thanks for your reply. I've read both of the articles in the past, and whil
e they offer a lot of helpful information, they do not address my concern, w
hich is more related to internal controls and permissions for IT than to ext
ernal threats or internal n
on IT threats. I assume that AS is a cost-based service, so I wanted to see
if I could get some general suggestions and comments before heading down th
at road. I have to believe that others are dealing with this issue and may
have already found good ans
wers.|||Hi Shelley,
Thanks for your updating
Based on my knowledge, I think the first solution of three is better than
others. However, I am not sure whether it will be the best practice for
your senario, as I have said, I could not define the best practice through
newsgroup. The reason why first one is better than okther two is that the
first one could prevent what might happened while other two just monitor
what happened. When there is a damage on database that could not be
recovered, last two is useless, while I think the first one could prevent
it from happening.
Seocndly, SQL Server has such functionality to track DBA activieties. You'd
better have a look at Books Online or MSDN Online with topic "C2 Auditing".
C2 auditing, which requires that you follow very specific security
policies. You could follow this to make it effective (You need restart your
server). Attention please, opening c2 auditing will have an IMPACT on your
server performance!
-In Query Analyzer
sp_configure 'show',1
go
reconfigure with override
go
sp_configure 'c2',1
reconfigure with override
go
--then restart the server, you will see an trc file on your data directory
now. AGAIN, this kind of writing into disk will have an influence on your
performance
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.sql
No comments:
Post a Comment