Auditing / logging query and stored proc

[Former Member]

Hi,

I'm looking for a way to capture all query and stored proc call made to a database (SA17), ideally a solution that can be turned on/off for each connection. The database contains very sensitive personal information (payroll). I looked at the auditing option, and it contains some useful information but not what query was performed. In case of a database hack, we would like to have some kind of records on what query was performed on the DB. Any suggestion ?

Thank you

[VolkerBarth]

If the builtin auditing feature is not sufficient for your needs - do you own the database, i.e. can you decide whether a user can directly query all tables or are you able to restrict the access, say to stored procedures? (Possibly only for the tables with sensitive data.) Those procedures could of course trace their usage... - see that familiar question.

---

Aside: Breck has blogged several times on auditing but that focusses primarily on DML statements, not queries.

[Former Member]

Yes, we own the database. Manually tracing the usage of procs is not really an option for us, we have over 10 000 procedures. Yes, only a fraction handle sensitive information, but it's still a couple of hundreds and that would require a huge amount of work.

We have also made some tests with Request logging and for the most part, it would provide what we need, but the problem we have is we can only seems to be able to activate it for all DB connections or only one at a time and I'm unable to have the logging enabled for only 4-5 connections at the same time.

[Breck_Carter]

What happens when you call it twice? ( just a guess 🙂

CALL sa_server_option( 'RequestFilterConn', 123 );

CALL sa_server_option( 'RequestFilterConn', 456 );

[VolkerBarth]

What particular risk are you trying to prevent: an ordinary user gaining access to the credentials of a different user (with different privileges) but still using the application, or someone getting direct database access or even DBA superpowers?

[Former Member]

One option with our payroll software is having access to a web portal to allow employees to access their paystub and other information. If there is a bug in the portal and/or SQL code and someone get access to someone else data or someone compromise the system (by the web application), we would like to be able to have some kind of audit / trace to be able to identify what information was leaked to inform the customer.

A query log is also requested by our auditor for our SOC2 certification.

[Former Member]

When I call it twice, the logging stop for the first one and only log for the 2nd call.