Home » SQL & PL/SQL » SQL & PL/SQL » create a view in schema1 that references a view in schema2
icon5.gif  create a view in schema1 that references a view in schema2 [message #246967] Fri, 22 June 2007 11:25 Go to next message
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 #246968 is a reply to message #246967] Fri, 22 June 2007 11:30 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
"ORA-01031: insufficient privileges".


you should have
some privileges like that

create view  	create any view  	drop any view  	under any view
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 Go to previous messageGo to next message
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
icon14.gif  Re: create a view in schema1 that references a view in schema2 [message #246985 is a reply to message #246967] Fri, 22 June 2007 13:46 Go to previous message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Thank you very much.
It was the second issue you raised that was the problem.
Privileges were granted to a role and not to the schema.
Once we ran ...

    GRANT SELECT ON viewname TO schem1


within schema2 I was able to create the view.

Thanks again.
Previous Topic: Grabbing most recent data
Next Topic: find column name in database
Goto Forum:
  


Current Time: Tue Feb 11 13:44:55 CST 2025