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

asked 06 Oct '15, 16:45

pLee's gravatar image

pLee
21691119
accept rate: 0%

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?

(06 Oct '15, 17:26) Vlad

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.

(06 Oct '15, 17:55) pLee
Replies hidden

[...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.

(07 Oct '15, 04:42) Volker Barth
1
> ... 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.

(08 Oct '15, 15:56) Breck Carter
Be the first one to answer this question!
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:

×11
×5

question asked: 06 Oct '15, 16:45

question was seen: 978 times

last updated: 08 Oct '15, 15:57