Re: Roles, packages and procedures

FAQ:
Roles and stored procedure does not mix. Any role you have are disabled during the execution of a stored procedure. Roles cannot be used to authorize access within a stored procedure. Roles are dynamic and procedures static, hence a static authentication is required. Using roles is not possible.

The answer to the original question must then be, that if you which to set functionality depending on the user, you will have to implement a grouping of users yourself, in a table. Query this table in the package, determind the user-ground and act accordingly in the package.

Regards
  Peter H. Larsen
  Senior Oracle Consultant

Thomas Pall wrote in message <365b4c4d.0_at_feed1.realtime.net>...
>How about creating a role. Grant the role to some users, not to others.
>In your procedure, query user_role_privs to see if they have been granted
>the role. If they have, run. If they have not, exit.
>
>B. Hotting (bhotting_at_wxs.nl) wrote:
>: Hi,
>
>: I found out that you can only grant execute rights to a package
>: and not to a procedure in the package. So different roles can't
>: execute different procedures in the same package.
>
>: eg. grant execute on pkg_sallary.raise to boss;
>: doesn't work.
>
>: Let me describe my problem in more detail..
>: I have a package with stored procedures. I want to keep these procedures
>: together in the same package because they are related.
>: I also have some roles. let's say a 'boss' and 'employe' role.
>
>: How can i grant execute rights so the 'employe' hasn't access to the
>: "boss's" procedures ?
>
>: 1) I could write the package twice, but that is not efficient (any
>: change...)
>
>: 2) I could build another layer of procedures (or views if thats possible
>: for procedures) but that will mean that on the client site, depending on
>: who is working with the application, the software has to call a different
>: function. Which is not nice.
>
>: 3) I could split up my packages for 'boss' and 'employe' but if after a
>: change,
>: when some procedure becomes available to the other roll, it will have to
>: move to
>: another package, wich is not what i want.
>
>
>: Any good suggestions ??
>
>: Bjorn Hotting
>
>
>
>
>--
Received on Wed Nov 25 1998 - 07:28:09 CST