Re: Logout system trigger

  • From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
  • To: "mark.powell2@xxxxxx" <mark.powell2@xxxxxx>
  • Date: Thu, 9 Oct 2014 14:41:40 -0500

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


Other related posts: