| How do I know which user has updated the tables? [message #69974] |
Mon, 25 March 2002 22:23  |
Rajesh Subramanian
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
Hi,
We a team of 60 are working in a report generation project, which exhaustively uses the tables in our database. We all have one single user id "DBO" and I'd like to know which user (OS USER in a previously logged-in SESSION) has made changes to the database.
I think there must be a way to accomplish this using the System tables (V$ tables), but don't know how?
Can you help me out with an answer to this issue?
Regards,
Rajesh Subramanian.
|
|
|
|
| Re: How do I know which user has updated the tables? [message #69985 is a reply to message #69974] |
Tue, 26 March 2002 15:07  |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
It has few ways to do this. but in your case,i suggest you to use AUDIT.Below is the step to setting for audit..
1. Shutdown your database, in parameter file, please setting this
AUDIT_TRAIL=DB; {for database}
and start your database.
2. Now your database is able to use AUDIT. ( if you can find any AUDIT table - SYS.AUD$ then please execute this script CATAUDIT.SQL}.
Logon as system ( who has AUDIT SYSTEM/AUDIT ANY system privileges). AUDIT has three type
- Privileges/Statement AUDIT
AUDIT SESSION BY DBO; - record all access by this user DBO in SYS.AUD$.
- Object AUDIT
AUDIT DELETE,UPDATE,INSERT ON table BY ACCESS;- record all this DML by table access in SYS.AUD$
- NOAUDIT is used to revoke the AUDIT action
Note:
- If you audit too many object, which causes performance issue.
- hausekeeping this table AUD.SYS$, because when this table is full then user is not allowed to access any AUDIT table. with backup and truncate this table.
- makesure this table is also AUDIT to prevent any user to delete/truncate this table with this
AUDIT DELETE,UPDATE,INSERT ON AUD.SYS& BY ACCESS;
- makesure certain people have AUDIT SYSTEM/AUDIT ANY system privileges.
- More information please refer to Oracle Documentation.
- V$table is only keep the most current session and database information. It doesn't save general previous access information.
Hope this is helping
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
