Re: using set role command in a logon trigger

On 4/4/07, laura pena <lizzpenaorclgrp_at_yahoo.com> wrote:
>
> looking at proxy users now. Anyone set this up using JBoss and a
> datasource connection?
>
> Little confused on if in the datasource do you specify rolename to use?
> Has anyone used this before I attempt all this work?

There are others on this list that have experience using this. I do not have experience with it.

P.S I think it's a bug because is no error saying this is not allowed at
> compile time or at runtime.
>
> This bug number references and enhancement request.
>
> -Lizz
>

As long as all you need is to enable the role, the trigger solution would work. As that enhancement is request is 4+ years old, you may be waiting awhile for it.

If however you also depend on that role for privileges in any PL/SQL code, a role will not work.

Wrote file afiedt.buf

  1 declare
  2 i integer;
  3 begin
  4 select count(*) into i from from dba_users;   5* end;
09:49:54 6 /
end;
*
ERROR at line 5:
ORA-06550: line 4, column 30:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored

The select statement worked in sqlplus as a query due to privs granted through a role. The way around that dilemna is to create procedures with owners rights. That does require some extra attention to security.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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