Auditing changes...By developers

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!
JosephYou can use a server side trace to monitor that login, who
is using it from what workstation and/or what application.
You can monitor with third party products as well. For third
party products and SOX, I've used Compliance Manager from
Idera: http://www.idera.com/Products/SQLcm/Default.aspx
-Sue
On 19 Mar 2007 16:25:28 -0700, "Joseph"
<josephsheppard@.gmail.com> wrote:

>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|||A couple of questions come to mind:
1) Do the developers need the ability to modify the data outside the applica
tion?
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-repudi
ation
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 share
d
accounts of any sort.
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://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
>|||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 appli
cation?
> 2) If so, how are they logging in? Do they know the single username/passwo
rd
> 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-repu
diation
> hurdle (being able to deny you did it) is hard to do unless they are loggi
ng
> in with an account whose password only they know and they aren't using sha
red
> accounts of any sort.
> K. Brian Kelley, brian underscore kelley at sqlpass dot orghttp://www.trut
hsolutions.com/
>
>
>
>
>
>
>
>
>
> - 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!|||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 audit
ors
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:
>
> 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!
>|||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 th
e
> developers know the login the application uses. This is something most aud
itors
> 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 logi
ns
> a member of said role. Don't grant more than they need, for obvious reason
s.
> 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. Therefo
re,
> 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.trut
hsolutions.com/
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - 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|||>>
> 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
>
If it's an ASP.Net application, take a look at aspnet_setreg.exe. More here:
http://support.microsoft.com/kb/329290
Our implementation folks use this for our ASP.NET applications so that the
connection string can be stored encrypted in the registry.
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/