Re: Logout system trigger

  • From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
  • To: Ethan Post <post.ethan@xxxxxxxxx>
  • Date: Fri, 10 Oct 2014 08:08:48 -0500

Ok, I did track this down. It can be done with dbms_scheduler, but I have to 
set the parameter use_current_session => false in the call to dbms_scheduler.  
And here I thought a call to dbms_scheduler would automatically be in the 
schedulers own session.

Sent from my iPhone

> On Oct 9, 2014, at 4:37 PM, Ethan Post <post.ethan@xxxxxxxxx> wrote:
> 
> Can you lock an account while a user is connected? If no, then perhaps the 
> user is not technically disconnected and perhaps that is the issue, in that 
> case a dbms_job with a wait to ensure no more sessions connected before 
> locking is the trick. 
> 
>> On Thu, Oct 9, 2014 at 3:52 PM, Fergal Taheny <ftaheny@xxxxxxxxx> wrote:
>> Hi,
>> 
>> If the autonomous transaction doesn't work (from memory i think it won't but 
>> can't remember why) but you can submit a dbms_job which locks the account. 
>> Submitting a dmbs_job is dml so no problem there and then the job does the 
>> ddl.
>> 
>> I have used this approach for other ddl
>> a few times and it works fine. And yeah you can use dbms_scheduler if you 
>> prefer but I prefer dbms_job for one off jobs.
>> 
>> Regards,
>> Fergal
>> 
>>> On 9 Oct 2014 21:02, <rajendra.pande@xxxxxxx> wrote:
>>> 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: