Enable/Disable Table level Audit Option in Oracle

Use this sql command to check out audit option is enabled on which Tables.


SELECT OWNER, OBJECT_NAME FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'SIBL';

OWNER                          OBJECT_NAME                   
------------------------------ ------------------------------
SIBL                           ACCOUNT                       
SIBL                           ACCOUNTTYPE                   
SIBL                           APPCONFIGURATION              
SIBL                           DEP_TXN_ARCH_OLD              
SIBL                           FINANCIALPRODUCT              
SIBL                           INLANDREMITTANCE              
SIBL                           INVMSTR                       
SIBL                           REMITTANCEPRODUCT             
SIBL                           SSP_ACCOUNT                   
SIBL                           SSP_MONTHLYPRODUCTINFO        
SIBL                           SSP_PRODUCT                   
SIBL                           TFS_ACCOUNT                   
SIBL                           TRANSACTIONRECORD  



Use those sql command to generate sql script to enable/disable audit options for those tables;


--to generate disable audit option script
SELECT 'NOAUDIT INSERT, DELETE, UPDATE ON ' || OWNER || '.' || OBJECT_NAME  FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'SIBL';
--to generate enable audit option script
SELECT 'AUDIT INSERT, DELETE, UPDATE ON ' || OWNER || '.' || OBJECT_NAME  || ' BY ACCESS  WHENEVER SUCCESSFUL;' FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'SIBL' OR OWNER = 'ABABILFE';
SELECT 'AUDIT INSERT, DELETE, UPDATE ON ' || OWNER || '.' || OBJECT_NAME  || ' BY ACCESS  WHENEVER NOT SUCCESSFUL;' FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'SIBL' OR OWNER = 'ABABILFE';

Those sql commands generate from above sql command to disable table level auditing.


NOAUDIT INSERT, DELETE, UPDATE ON SIBL.ACCOUNT;                                  
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.ACCOUNTTYPE;                              
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.APPCONFIGURATION;                         
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.DEP_TXN_ARCH_OLD;                         
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.FINANCIALPRODUCT;                         
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.INLANDREMITTANCE;                         
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.INVMSTR;                                  
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.REMITTANCEPRODUCT;                        
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_ACCOUNT;                              
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_MONTHLYPRODUCTINFO;                   
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_PRODUCT;                              
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.TFS_ACCOUNT;                              
NOAUDIT INSERT, DELETE, UPDATE ON SIBL.TRANSACTIONRECORD;


Those sql commands generate from above sql command to enable table level auditing again.


AUDIT INSERT, DELETE, UPDATE ON SIBL.ACCOUNT BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.ACCOUNTTYPE BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.APPCONFIGURATION BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.DEP_TXN_ARCH_OLD BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.FINANCIALPRODUCT BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.INLANDREMITTANCE BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.INVMSTR BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.REMITTANCEPRODUCT BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_ACCOUNT BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_MONTHLYPRODUCTINFO BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_PRODUCT BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.TFS_ACCOUNT BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.TRANSACTIONRECORD BY ACCESS  WHENEVER SUCCESSFUL;

AUDIT INSERT, DELETE, UPDATE ON SIBL.ACCOUNT BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.ACCOUNTTYPE BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.APPCONFIGURATION BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.DEP_TXN_ARCH_OLD BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.FINANCIALPRODUCT BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.INLANDREMITTANCE BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.INVMSTR BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.REMITTANCEPRODUCT BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_ACCOUNT BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_MONTHLYPRODUCTINFO BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.SSP_PRODUCT BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.TFS_ACCOUNT BY ACCESS  WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON SIBL.TRANSACTIONRECORD BY ACCESS  WHENEVER NOT SUCCESSFUL;

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: