SQL & PL/SQL

Hi,

I'm on Oracle9i - 9.2.0.6.0.

My problem is:

User user_one has created two views. Both views query tables only, and all tables are owned by user_one:

create or replace view view1
as
   select *
     from table1, table2;

create or replace view view2
as
   with table5 as (select * 
                     from table4)
   select *
     from table5, table6;

grant select on view1 to user_two;

grant select on view2 to user_two
/

Now, conencted as user_two I can do:

select * from user_one.view1 where 1=2;
no rows selected

select * from user_one.view2 where 1=2;
ORA-01031 insufficient privileges

I have checked user_tab_privs:

select * from user_tab_privs where grantor = 'USER_ONE';
2 rows selected 

Tells me that user_two has SELECT privilege on both views, and no other object priviliges.

I have also tried this.

select * from user_col_privs;
no rows selected.

And just to check whether I have grants on underlying objects, granted through roles:

select tp.*
  from user_role_privs rp, role_tab_privs tp
 where rp.granted_role = tp.role
   and tp.owner = 'USER_ONE';
no rows selected

I have checked whether the views are VALID, just in case

select * 
  from all_objects
 where owner = 'USER_ONE'
     and object-name in ('VIEW1', 'VIEW2');

Which tells me that both views are VALID.

Now I'm running out of ideas. I'm experiencing this in a UAT environment, and code has passed DEV and TEST with no problems. I can't see any differences between the environments.

The only difference in views - apart from querying different tables - is that the one having the problem is using WITH. I'm NOT using /*+ materialize */ on this (Not sure if this would have mattered.

So, all input is appreciated, I'm stuck.

Regards
Peter

Subject corrected
Message was edited by:
Peter Gjelstrup

SOLVED
Message was edited by:
Peter Gjelstrup