2

We have set audit_trail to DB, extended.I m trying to restrict the audit for specific users. But all users(objects) DML operations are auditing by default.

I tried to stop the auditing using all these statements:

SQL> NOAUDIT ALL;

Noaudit succeeded.

SQL> NOAUDIT ALL ON DEFAULT;

Noaudit succeeded.

SQL> NOAUDIT SESSION;

Noaudit succeeded.

SQL> noaudit select any table by X;

SQL> noaudit all by X;

But its still auditing all dml operations(for x schema also)

There are no audit policies and i see below output from below statement

SELECT * FROM DBA_PRIV_AUDIT_OPTS UNION SELECT * FROM DBA_STMT_AUDIT_OPTS;

null EXEMPT ACCESS POLICY By Access By Access

So i disabled it too

noaudit EXEMPT ACCESS POLICY;

Oracle 11g Release 2 Database is running on 64 bit CentOS.

Please let me know,is this default behavior of 11g database.If yes,Is there any way i can restrict it.

Your help is really appreciated.

Thank You!

APC
  • 144,005
  • 19
  • 170
  • 281
user3171342
  • 51
  • 2
  • 10

1 Answers1

1

By default database will record when it is stopped or started as well as record when a user logs on with either SYSDBA or SYSOPER privileges. 

Oracle Database 11g audits the following privileges by default: 
ALTER ANY PROCEDURE DROP ANY TABLE       CREATE ANY PROCEDURE
ALTER ANY TABLE     DROP PROFILE         CREATE ANY LIBRARY  
ALTER DATABASE      DROP USER            CREATE ANY TABLE
ALTER PROFILE       DROP ANY PROCEDURE   CREATE EXTERNAL JOB
ALTER SYSTEM        EXEMPT ACCESS POLICY CREATE PUBLIC DATABASE LINK
ALTER USER          AUDIT SYSTEM         CREATE SESSION
GRANT ANY PRIVILEGE GRANT ANY ROLE       CREATE USER
GRANT ANY OBJECT PRIVILEGE               CREATE ANY JOB

Reference : https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000

In your case you could check if fine grained auditing is turned on. You could check with the following SQLs.

SQL> SET lines 150
SQL> SELECT object_schema,object_name,policy_name,policy_column,enabled,sel,ins,upd,del FROM dba_audit_policies;

SQL> SELECT * FROM dba_audit_policy_columns;
Sabiha
  • 71
  • 1