Home » RDBMS Server » Security » unable to capture audit on ALTER PROCEDURE...COMPILE (oracle 11gr2 linux 6.4)
unable to capture audit on ALTER PROCEDURE...COMPILE [message #613087] Tue, 29 April 2014 10:53 Go to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Team,

i have set audit_trail = 'db_extended' and would like to capture audit on ALTER PROCEDURE ..COMPILE commands.
as of now, i have implemented audit statemets for PROCEDURE as below:

AUDIT DROP ANY PROCEDURE BY ACCESS;
AUDIT ALTER ANY PROCEDURE BY ACCESS;
AUDIT EXECUTE ANY PROCEDURE BY ACCESS;
- i do procedure compile as below:

11:43:48 SQL> show user
USER is "USER001"
11:43:49 SQL> alter procedure TEST_SP_1 compile;
Procedure altered.

But above activity is not captured. should i hardcode username like this ?

audit procedure by user001 by access;
audit procedure by user001;
but i tried both above also. it is not captured. Is this expected behaviour or ?

Please guide me
thank you

Re: unable to capture audit on ALTER PROCEDURE...COMPILE [message #613093 is a reply to message #613087] Tue, 29 April 2014 14:03 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
when all else fails, Read The Fine Manual.

which ACTIONS can be subject of AUDIT?
Re: unable to capture audit on ALTER PROCEDURE...COMPILE [message #613099 is a reply to message #613093] Tue, 29 April 2014 15:43 Go to previous messageGo to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

I am getting it now. I am sorry for the inconvenience.

ALTER PROCEDURE ...COMPILE

in general, ALTER comamnds are not captured against procedure. But against "priv_used" column, that is if any user has ALTER ANY PROCEDURE privilege and issue alter command against that procedure in their schema, then it is captured. I referred below manual table "Table 13-3 Schema Object Auditing Options" it explains me now clearly.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm#BABCFIEA

2) i think this is applicable for JOB also.
I am not able to see audit entry after create job using DBMS_SCHEDULER. But since master user has "any" privilege ( CREATE ANY JOB ) , it is captured under "priv_used" column in dba_audit_trail. I already have implemeted below, but able to view audit records when master user creates the job ( traces pasted below )

[code]AUDIT CREATE ANY JOB;
AUDIT CREATE ANY JOB BY ACCESS;
AUDIT CREATE EXTERNAL JOB;
AUDIT CREATE EXTERNAL JOB BY ACCESS;[/code
]<|PERMTEST     |ip-xxxxxxxxxxxx               |ksundar9       |xxxxxxxxxxxx |29-APR-14 12.47.07 PM               |29-APR-14 04.47.07 PM               |29-APR-14 12.47.07.932208 PM -04:00 |PL/SQL EXECUTE              |CREATE ANY JOB
BEGIN
  DBMS_SCHEDULER.DROP_JOB ('DBSA.MY_NEW_JOB575');
END;

<|PERMTEST     |ip-xxxxxxxxxxxx               |ksundar9       |xxxxxxxxxxxx |29-APR-14 12.47.07 PM               |29-APR-14 04.47.07 PM               |29-APR-14 12.47.07.321060 PM -04:00 |PL/SQL EXECUTE              |CREATE ANY JOB
 BEGIN
 DBMS_SCHEDULER.SET_ATTRIBUTE (
 name         =>  'DBSA.my_new_job575',
 attribute    =>  'repeat_interval',
 value        =>  'freq=weekly; byday=wed');
 END;

<|PERMTEST     |ip-xxxxxxxxxxxx               |ksundar9       |xxxxxxxxxxxx |29-APR-14 12.47.07 PM               |29-APR-14 04.47.07 PM               |29-APR-14 12.47.07.231877 PM -04:00 |PL/SQL EXECUTE              |CREATE ANY JOB
 BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
job_name          =>  'DBSA.my_new_job575',
program_name      =>  'my_saved_program',
repeat_interval   =>  'FREQ=DAILY;BYHOUR=12',
Please clarify for job.
Thank you
Re: unable to capture audit on ALTER PROCEDURE...COMPILE [message #613100 is a reply to message #613099] Tue, 29 April 2014 15:54 Go to previous message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_5001.htm#SQLRF56357
Previous Topic: what is wrong here
Next Topic: Audit oracle and legal value
Goto Forum:
  


Current Time: Tue Jun 18 16:32:29 CDT 2019