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  |
kesavansundaram
Messages: 183
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;
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.
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
--------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- --------- --------- ---------
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
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  |
 |
Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
Goto Forum:
Current Time: Tue Mar 11 21:10:56 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
