Home » RDBMS Server » Security » AUDIT ALTER ANY TRIGGER (oracle 11gr2, linux 6.4)
AUDIT ALTER ANY TRIGGER [message #603003] Tue, 10 December 2013 16:51 Go to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Hi Team,

Iam testing privilege auditing on my test database. I am checking ALTER ANY TRIGGER system privilege. after granting this system privilege to user TEST_USER3, when TEST_USER3 disable a trigger which is in TEST_USER, the acutal statement must be captured by audit. Iam not able to achieve this. Please guide me.

Traces are as below:

---as SYS

17:37:55 SQL> grant alter any trigger to test_user3;

Grant succeeded.

17:44:49 SQL> audit trigger by test_user3;

Audit succeeded.

17:45:01 SQL> audit alter any trigger by test_user3;

Audit succeeded.



----as test_user3, iam disabling a trigger ( owner: TEST_USER)

17:37:45 SQL> alter trigger test_user.trigger_xx1 disable;

Trigger altered.

17:45:44 SQL>


--as SYS, i am checking audit records from DBA_AUDIT_TRAIL using below query:
but, i am not able to get output for this query. i have just put xxxx mark instead of my acutal o/s user

select 	D.NAME "DBMS",
	I.HOST_NAME,
	A.USERNAME,
	A.OS_USERNAME, 								
	A.USERHOST,
	S.MACHINE,
	P.PROGRAM,
	S.MODULE,
	A.OS_PROCESS,
	S.SID,
	S.SERIAL#,
	to_char(S.LOGON_TIME,'Dy dd-mon-yyyy:hh24:mi:ss') "Session Logon Time",								
	A.TERMINAL,
	to_char(A.TIMESTAMP,'Dy dd-mon-yyyy:hh24:mi:ss') "local audit timestamp",							
	A.EXTENDED_TIMESTAMP "Global audit timestamp",
	a.action,
	A.ACTION_NAME "CMDType",	
        A.PRIV_USED "System Privilege Used",
	A.OWNER "Object Owner",
	A.OBJ_NAME "Object Name",								
	A.SQL_TEXT,
	S.SQL_HASH_VALUE,
	A.CLIENT_ID								
from DBA_AUDIT_TRAIL A, V$DATABASE D, V$INSTANCE I, V$PROCESS P, V$SESSION S	 					
where  A.DBID = D.DBID
and    D.NAME = I.INSTANCE_NAME
AND    A.OS_PROCESS = P.SPID
AND    A.USERNAME = S.USERNAME
and a.username = 'TEST_USER3'
and    A.OS_USERNAME = 'xxxxxxx' 
order by A.EXTENDED_TIMESTAMP;	


Please guide me on the same.

Thank you very much
Re: AUDIT ALTER ANY TRIGGER [message #603004 is a reply to message #603003] Tue, 10 December 2013 17:00 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
just adding,

i tested DROP ANY TRIGGER. iam not able to see the audit records after dropping ( from TEST_USER3) another schema's trigger ( TEST_USER). please guide me on this also.

thank you
Re: AUDIT ALTER ANY TRIGGER [message #603005 is a reply to message #603004] Tue, 10 December 2013 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
did TEST_USER3 start a new session after GRANT was issued?
Re: AUDIT ALTER ANY TRIGGER [message #603006 is a reply to message #603005] Tue, 10 December 2013 17:06 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
no, i did not open any new session. i am checking on old session only. should i try ?
Re: AUDIT ALTER ANY TRIGGER [message #603007 is a reply to message #603006] Tue, 10 December 2013 17:07 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
this is amazing. now i am able to see the record. let me check all other system privileges.

thank you very much
Re: AUDIT ALTER ANY TRIGGER [message #603008 is a reply to message #603007] Tue, 10 December 2013 17:10 Go to previous message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member

just hiding original ip and schema details:



xxxxx   ip.1.2.3.4.5   TEST_USER3   ksundar9     xxxxxx        xxxxx    oracle@ip.1.2.3.4.5. SQL*Plus     29502           17       4751 Tue 10-dec-2013:23:06:43       xxxxxN   Tue 10-dec-2013:18:06:55       10-DEC-13 06.06.55.894803 PM -05:00               118 ENABLE TRIGGER               ALTER ANY TRIGGER                        TEST_USER       TRIGGER_XX1     alter trigger test_user.trigger_xx1 enable        1994499884
Previous Topic: Accessing Oracle DB from UNIX OS
Next Topic: Password expiry
Goto Forum:
  


Current Time: Thu Mar 28 19:18:31 CDT 2024