Creating View againt other user's view

I thought of sharing one small issue I experienced yesterday  while I was trying to help an end user who is not that good in writing sql with hints.

This user had access to some views which are granted to him via a role and user has create view privileges.  Since his queries to this view is running for hours and he is not good in listening to my advises about using hints ( he claim his SAS tool is not passing hints. I suspect it is not correct),  I thought I could help him by creating a view in his schema so that others won’t see it and he could execute it and query will finish in less than 30 minutes instead of 8 hours or longer.

So I created a script like this and executed as a user with DBA privileges. User name obscured to hide the identity of the real user.

SQL> CREATE OR REPLACE FORCE VIEW ABCDE.DONT_CONTACT_VIEW      

(

BAN,

PCS_NUM

)

AS

SELECT /*+ PARALLEL(a,16) full(a) */ BAN, PCS_NUM

    FROM   CIRADM.CUSTOMER_PREFERENCE_VW a

    WHERE DO_NOT_CALL          = 'Y'

     OR         DO_NOT_EMAIL         = 'Y'

     OR         DO_NOT_MAIL                = 'Y'

     OR         DO_NOT_SMS           = 'Y'

     OR         DO_NOT_VOICEMAIL     = 'Y'

WITH READ ONLY;

I got following message.

Warning: View created with compilation errors.

SQL> desc ABCDE.DONT_CONTACT_VIEW;

ERROR:

ORA-24372: invalid object for describe

Then I realized Oracle’s rules on creating views on views. A user needs to have access to a view or table via direct grant  and not through ROLE privileges to create  a view on these objects . And that is the reason I am getting above message. One way to avoid this issue to grant select privileges on CIRADM.CUSTOMER_PREFERENCE_VW as follows and then execute create view script.

Grant select on CIRADM.CUSTOMER_PREFERENCE_VW to ABCDE;

Or create this view in CIRADM schema and grant select privileges to ABCDE user.

Under SQL, if a user can select another user's table and has the privilege to create a view, the create view works. Yet, a create view on the other user's table generates ORA-00942 if the select

privilege has been granted to a role and not directly. This is true for procedures too.