Re: Logout system trigger
Date: Fri, 10 Oct 2014 12:35:26 -0500
Message-ID: <CAMNhnU00V-n8Z7o2=Qn7LYY72ddvKVcrKr2VTmjOuOnVTq0jAQ_at_mail.gmail.com>
By the way a handy utility in your personal automation library is a procedure which you can pass the name of another procedure to and it automatically schedules it with dbms_job and runs it unattended. I have used this for many years, think I call it run_proc and perhaps copied it from somewhere else.
On Fri, Oct 10, 2014 at 8:08 AM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:
> 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_at_gmail.com> 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_at_gmail.com> 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_at_ubs.com> 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_at_freelists.org [mailto:
>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jure Bratina
>>> *Sent:* Thursday, October 09, 2014 3:58 PM
>>> *To:* andrew.kerber_at_gmail.com
>>> *Cc:* mark.powell2_at_hp.com; 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: h <http://goog_1416375729>
>>> ttp://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_at_gmail.com>
>>> 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_at_hp.com> wrote:
>>> >
>>> > Shouldn't the command to lock the account just be: 'alter user my
>>> account lock' ?
>>> >
>>> > -----Original Message-----
>>> > From: oracle-l-bounce_at_freelists.org [mailto:
>>> oracle-l-bounce_at_freelists.org] 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_at_gmail.com>
>>> 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.
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 10 2014 - 19:35:26 CEST