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

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. Received on Tue Jan 03 2006 - 17:28:44 CST