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

asked 09 Aug, 15:06

lebpas's gravatar image

lebpas
2018917
accept rate: 0%


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.

permanent link

answered 09 Aug, 16:25

Volker%20Barth's gravatar image

Volker Barth
32.2k328474688
accept rate: 32%

edited 09 Aug, 16:30

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.

(10 Aug, 13:04) lebpas
Replies hidden

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

CALL sa_server_option( 'RequestFilterConn', 123 );

CALL sa_server_option( 'RequestFilterConn', 456 );

(10 Aug, 14:52) Breck Carter

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?

(10 Aug, 17:49) Volker Barth
Comment Text Removed

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.

(14 Aug, 09:02) lebpas

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

(14 Aug, 09:05) lebpas
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6

question asked: 09 Aug, 15:06

question was seen: 102 times

last updated: 14 Aug, 09:05