Re: database logon trigger woes

Perhaps it is a security related issue even though you can execute the package directly. How are your granted the execute privilege? Is it a direct grant to your id, via a role, or via a grant to public. If from a role or even via public try a direct grant and see what happens. And please let up know.

In article <39291DE6.13CC_at_yahoo.com>, Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:
>Anyone seen this anomaly...I was aiming to automatically enable
 tracing
>on the 'demo' schema as they logged on...
>
>create or replace
>trigger demo_trace
>after logon on demo.schema
>begin
> dbms_session.set_sql_trace(true);
>end;
>/
>
>compiles no problems... but then
>
>SQL> conn demo/demo
>ERROR:
>ORA-00604: error occurred at recursive SQL level 1
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SESSION", line 83
>ORA-06512: at line 4
>
>
>Warning: You are no longer connected to ORACLE.
>
>SQL> conn sys
>Enter password:
>Connected.
>SQL> drop trigger demo_trace;
>
>Trigger dropped.
>
>SQL> conn demo/demo
>Connected.
>SQL> set role none;
>
>Role set.
>
>SQL> exec dbms_session.set_sql_trace(true);
>
>PL/SQL procedure completed successfully.
>
>
>Even more interestingly was if I changed it to 'alter session'
 using
>dbms_sql then it still didn't work, but OTHER alter session
 commands
>(such as current_schema) worked fine...
>
>Hmmmmmmmmmm..........
>
>
>--
>===========================================
>Connor McDonald
>http://www.oracledba.co.uk
>
>We are born naked, wet and hungry...then things get worse
>
>

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!