RE: Permissions in PL/SQL
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <HANDM@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 9 Apr 2009 10:28:51 -0400
I'll take stab.
You have to have a direct grant to execute privilege within any
procedure, function package etc. (not a role). So SYS would have to
grant directly to owner of code.
If owner A owned some code, and users b, c, and d wished to execute
A.code with AUTHID current_user.
Owner A could not compile his code without having the grant... leaving
you back at the beginning. Now Owner A could just grant execute on
A.code to users b, c, and d... With AUTHID, now users, b, c, and d
have to also have the ability to execute select statements on X tables
which is more administration.
A better example of AUTHID would be if owner A owned procedure A.code,
and users b, c, and d wanted to execute the code, but the code would
manipulate data owned by each user respectively, b's tables, c's tables,
d's tables...
Others can concur, or point out mistakes.
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hand, Michael T
Sent: Thursday, April 09, 2009 10:02 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Permissions in PL/SQL
Greeting,
I am trying to expand my horizons by learning more about PL/SQL and
convert an anonymous block I wrote ages ago into a procedure. I've
learned that Types cannot be defined in a procedure so am building a
package and have run into a problem. The code block selects from
SYS-owned objects and X$KTFBUE. The original block was run from SYS,
but I don't want go create the package there so I thought I could
execute the create package from SYS with a different schema as owner
(UTILITY) and the clause AUTHID CURRENT_USER to get similar permissions.
I get compile errors indicating table access failures and I can't grant
select on X$ tables. Am I missing something, or is my only choices to
convert the code to use the DBA_LMT_USED_EXTENTS view instead or have
SYS as the package owner? (Not about to use the 2nd choice.)
This is on a 9i database if that has any bearing.
Thanks,
Mike Hand
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
