RE: Permissions in PL/SQL
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.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hand, Michael T Sent: Thursday, April 09, 2009 10:02 AM
Subject: Permissions in PL/SQL
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.
http://www.freelists.org/webpage/oracle-l Received on Thu Apr 09 2009 - 09:28:51 CDT