RE: Logout system trigger

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Thu, 9 Oct 2014 19:02:14 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD126F0D55C_at_G6W2491.americas.hpqcorp.net>

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

Received on Thu Oct 09 2014 - 21:02:14 CEST