Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Web PL/SQL, roles, Oracle 9i, IAS 9.0.4

Re: Web PL/SQL, roles, Oracle 9i, IAS 9.0.4

From: jatix <jatix8_at_gmail.com>
Date: 4 Jan 2006 00:09:02 -0800
Message-ID: <1136362142.389341.130150@g47g2000cwa.googlegroups.com>

stephen O'D ha escrito:

> jatix wrote:
>
> > Hi, I have Oracle 9i, IAS 9.0.4, and I use Web PL/SQL. I have a only
> > connect user from database and a role with permissions to object
> > access.
> > For users accessing I can't use default roles (except connect) and I
> > can't give grantings directly from user (grant execute from package not
> > permited by customer).
> >
> > How can I active the role ???
> > How can I keep the role active in calls between the packages???
> >
> > I called a procedure with "authid current_user", and I executed
> > dbms_session.active_role("ALL"), but then when I use other package, I
> > can't see the others packages, althoug the role granted.
> >
> > Regards.
>
> In the default case, Roles are disable in PLSQL - if a user attempts to
> access an object not owned by it, and it was given access via a role,
> it will not be able to use the object in PLSQL. If the priv was given
> directly to the user, then it will be able to access it.
>
> You can test this out by selecting from session_roles inside a
> procedure and from within SQLPLUS.
>
> Now, if you create a procedure as authid current_user, the roles
> granted to the calling user become active while it is accessing that
> procedure - if that procedure calls another that has not been defined
> as authid current_user, the roles become disabled again until control
> returns to the original procedure.
>
> The only way for you to fix your problem, is to define everything as
> authid current user (but then the user is going to need grants or roles
> on every object accessed by the package, which is not very secure at
> all), or to grant execute on the pacakges you require directly to the
> user.

Thanks. Received on Wed Jan 04 2006 - 02:09:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US