RE: Permissions in PL/SQL

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hand, Michael T Sent: Thursday, April 09, 2009 10:02 AM
To: oracle-l_at_freelists.org
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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 09 2009 - 09:28:51 CDT