Who is querying tables? [message #50447] |
Fri, 15 March 2002 05:25 |
Phil Lima
Messages: 1 Registered: March 2002
|
Junior Member |
|
|
I have a client that wants to collect information on who is querying a table. The database is of the 8i variety. The table is accessed thru an application that uses Forms 6i, so the users are not issuing command line queries. Each time a user opens a form, our developed security, which is tied to Oracle Roles, checks to see what the user can do on this form (query only or update/insert).
Basically with this type if configuration, each time the form opens the security is checked and if the user has access to the form, the underlying table is queried ( I didn't come up with this system, I just
need to support it) in anticipation of the user either querying the rows or preparing for insert so that constraints can be checked and uniqueness of data verified. In other words, each time the form opens
the table is queried.
The client isn't worried about the users that are inputting data, their userid is recorded as part of the inserted record on commit, but no such
information is saved in the table if only a query is done. They only want to collect the userids that are only looking at data and what data they looked at.
Oh, and the final dinger, they do not want to impact performance dramatically.
If there is some suggestions any of you might have, I would appreciate it.
Regards,
Phil Lima
Oracle Hotline
ACS Government Systems
Lexington KY
phil.lima@acs-inc.com
|
|
|
Re: Who is querying tables? [message #50522 is a reply to message #50447] |
Tue, 19 March 2002 20:30 |
Andrew Lenz
Messages: 16 Registered: August 1999
|
Junior Member |
|
|
you can turn on auditing for a table, for a role or for a specific username. be aware that auditing can cause lots of overhead if you audit too much. also audit information is stored in the sys.aud$ table. this table should be moved out of the system tablespace as it can grow quite large.
another way is to use Precise Indepth for Oracle. Indepth for Oracle will track what queries users execute and store it in what they call the "performance warehouse". you can see who ran what queries now and anytime in the past.
|
|
|