Re: Privs thru Roles (was Re: sys.dbms_pipe & triggers)

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/03/29
Message-ID: <4jgt8d$d9o_at_inet-nntp-gw-1.us.oracle.com>

tcox_at_netcom.com (Thomas B. Cox) wrote:

[snip]

>>What the real problem is is in permissions. In order to stored a
 compile object
>>(view, procedure, function, package, trigger) you must have priveleges
 granted
>>directly to you.
 

>In my 7.2.2 instance (which I should have checked before posting the
>first time), the only permission on DBMS_OUTPUT is a grant of EXECUTE by
>SYS to PUBLIC.
 

>Permissions on DBMS_OUTPUT may be granted through a role. See the
>output at the end of this posting.

No they can't. Public is *not* a role. DBA, CONNECT, RESOURCE, etc are roles. PUBLIC is not. If you granted something to PUBLIC it is as if you have granted it to each and every user in the system directly. Perhaps I should modify my statement:

In order to stored a compiled object, you must be able to disable all currently active roles and still be able to access the object; that is, you must either have gotten the privelege for the object granted directly to YOU or PUBLIC access must have been given.

>There are some things you have to have granted to you directly -- i.e.
>you can't get them through a role and have things work correctly -- but
>EXECUTE isn't one of them, at least on DBMS_OUTPUT. I can't recall what
>database operations have this limitation (and I'm done guessing, at
>least for this thread).
 

>>grant execute on dbms_pipe to USERNAME
>>when logged in as sys and your trigger will compile.

[snip]

BTW, In case you are interested, this is from one of my postings in October last year trying to explain why you can't store compiled objects which privs from a role:

What if I as the owner of THE_TABLE....

create public synonym THE_TABLE for THE_TABLE; grant select on THE_TABLE to THE_ROLE;

create user bob identified by bob;
grant connect, resouce, the_role to bob; alter user bob default role all except the_role;

connect bob/bob;

select * from THE_TABLE;
*** Error

SET ROLE ALL;
select * from THE_TABLE; -- succeeds;
create view MY_VIEW as SELECT * FROM THE_TABLE; -- Assume it could succeed select * from MY_VIEW; -- succeeds;

disconnect

connect bob/bob
select * from the_table; -- FAILS
select * from MY_VIEW; -- What should this do?

The reason i wrote "what should this do" is that permissions, for justified performance reasons, are stored with the compiled objects (views and procedures). When I generated the view MY_VIEW, it checks my authorization and sets up a dependendecy between my compiled view and the real table. The only thing that will invalidate the view definition is if someone changes the grants on THE_TABLE. My simple act of logging out changed my privilege set behind the covers (since the_role is not default but must be enabled) hence the "what should this do?" If you say "It should Fail of course" then the ramification would be that views and stored procedures COULD NOT BE COMPILED, not even from call to call. The privelege set I have can change from call to call and not because someone touched the object I am dependent on but because I enabled or disabled a role. If you say "It should Succeed" then you have just opened up a massive whole in the underlying DAC model.

ONE very popular way people use roles (it may not be what you are using them for) is to control access to objects so that only an application can insert/update/delete/select from the tables. The application logs into the database and enables a password protected role that was granted to the user. The user doesn't really know what the password is (the password can even be stored encrypted in the database so that it changes periodically, i've seen people implement this). So anyway, suppose we could create views/procedures on top of objects we have access to via a role. Then, if access to the view/procedure should fail when the role is not enabled, performance goes down the tubes. No such thing as shared sql, no reusing cursors, no nothing. The view/procedure could not be stored compiled since we have to compile each time checking our current permission set for that statement. If on the other hand, we let it succeed when the role is not enabled, we can't use roles to implement what I just described.

IMHO- roles are for end users and applications. End Users do not create database objects, they use existing ones. Applications are built on a schema that already exists. End Users and Applications are not creating views and procedures.

Roles are NOT for developers. Developers need to build stored, compiled objects. These objects need to be compiled once, not once per execution.

sizeof( development staff ) < sizeof( end users )

My development staff is small and I can control their grants via scripts. My user community is large. I need help (roles) here.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

---

opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Fri Mar 29 1996 - 00:00:00 CET