SELECT TRIGGER 2

[kaiuweb]

Hi,
Does anyone know of a way I can monitor users doing a select on a table if they haven't connected via the application.

I have a system with sensitive information, the users will access this info through an unbound adp.
They have concerns that out IT Staff, who all have full rights as developers and dba's, could view this data.
So as a compromise I thought there must be a way that if userX does a select on a table and they haven't come in via the application, that sysadmin or userZ could get notified.
Or maybe a trigger could write to a file, or anything to record/alert when anyone does a select on a table they shouldn't be viewing.

Hope I haven't confused you.
Any ideas would be appreciated.

 

[fluteplr]

Unfortunaly no such thing as a select trigger.

You could run a trac continuously and set the parameters to the database in question and application names other than the authorized application.

Of course if they have access they could always shut down the trace.

Usually the solution to this problem is to have all the developers sign a non-disclosure agreement indicating the issues with sensitive data.

Trying to keep people who have admin rights out of databases is a losing proposition.

 

[SQLSister]

Personally what we do here is limit the developers rights to production data. They have full rights on our development server, but on production only dbas have system rights or development rights of any kind. On production, they have the same rights the users have because they need to see what is happening if there is a problem. So it isn't fair to take away their select rights if they need to support production. Further, non-dba personnnel do not have the password to sa on any server. Therefore they can't log in as sa (or worse, have their application log in as sa to avoid having to set rights). As flutplyr said, it is a good idea to have anyone with access to sensistive data to sign a non-disclosure agreement. But the bottom line is don't give system rights to anyone you don't trust. Very sensitive data can be encrypted by your user interface so that it is essentially meaningless until it is decrypted by the user interface. Many companies do this with passwords or credit card information or salary information to ensure the developers can't simply read it. But they can decrypt it if they take the trouble to do so (after all who wrote the encryption software?). It does make it more of a pain and more of a deliberate act to read sensistive information though.

 

[kaiuweb]

Thanks fluteplr and SQLSister.
I guessed as much. I think I just needed confirmation.

Thanks to you both.

kaiuweb
UK