Re: Auditing changes...By developers

I'm actually surprised that your auditors haven't flagged the fact that the developers know the login the application uses. This is something most auditors don't like.

My recommendations:

1) Change the login password the application uses and don't give it to the developers (this will likely require Change Control / System Admin / DBA / etc. to be involved to set the connection string for the application).

2) Give the developers access via individual logins. This would preferably be Windows user accounts given access via a Windows group.

3) Grant the access the developers need via a database role. Make the logins a member of said role. Don't grant more than they need, for obvious reasons.

4) Audit via either traces or via triggers. Since you aren't worried about the app, triggers probably aren't necessary.

The bottom line is that as long as they have access to a shared login, it becomes very difficult to prove who did what. Information you normally see in traces like hostname and app name can be forged and it is trivial to do so because it comes from the client, meaning you can't rely on it. Therefore, you can't really determine from anything within SQL Server if it's the app coming in or a developer if a developer intends to be malicious. That is, unless you force them to their individual accounts.


K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

On Mar 19, 7:59 pm, K. Brian Kelley <brian_kel...@REMOVE-
ME.sqlpass.org> wrote:

A couple of questions come to mind:

1) Do the developers need the ability to modify the data outside the
application?
2) If so, how are they logging in? Do they know the single
username/password
the application is using?
As Sue posted, a server side trace will show when data is being
modified
because you can capture the statements. However, getting past the
non-repudiation
hurdle (being able to deny you did it) is hard to do unless they are
logging
in with an account whose password only they know and they aren't
using shared
accounts of any sort.
K. Brian Kelley, brian underscore kelley at sqlpass dot
orghttp://www.truthsolutions.com/

What is the best way to go to track changes by developers that are
privy to db usernames and passwords?

Not all application users have domain accounts, so we can't use
trusted connections. Instead, we have a single username that the
application (we only have one) uses to perform its work.

We have auditing at the internal application level...Now we need a
way to determine if any of the four developers are possibly
manipulating data.

I briefly looked at application roles, but considering that you can
run sp_setapprole from the QA, that doesn't seem worthwhile.

How is everyone else doing it? Our auditors assure us it is being
done...

Don't you love SOX?

Thanks!

Joseph- Hide quoted text -

- Show quoted text -

1) Actually, they *do* need the ability to modify outside of the app,
as it is a relatively new application, and corrections need to be
made.
2) Right now, all connections are being made with the username/
password the application uses. They do know it (although all options
are opened).
I was thinking that the developers could know the database login used
by the app, but then the application would switch to an application
role to get anything done. Thus the Query Analyzer statement: What's
to keep them from launching QA, executing sp_SetAppRole, and then
altering data without any of the application's safequards?

For the record, we are trusting the trail left by the application, and
we aren't concerned about that angle...

Thank you!



.

Relevant Pages

  • Re: Auditing changes...By developers
    ... Change the login password the application uses and don't give it to the ... developers (this will likely require Change Control / System Admin / DBA ... the app, triggers probably aren't necessary. ... brian underscore kelley at sqlpass dot orghttp://www.truthsolutions.com/ ...
    (microsoft.public.sqlserver.security)
  • Re: Visual Studio gives direct dbo access to Sql Server 2000 ??? <<Update>>
    ... After I did this I made sure I could still login ... into the db with the "dba" account we've set up. ... With respect to developers that are also domain admins ... ...
    (microsoft.public.sqlserver.security)
  • Re: What your GNU/Linux looks like? Suggestion for developers...
    ... > suggestions for different kind of developers. ... > Kernel starts with textmode where is a text that no-home user needs. ... When I want to login I'd like to klick ... I use KDE all day long no matter if working or at home. ...
    (alt.os.linux)
  • Re: Should I delete the NT/Admins group in SQL Server 2k
    ... Just remove it from the sysadmin role. ... (Right-click the login, select ... What you should do is create a database role in each database, ... that role permissions that you want your developers to have. ...
    (microsoft.public.sqlserver.setup)
  • Re: How to limit access to production data from non-production code?
    ... would create a situation where one person would have multiple accounts ... then in the ACL indicating which identifier will have access or not. ... Developers never have access to Test or Production environments, ...
    (comp.os.vms)