| AUDIT ALTER ANY TRIGGER [message #603003] |
Tue, 10 December 2013 16:51  |
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  |
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 #603008 is a reply to message #603007] |
Tue, 10 December 2013 17:10 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
