AUDITING NON-APPLICATION USERS on APPLICATION TABLES

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Wed, 22 Sep 2004 19:45:18 -0500
Message-ID: <433A07749711884D8032B6A0AB1152620349EB31@conmsx07.corp.acxiom.net>

Will Something like this work?:  

            Thanks

Larry    

SPOOL $DBS/AUD/ALT.$ORACLE_SID SELECT 'GRANT AUDIT SYSTEM TO '||USERNAME||';' FROM DBA_USERS   WHERE USERNAME NOT IN   (

   'DBSNMP'
,'OUTLN'

,'PATROL'

,'XXXXXX','YYYYYY' where X and Y are privileged users

,'SYS'

,'SYSTEM'

,'TOAD'
  )

;

SPOOL OFF   19:17:49 SQL> @AUD/ALT   GRANT AUDIT SYSTEM TO Application;

GRANT AUDIT SYSTEM TO USJDE1; GRANT AUDIT SYSTEM TO CNXARB1; GRANT AUDIT SYSTEM TO EUJDE1; GRANT AUDIT SYSTEM TO USMFG1; GRANT AUDIT SYSTEM TO ACH; GRANT AUDIT SYSTEM TO CATALOG; GRANT AUDIT SYSTEM TO REGOA; ;    

We connect as each user and:  

SELECT 'USER IS '||USER FROM DB;   SPOOL $DBS/AUD/ALTBY.$ORACLE_SID SELECT ' AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY ' ||USERNAME|| ' BY ACCESS;'   FROM DBA_USERS   WHERE USERNAME != USER ;

SPOOL OFF

                  AS AN EXAMPLE:

USER IS SYS    AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY SYSTEM BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY PATROL BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY Application BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY CNXARB1 BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY EUJDE1 BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY USMFG1 BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY USJDE1 BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY CATALOG BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY OUTLN BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY DBSNMP BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY XXXXXX BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY ACH BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY TOAD BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY YYYYYY BY ACCESS;  AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE, SELECT TABLE BY REGOA BY ACCESS;   Notice SYS didn't get generated as owner of schema.  

---

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited.
If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 22 2004 - 19:41:04 CDT