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

Thomas J Kyte <tkyte_at_us.oracle.com> wrote:
>tcox_at_netcom.com (Thomas B. Cox) wrote:
 

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

  I was vague. You can grant EXECUTE on 'Foo' through a role.   And the grantee can then run an anonymous PL/SQL block that calls   'Foo' or can use the SQL command "Execute Foo". But you can't create   your own stored object that calls 'Foo' if your only access to 'Foo'   is through roles.

  I had lost track of the fact that this thread was about creating   stored objects.

  <really interesting stuff snipped>

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

  I just realized that in all my apps to date, all stored executables   were owned by the app owner. Hence this was never an issue.

  Thank you for setting me straight.

 -Tom

-- 
Thomas B. Cox   <tcox_at_netcom.com>