RE: Logout system trigger

  • From: <rajendra.pande@xxxxxxx>
  • To: <jure.bratina@xxxxxxxxx>, <andrew.kerber@xxxxxxxxx>
  • Date: Thu, 9 Oct 2014 16:01:11 -0400

Nice!! 

 

Maybe an autonomous transaction?

 

Regards 

 

- Raj Pande

UBS AG

  Platform Services - Operations 

  Global Service Delivery (GSDM) 

  480 Washington Blvd. Jersey City, NJ 07310

  TEL# - External - +1 201 318 7597

             Internal - 19 436 7597

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jure Bratina
Sent: Thursday, October 09, 2014 3:58 PM
To: andrew.kerber@xxxxxxxxx
Cc: mark.powell2@xxxxxx; Oracle-L Freelists
Subject: Re: Logout system trigger

 

Hi,

when tracing the session when such a trigger fires, an ORA-30511 is found in 
the trace: 

PARSING IN CURSOR #10476772 len=76 dep=1 uid=553 oct=47 lid=553 
tim=1412883002571503 hv=1931945942 ad='45bd7ad0' sqlid='11j0qr5tkf9yq'
begin
  execute immediate 'alter user u1 identified by a account lock';
end;
END OF STMT
PARSE 
#10476772:c=2999,e=3411,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1412883002571501
=====================
PARSING IN CURSOR #10473708 len=41 dep=2 uid=553 oct=43 lid=553 
tim=1412883002593638 hv=1975896108 ad='4752a160' sqlid='ca8n0fxuwbk1c'
alter user u1 identified 
END OF STMT
PARSE 
#10473708:c=0,e=21909,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=1412883002593636
CLOSE #10473708:c=0,e=5,dep=2,type=0,tim=1412883002593965
EXEC 
#10476772:c=0,e=22382,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1412883002594025
ERROR #10476772:err=30511 tim=1412883002594048


$ oerr ora 30511
30511, 00000, "invalid DDL operation in system triggers"
// *Cause:  An attempt was made to perform an invalid DDL operation
//          in a system trigger. Most DDL operations currently are not
//          supported in system triggers. The only currently supported DDL
//          operations are table operations and ALTER?COMPILE operations.
// *Action: Remove invalid DDL operations in system triggers.



Might be because of this restriction: 
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS797
Restrictions (for BEFORE LOGOFF triggers): DDL on other objects is limited to 
compiling an object, creating a trigger, and creating, altering, and dropping a 
table.


Regards,

Jure Bratina

 

 

On Thu, Oct 9, 2014 at 9:41 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote:

The syntax is valid. I was just adding an extra level of complexity by changing 
the password again.

Sent from my iPad


> On Oct 9, 2014, at 2:02 PM, Powell, Mark <mark.powell2@xxxxxx> wrote:
>
> Shouldn't the command to lock the account just be: 'alter user my account 
> lock' ?
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Andrew Kerber
> Sent: Thursday, October 09, 2014 12:14 PM
> To: Oracle-L Freelists
> Subject: Re: Logout system trigger
>
> Here is the trigger so far:
>
> create or replace trigger my_logoff_trigger before logoff on my.schema declare
>  sql_cmd varchar2(200);
>  pwd varchar2(30);
> begin
>  dbms_output.enable(10000);
>    update my_audit
>    set logoff_time=systimestamp
>    where os_pid=(SELECT P.SPID FROM V$PROCESS P INNER JOIN V$SESSION S ON
>               S.PADDR = P.ADDR WHERE S.AUDSID = sys_context('USERENV', 
> 'SESSIONID'))
>    and oracle_session_id=SYS_CONTEXT('USERENV','SESSIONID');
>    commit;
>    pwd:=generate_password();
>    sql_cmd:='alter user my identified by '||pwd||' account lock';
>    execute immediate sql_cmd;
>  end if;
> exception
>  when others then
>  RAISE;
> end;
>
> Sent from my iPad
>
>> On Oct 9, 2014, at 11:00 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote:
>>
>> I am trying to create a trigger that locks an account after a user 
>> disconnects.  It also writes a record to an auditing table.  It writes the 
>> record successfully, then I use execute immediate to lock the account, but 
>> the lock command seems to be ignored.  I expect there is some special 
>> processing I need to do.  Does anyone have an example of how to do this?  
>> 11.2.0.4 EE on Linux.
>>
>> Sent from my iPad
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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



 

Please visit our website at 
http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html 
for important disclosures and information about our e-mail 
policies. For your protection, please do not transmit orders 
or instructions by e-mail or include account numbers, Social 
Security numbers, credit card numbers, passwords, or other 
personal information.

Other related posts: