Re: Auditing changes...By developers

On Mar 19, 11:47 pm, K. Brian Kelley <brian_kel...@REMOVE-
ME.sqlpass.org> wrote:
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 orghttp://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!- Hide quoted text -

- Show quoted text -

Thanks, Brian!

I've actually made this recommendation in my "Most Secure" plan...It
will just take some time to get the code changes in place.
(Currently, the application reads the credentials from a two-way
encrypted file using TripleDES). I like the idea of a protected
connection string...Perhaps a compiled .dll in the hands of the DBA
(myself)?

These are good recommendations...I appreciate it! If you have any
more thoughts, I would love to hear them.

-Joseph

.

Relevant Pages

  • 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: 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. ... Change the login password the application uses and don't give it to the developers. ... This would preferably be Windows user accounts given access via a Windows group. ...
    (microsoft.public.sqlserver.security)
  • 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: Advice Needed...
    ... will notice something is wrong with the general look of the app. ... Access developers don't seem to notice these things for some reason. ... I've written controls before and made every effort ... never liked about VB6 is the lack of a design-time size-to-fit feature. ...
    (microsoft.public.vb.general.discussion)