Re: ORA-01933 error..

How does this user have access to user2's tables? If it's by way of a role, that's why you're getting the error. You must grant select on user2.table1, user2.table2, etc directly to the user creating the new view.  You cannot use a role to gain access to user2's tables for the purpose of creating a view or other stored object.

Hope this helps!

-- 
Stephen A. (Tony) Rodgers
Prominence Consulting, Inc.

Jeff Murphy <jcmurphy_at_acsu.buffalo.edu> wrote in article
<557rg0$jo_at_prometheus.acsu.buffalo.edu>...


> i have a user (user1) that belongs to a role (role1). role1 has the 

> privilege "CREATE VIEW". 

> 

> the user can do the following with no problem:

> 

> create view test as select table_name, tablespace_name from all_tables;

> 

> but can not to this:

> 

> create view test as select item1, item2 from user2.item_list;

> 

> where "user2.item_list" is a view into several of user2's tables. 

> 

> user1 has select perms on the views and tables of user2.

> 

> here is the error message:

> 

> ORA-01933: cannot create a stored object using privileges from a role

> 

> the manuals indicate "grant the priv directly to the user" as the

> workaround. i tried granting "create view" directly.. but the same error

> occurs.  

>