Oracle Create View issue

Question

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 1

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.

Answer 2

You can do CREATE OR REPLACE FORCE VIEW ...

That will create the view despite the lack of privileges, but the view would not be usable unless the privileges are granted. It is a solution that can be useful if privileges will be granted later (eg in some automated build script).