Re: Logout system trigger

  • From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
  • To: Oracle-L Freelists <Oracle-L@xxxxxxxxxxxxx>
  • Date: Thu, 9 Oct 2014 11:13:45 -0500

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


Other related posts: