sqloracle-databaseviewsql-grantora-00942

Oracle Create View issue

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?

Solution

  • 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.