create a view in schema1 that references a view in schema2 [message #246967] |
Fri, 22 June 2007 11:25  |
tomstone_98
Messages: 44 Registered: May 2007 Location: Atlanta
|
Member |
|
|
I have searched for an answer to this problem in this OraFAQ forum but have not found anything yet.
The error "ORA-01031: insufficient privileges" was received while trying to create a view in schema1 that references a view in schema2.
I can run
SELECT x, y, z
FROM schema2.viewname
from schema1 successfully (i.e. I have the privileges), but when I try to run ...
CREATE OR REPLACE VIEW schema1.viewname
( x, y, z)
AS
SELECT x, y, z
FROM schema2.viewname
I get the error "ORA-01031: insufficient privileges".
Thanks.
|
|
|
|
Re: create a view in schema1 that references a view in schema2 [message #246972 is a reply to message #246967] |
Fri, 22 June 2007 11:46   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Database SQL Reference, "CREATE VIEW" page, "Prerequisites" section:
Quote: | To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege.
The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.
|
I bet at least one of the 2 is false.
Regards
Michel
|
|
|
|