Re: Logout system trigger

From: Jure Bratina <jure.bratina_at_gmail.com>
Date: Thu, 9 Oct 2014 21:58:10 +0200
Message-ID: <CAC08BH+dHWgOMquXLS-jUtjPUigpM9FjUr+JvrrNt79yoEs-cQ_at_mail.gmail.com>

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 <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
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 09 2014 - 21:58:10 CEST