OraFAQ Forum: Security » unable to capture audit on ALTER PROCEDURE...COMPILE

Home » RDBMS Server » Security » unable to capture audit on ALTER PROCEDURE...COMPILE (oracle 11gr2 linux 6.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

unable to capture audit on ALTER PROCEDURE...COMPILE [message #613087]

Tue, 29 April 2014 10:53

kesavansundaram
Messages: 183
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:

Code: [Select all] [Show/ hide]

AUDIT DROP ANY PROCEDURE BY ACCESS;
AUDIT ALTER ANY PROCEDURE BY ACCESS;
AUDIT EXECUTE ANY PROCEDURE BY ACCESS;

- i do procedure compile as below:

Code: [Select all] [Show/ hide]

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 ?

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: unable to capture audit on ALTER PROCEDURE...COMPILE [message #613093 is a reply to message #613087]

Tue, 29 April 2014 14:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when all else fails, Read The Fine Manual.

which ACTIONS can be subject of AUDIT?

Report message to a moderator

Re: unable to capture audit on ALTER PROCEDURE...COMPILE [message #613099 is a reply to message #613093]

Tue, 29 April 2014 15:43

kesavansundaram
Messages: 183
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.

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

]<|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

Report message to a moderator

Re: unable to capture audit on ALTER PROCEDURE...COMPILE [message #613100 is a reply to message #613099]

Tue, 29 April 2014 15:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_5001.htm#SQLRF56357

Report message to a moderator

Previous Topic:	what is wrong here
Next Topic:	Audit oracle and legal value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 04 20:02:50 CDT 2025