Home » RDBMS Server » Security » How to remove object audit ? (oracle 11.2.0.4.0)
How to remove object audit ? [message #625899] Wed, 15 October 2014 14:22 Go to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Hi Team,

I enabled default audit using below audit statements on one test instance.

AUDIT CREATE, ALTER, RENAME, GRANT, INSERT, UPDATE, DELETE, SELECT, EXECUTE, COMMENT, FLASHBACK, INDEX, LOCK, AUDIT, READ ON DEFAULT;
AUDIT TABLE;
(i) after that I created this schema U002 and created below 6 tables.

15:07:08 SQL> SELECT * FROM DBA_OBJ_AUDIT_OPTS where owner = 'U002' and object_type = 'TABLE';

OWNER                          OBJECT_NAME                    OBJECT_TYPE             ALT       AUD    COM          DEL       GRA       IND       INS       LOC       REN       SEL       UPD       REF EXE       CRE       REA       WRI       FBK
------------------------------ ------------------------------ ----------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- --------- --------- --------- --------- ---------
U002                           T341                           TABLE                   S/S       S/S    S/S          S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-       -/-       -/-       -/-       S/S
U002                           T351                           TABLE                   S/S       S/S    S/S          S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-       -/-       -/-       -/-       S/S
U002                           T361                           TABLE                   S/S       S/S    S/S          S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-       -/-       -/-       -/-       S/S
U002                           T371                           TABLE                   S/S       S/S    S/S          S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-       -/-       -/-       -/-       S/S
U002                           T381                           TABLE                   S/S       S/S    S/S          S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-       -/-       -/-       -/-       S/S
U002                           T44                            TABLE                   S/S       S/S    S/S          S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-       -/-       -/-       -/-       S/S

6 rows selected.
all below actions are captured for these 6 tables.

ALTER
AUDIT
COMMENT
DELETE
FLASHBACK 
GRANT
INDEX
INSERT
LOCK
RENAME
SELECT
UPDATE


--after removing default audit, i created 12 more tables. But when i query DBA_OBJ_AUDIT_OPTS, those 12 tables which were created after disbling default audit are not there.
i.e. if I query select/insert/update/delete against those 12 tables, audit is not captured. Only for above 6 tables it is captured. At this time, I want to remove audit capture
from those 6 tables also. How to disable audit on these 6 tables ??

15:08:57 SQL> SELECT * FROM ALL_DEF_AUDIT_OPTS;

ALT       AUD       COM       DEL       GRA       IND       INS       LOC       REN       SEL       UPD       REF EXE       FBK       REA
--------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- --------- --------- ---------
-/-       -/-       -/-       -/-       -/-       -/-       -/-       -/-       -/-       -/-       -/-       -/- -/-       -/-       -/-
--there are 18 tables under this schema U002

15:08:58 SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE owner = 'U002';

  COUNT(*)
----------
        18


for e.g I tried below command, but still SELECT is captured for those 6 tables. I want to set "NOAUDIT" for all below actions against these 6 tables. How to perform ?

ALTER
AUDIT
COMMENT
DELETE
FLASHBACK 
GRANT
INDEX
INSERT
LOCK
RENAME
SELECT
UPDATE

Please guide me

Thank you
kesavan
Re: How to remove object audit ? [message #625905 is a reply to message #625899] Wed, 15 October 2014 23:53 Go to previous message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> AUDIT CREATE, ALTER, RENAME, GRANT, INSERT, UPDATE, DELETE, SELECT, EXECUTE,
  2        COMMENT, FLASHBACK, INDEX, LOCK, AUDIT, READ
  3    ON DEFAULT;

Audit succeeded.

SQL> AUDIT TABLE;

Audit succeeded.

SQL> create table t1 (val int);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> update t1 set val=val+1;

1 row updated.

SQL> delete t1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> @aud_opt_obj michel %

AuditType User                           Object                         Object Type           Alter  Audit  Commen
--------- ------------------------------ ------------------------------ --------------------- ------ ------ ------
Delete Grant  Index  Insert Lock   Rename Select Update Refere Execut Create Read   Write  Flashb
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
Object    MICHEL                         T1                             TABLE                 S/S    S/S    S/S
S/S    S/S    S/S    S/S    S/S    S/S    S/S    S/S    -/-    -/-    -/-    -/-    -/-    S/S

SQL> @aud_obj2 michel

Ora User     Owner           Object               Action                              RC Datetime
------------ --------------- -------------------- ------------------------------- ------ -----------------
MICHEL       MICHEL          T1                   DEL/                                 0 16/10/14 06:32:48
MICHEL       MICHEL          T1                   INS/                                 0 16/10/14 06:32:48
MICHEL       MICHEL          T1                   UPD/                                 0 16/10/14 06:32:48

SQL> noAUDIT CREATE, ALTER, RENAME, GRANT, INSERT, UPDATE, DELETE, SELECT, EXECUTE,
  2        COMMENT, FLASHBACK, INDEX, LOCK, AUDIT, READ
  3    ON DEFAULT;

Noaudit succeeded.

SQL> noAUDIT TABLE;

Noaudit succeeded.

SQL> @aud_opt_obj michel %

AuditType User                           Object                         Object Type           Alter  Audit  Commen
--------- ------------------------------ ------------------------------ --------------------- ------ ------ ------
Delete Grant  Index  Insert Lock   Rename Select Update Refere Execut Create Read   Write  Flashb
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
Object    MICHEL                         T1                             TABLE                 S/S    S/S    S/S
S/S    S/S    S/S    S/S    S/S    S/S    S/S    S/S    -/-    -/-    -/-    -/-    -/-    S/S

SQL> create table t2 (val int);

Table created.

SQL> insert into t2 values (1);

1 row created.

SQL> update t2 set val=val+1;

1 row updated.

SQL> delete t2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> @aud_obj2 michel

Ora User     Owner           Object               Action                              RC Datetime
------------ --------------- -------------------- ------------------------------- ------ -----------------
MICHEL       MICHEL          T1                   DEL/                                 0 16/10/14 06:32:48
MICHEL       MICHEL          T1                   INS/                                 0 16/10/14 06:32:48
MICHEL       MICHEL          T1                   UPD/                                 0 16/10/14 06:32:48

SQL> @aud_opt_obj michel %

AuditType User                           Object                         Object Type           Alter  Audit  Commen
--------- ------------------------------ ------------------------------ --------------------- ------ ------ ------
Delete Grant  Index  Insert Lock   Rename Select Update Refere Execut Create Read   Write  Flashb
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
Object    MICHEL                         T1                             TABLE                 S/S    S/S    S/S
S/S    S/S    S/S    S/S    S/S    S/S    S/S    S/S    -/-    -/-    -/-    -/-    -/-    S/S


Quote:
I want to set "NOAUDIT" for all below actions against these 6 tables.


Did you try to explicitly execute NOAUDIT on these tables? You still have the rows in dba_obj_audit_opts.

Previous Topic: Performing Auditing
Next Topic: how to lock user account after expiere
Goto Forum:
  


Current Time: Mon Jun 17 15:54:20 CDT 2019