Re: Logout system trigger
Date: Thu, 9 Oct 2014 16:37:53 -0500
Message-ID: <CAMNhnU3VLDVv8uxzic6zGmdF_uWqkkgnYb5XfDeRp2h5eYCP9A_at_mail.gmail.com>
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 Thu Oct 09 2014 - 23:37:53 CEST