Oracle Create View issue

sql oracle view grant ora-00942
ntsue picture ntsue · Nov 2, 2010 · Viewed 7.9k times · Source

I am logged in as the dba account and I want to create a view in User1's schema, but selecting data from User2's.

I used the following query:

CREATE OR REPLACE VIEW User1.NewView (Column1) AS
SELECT DISTINCT Column1 FROM User2.Table

and I get the following error:

SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

To resolve this I had to grant select access to User1 on User2.Table. Is there a way to do this without having to grant access, since I am already logged in as the dba?

Answer

OMG Ponies picture OMG Ponies · Nov 2, 2010

Yes, you have (and always should) to explicitly grant access to objects in another schema.

GRANT SELECT ON user2.table TO user1

Though you're logged in as "the dba account" (SYS, I'm assuming), the CREATE statement is for the user1 schema specifically.

Related questions

What is the difference between Views and Materialized Views in Oracle?

What is the difference between Views and Materialized Views in Oracle?

Read More
How to view the original script of a materialized view?

I am interested in moving a materialized view from one db to the other, regardless, I also need to change one of the columns. How can I view the original script that build the MV? I am running TOAD, but …

Read More
Query on View is running slower than direct query - in Oracle

I tried creating view with parameters to get the data dynamically. (I cant use WHERE condition as the select statement itself changes based on the parameters) For this, I wrote a procedure which returns me the required data as oracle …

Read More