How to capture what the specific user saw in database

[Former Member]

I have a project that captures specific user saw from database including what DML or/and data? Any suggestion?

[Vlad]

Could you please tell us what is the purpose of such task? E.g. do you want a short-time tracing, or you want to spy and see what requests the user sends, and what responses he gets.

Will this make sense to modify the application for this purpose?

[Former Member]

The reason is that a ODBC connection provided to third-party for reporting only. But we need to know what query they run or/and probably we need to capture what result set they got.

[VolkerBarth]

[...including what DML...] and [... for reporting only.]

Just to understand (as DML usually includes INSERT/UPDATE/DELETE/MERGE, either - do you want to find out whether they do data modifications (which should be preventable by according permissions)?

One possibility would be to use Request Level Logging (RLL) just for that according connection. In case you would have to run that regularly, you could use a login_procedure to start RLL when the according user connects. - Note: I'm not sure whether that will reveal the returned result sets.

[Breck_Carter]

> ... ODBC connection provided to third-party for reporting only. 
> ... we need to know what query they run or/and probably we need 
> to capture what result set they got.

What you are asking for is (a) completely reasonable and (b) enormously difficult to implement for unrestricted and unpredictable adhoc queries.

If you can demand that the third-party provide stored procedures to perform the queries, then you could (1) modify those stored procedures to save the result sets in permanent tables for you to audit and (2) grant the third-party permission to call those procedures and do nothing else.

In lieu of "provide stored procedures" you could ask them to provide the code for their queries and then you could write the procedures. Once the queries are wrapped inside CREATE PROCEDURE / END blocks you have a great deal of control over logging, auditing, etcetera.

If you are not familiar with stored procedures that return result sets, just ask more questions here.