Re: Create Views with object-privs from Roles

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 01 Oct 1998 17:55:16 GMT
Message-ID: <3627c0a4.108468910@192.86.155.100>

A copy of this was sent to "Loy&Hutz" <loyhutz_at_dinx.de> (if that email address didn't require changing) On 1 Oct 1998 18:01:19 -0200, you wrote:

>Create Views with object-privs from Roles
>
>I like to create views in Schema B on Table in Schema A
>
>
>I give User B (having create any view-privilege) object privileges to Table
>in Schema A
>(GRANT SELECT, INSERT, UPDATE, DELETE ON SchemaA.TableA TO Userb WITH GRANT
>OPTION)
>plus privilege "references" (although I don´t know, what this means)
>
>I create the view
>
>all ok.
>
>But I have a lot of users like B. So I like to use a role
>
>I create a role with exact the same object privileges
>(GRANT SELECT, INSERT, UPDATE, DELETE ON SchemaA.TableA TO RoleX WITH ADMIN
>OPTION)
>
>I granted the role to user B
>
>I can access the tables (select * from A.T_Taetig2 WHERE M1 = 34) BUT:
>
>Create view B.Test3 As select * from A.T_Taetig2 WHERE M1 = 34
>
>Causes (german):
>ORA-00942: Tabelle oder View nicht vorhanden
>
>english: Table or view does not exists.
>
>Are object privileges via roles not valid for "create view" ?
>

that is right. It is more correct to say "you cannot create STORED OBJECTS that reference objects you have access to via a ROLE".

>But this sentences in the ORA-help indicates the opposite:
>"If you revoke an object privilege on an object from a user whose schema
>contains a view on that object, Oracle invalidates the view"
>

No, it says "from a user" not from a "role".

>any idea ?
>
>Greetings Sven Kunze
>
>
>ENVIRONMENT:
>PROBLEMS / TESTED WITH Visual Basic 4.0 16 Bit / Access 2.0 / Access 97 -
>Win NT 4.0 SP3 / Win 95
>ODBC-drviver: Oracle 8.00.0400 (=Version 8.0.4.4.0) (SQORA32.DLL 04.05.98)
>Oracle Server 8.00.4.0-Server
>

There are a couple of reasons why roles are not active in stored, compiled objects. the predominant one is performance.

security for views is compiled/set at view creation/compilation time. 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/shouldn'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 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 Oct 01 1998 - 12:55:16 CDT