Home » RDBMS Server » Security » How to enable auditing for a patricular table in oracle 9i
How to enable auditing for a patricular table in oracle 9i [message #295657] Wed, 23 January 2008 00:40 Go to next message
kesavansundaram
Messages: 165
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 #295665 is a reply to message #295657] Wed, 23 January 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you just use AUDIT statement?

Regards
Michel
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 Go to previous messageGo to next message
kesavansundaram
Messages: 165
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: How to enable auditing for a patricular table in oracle 9i [message #295705 is a reply to message #295695] Wed, 23 January 2008 02:31 Go to previous message
kesavansundaram
Messages: 165
Registered: October 2007
Location: MUMBAI
Senior Member

Thank you for your kind reply.
kesavan.
Previous Topic: sys dba authentication
Next Topic: Query Builder 6i to 10g
Goto Forum:
  


Current Time: Sun Sep 21 17:55:13 CDT 2014

Total time taken to generate the page: 0.08149 seconds