| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Missing Privs. When executing a Package procedure.
A copy of this was sent to "César Moreno Fernández" <cmoreno_at_csi.uned.es>
(if that email address didn't require changing)
On Fri, 26 Mar 1999 08:23:24 +0100, you wrote:
>Hello everybody:
>
> I heard that when you are executing a procedure from a Package,
>oracle doesn't matter your role privileges. You must have granted the
>privileges directly to your user, because the role privileges are
>missing while you are executing the procedure or function.
>
> I have been testing with the privileges of resource an connect role.
>I have tested with a procedure that create a table trigger. I couldn't
>until I granted enough privileges to de user.
>
> Does anybody know why? Is there a way to correct this?
>
> Thanks & Regards,
>
> Cesar Moreno.
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant <priv> on <whatever> to <OWNER>;
there are a couple of reasons why this is the case. the predominant one is performance.
pl/sql is stored compiled. It uses compile time binding for privs -- at compile time the privelege set is known. Consider the following example (@invalid runs a script that lists ALL invalid objects in my schema)
SQL> @invalid
no rows selected
SQL> grant imp_full_database to tkyte;
Grant succeeded.
SQL> @invalid
no rows selected
SQL> revoke imp_full_database from tkyte; Revoke succeeded.
SQL> @invalid
no rows selected
SQL> grant select any table to tkyte;
Grant succeeded.
SQL> @invalid
no rows selected
SQL> revoke select any table from tkyte; Revoke succeeded.
SQL> @invalid
OBJECT_TYPE OBJECT_NAME STATUS
--------------- ------------------------------ ----------
FUNCTION BLOB2HEX INVALID
COUNTEM INVALID
GETINTYPE INVALID
[every stored object i own is listed here]
...
Since the bindings for privs are figured out at compile time -- the simple act of REVOKING a priv forces all of my objects to become invalid -- they need to be recompiled at some point (the system will do that over time for me). I was able to recompile all of my objects after the revoke and get them all valid again.
ROLES are much more fluid (at least they are designed to be) then users as far as priv sets go. lets say that we let roles give us privs in stored objects then ANY TIME ANYTHING was revoked from ANY ROLE we had, or any role any role we have has (and so on -- roles can and are granted to roles) -- all of our objects would become invalid. Think about that -- REVOKE some privilege from a ROLE and suddenly your entire database must be recompiled!
Also consider that roles may be
I guess the bottom line is:
You have 1,000's or 10,000's of end users. They don't create stored objects. We need roles to manage these people.
You have 1's or 10's of application schema's (things that hold stored objects). For these we not only want to be explicit as to exactly what privileges we need and why (in security terms this is called the concept of 'least privileges' -- you want to specifically say what priv you need and why you need it -- if you inherit lots of junk from roles you cannot do that effectively), but we can actually manage to be explicit since the number of development schemas is SMALL (but end users are large)...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |