Re: why can I not create a stored object using privileges from a role

steve.clover_at_syntex.com writes:
:
: I am curious...
: Does anybody know the reason why you cannot create a stored object using privileges from a role.
:
: For example if we grant select any table to a role and grant that role to a user, that user cannot create a
: view based on a SYS table. You get ORA-01993
:
: Cheers,
: Steve Clover
: ORACLE DBA
: Syntex Development Research, Maidenhead, Berkshire, England.
:

This is documented in the Application Developer's Guide for Oracle7.

To create any stored object(ie, view, procedure, function etc), any Object Privileges which are required to create that object MUST be granted directly, and not via a role.
Note this is not true of the required System Privileges. For example, to create a view, the CREATE VIEW System Privilege may be granted via a role. However, if the view references a table owned by another user, the SELECT privilege on that table must be granted directly to create the view, and not via a role.

This is the documented desired behavior.

--
regards,
Carl
+-----------------------------------------------------------------------------+
Carl Gohringer, European Development Centre 
Oracle Park, Bittams Lane, Chertsey, Surrey, England, KT16 9RG
Internet   : cgohring_at_uk.oracle.com    
+-----------------------------------------------------------------------------+