Re: stored function question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/08/13
Message-ID: <35e02d68.106076820@192.86.155.100>

A copy of this was sent to morris_newsgroup_at_yahoo.com (if that email address didn't require changing) On Thu, 13 Aug 1998 14:40:48 GMT, you wrote:

>Did you mean even if an owner granted with the privilege thru. a role, the
>same owner creating the stored function will not be able to use this
>privilege when compiling? Instead, this owner needs to be granted the
>privilege directly.
>

i don't understand that completely -- "if an owner granted (??? granted what ???) with the privilege thru. a role...."

If you do not have a privelege directly granted to you (eg: CREATE TABLE, CREATE PUBLIC SYNONYM, etc) you will not be able to have that privelege in the procedure. So, if you created a table in SQLPlus after having been granted the RESOURCE ROLE, you'll be able to write a procedure that can DROP that table (you own it -- you have the privelege to drop it directly) but you cannot write a procedure to CREATE it again. You'll need to have CREATE TABLE granted to you.

>Why can't oracle use the roles granted to this owner when compiling?
>

there are a couple of reasons. 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

so what the above showed that granting/revoking a role to/from a user did nothing to that users schema. No problems, no invalid objects. Now, lets grant some privilege and then revoke it from the same user:

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

non default. if I have a non-default role and I enable it and I compile a procedure that relies on those privileges, when I log out I no longer have that role -- should my procedure become invalid -- why? why not? you could argue both sides.
password protected -- same issues as above.

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)...

hope this helps..

>Thanks,
>Morris
>
>----------------------------------------------------------------
>In article <6qtams$jjm$1_at_home.kpbank.ru>,
> "skv" <skv_at_kpbank.ru> wrote:
>> hi Moris
>> morris_newsgroup_at_yahoo.com ÃÃ‰Ã›Ã…Ã” Ã— Ã“ÃÃÃ‚ÃÃ…ÃŽÃ‰Ã‰ <6qt7je$uug$1_at_nnrp1.dejanews.com>
>> ...
>> >I can do a select on a SYNONYM of a table
>> >at the sqlplus prompt successfully. However,
>> >when I referred to this SYNONYM in a stored
>> >function, Oracle had trouble in compiling the
>> >stored function. Suppose the name of the SYNONYM
>> >is called 'USERPRODUCT', I got the following error
>> >when compiling:
>> >
>> >SQL> show errors;
>> >Errors for FUNCTION SF_GET_SEARCH_CNT:
>> >
>> >LINE/COL ERROR
>> >-------- -----------------------------------------------------------------
>> >6/1 PL/SQL: SQL Statement ignored
>> >6/31 PLS-00201: identifier 'USERPRODUCT' must be declared
>> >
>> >Does anyone know why?
>> GRANT SELECT ON USERPRODUCT TO your_function_owner
>> solves yor problem
>>
>> Regards
>>
>> ---------------------------------
>> Konstantin V. Sartakov
>> Kuzbassprombank
>> Kemerovo
>> Russia
>> mailto:skv_at_kpbank.ru
>>
>>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Aug 13 1998 - 00:00:00 CDT