| How to enable auditing for a patricular table in oracle 9i [message #295657] |
Wed, 23 January 2008 00:40  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Dear Sir / Madam,
I need to enable auditing ( SELECT,INSERT,UPDATE,DELETE) against one patricular table in oracle 9.2.0.6.0.
i added one policy against that table using below..
dbms_fga.add_policy (
object_schema=>'BANK',
object_name=>'ACCOUNTS',
policy_name=>'ACCOUNTS_ACCESS'
);
and i queried thro
select timestamp,
db_user,
os_user,
object_schema,
object_name,
sql_text
from dba_fga_audit_trail;
iam getting output for ONLY SELECT statement...
TIMESTAMP DB_USER OS_USER OBJECT_SCHEMA OBJECT_NAME SQL_TEXT
----------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1/23/2008 1 BDBSP Administrator BDBSP ACCOUNTS_TEST SELECT * FROM accounts_test
but i need to perform auditing for INSERT,UPDATE,DELETE also.
pls explain how to perform this.
thank you
kesavan.
|
|
|
|
|
|
| Re: How to enable auditing for a patricular table in oracle 9i [message #295682 is a reply to message #295665] |
Wed, 23 January 2008 01:40  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Dear Sir,
Thank you for your prompt reply.
Actually our database is in 9.2.0.6.0 ....but the URL which you have given is for 10g it seems. ( rel-2 ).H'ever, i tried the below
SQL> audit INSERT,UPDATE,DELETE ON BDBSP.ACCOUNTS_TEST;
Audit succeeded
SQL>
but iam not able to view the records after performing the DML on subject table...i tried in sys.aud$...
i have some list of views to get the output from sys.aud$
· DBA_AUDIT_EXISTS
· DBA_AUDIT_OBJECT
· DBA_AUDIT_SESSION
· DBA_AUDIT_STATEMENT
· DBA_AUDIT_TRAIL
· DBA_OBJ_AUDIT_OPTS
· DBA_PRIV_AUDIT_OPTS
· DBA_STMT_AUDIT_OPTS
for e.g
i did not get any rows when i query below:
SQL> select * from dba_audit_statement where owner = 'BDBSP' and obj_name = 'ACCOUNTS_TEST';
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP OWNER OBJ_NAME ACTION_NAME NEW_NAME OBJ_PRIVILEGE SYS_PRIVILEGE ADMIN_OPTION GRANTEE AUDIT_OPTION SES_ACTIONS COMMENT_TEXT SESSIONID ENTRYID STATEMENTID RETURNCODE PRIV_USED CLIENT_ID SESSION_CPU
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- ------------------------------ -------------------------------------------------------------------------------- --------------------------- -------------------------------------------------------------------------------- ---------------- ---------------------------------------- ------------ ------------------------------ ---------------------------------------- ------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------------------------------------- ---------------------------------------------------------------- -----------
SQL>
pls guide me further.
thank you
kesavan.
|
|
|
|
| Re: How to enable auditing for a patricular table in oracle 9i [message #295695 is a reply to message #295682] |
Wed, 23 January 2008 02:08 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
audit_trail parameter should be set to DB or TRUE (or anything different from NONE or FALSE).
This is specified in the doc:
| Quote: | To collect auditing results, you must set the initialization parameter AUDIT_TRAIL to DB.
|
You must restart the database after setting this parameter.
By the way, you can find the same thing in 9i documentation if you have a look at it.
Regards
Michel
[Updated on: Wed, 23 January 2008 02:10] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
