Problem compiling view when it is referencing a table in an other view: insufficient privileges

Question

Oracle 11g R2 Logged on: SYS / AS SYSDBA

When I try to compile or create a view that is referencing local schema tables. It works fine.

Problem does occur when I try to compile the same view referencing a table in another schema like schema.table in my query.

Oracle throws the exception ORA-01031: insufficient privileges.

Remember I am using SYS account (sysdba).

Answer 1

Even though you are using SYS (which you really shouldn't be), the view is stored in a SCHEMA1, as I will refer to it. SCHEMA1 is trying to select from a table, via the view, in SCHEMA2. Therefore, you need to grant SELECT access to SCHEMA2.TABLE to SCHEMA1.

And don't run things as SYS. :)

Answer 2

Here is how to replicate the error. Create two users with the needed system privileges. Each user creates a table and view. Test as SYS. The error occurs. user1 grants privileges to user2. Recreate the view. Works.

--setup users as SYS or another DBA user

CREATE USER user1 IDENTIFIED BY pw1;
CREATE USER user2 IDENTIFIED BY pw2;
GRANT CONNECT TO user1, user2;
GRANT CREATE TABLE TO user1, user2;
GRANT CREATE VIEW TO user1, user2;
GRANT UNLIMITED TABLESPACE TO user1, user2;

--connect as user1 and create user1 table and view

CREATE TABLE TABLE1 
(  COLUMN1A VARCHAR2(20) , COLUMN1B VARCHAR2(20) );

INSERT INTO user1.test1 VALUES ('ABC', 'DEF');
COMMIT;

SELECT * FROM user1.test1;

CREATE OR REPLACE VIEW user1.view1
AS SELECT * FROM user1.test1;

SELECT * FROM user1.view1;

--connect as user2 and create user2 table and view

CREATE TABLE TABLE2 
(  COLUMN2C VARCHAR2(20) , COLUMN2D VARCHAR2(20) );

INSERT INTO user2.table2 VALUES ('GHI', 'JKL');
COMMIT;

SELECT * FROM user2.table2;

CREATE OR REPLACE VIEW user2.view2
AS SELECT * FROM user2.table2

SELECT * FROM user2.view2;

--now user2 tries to select from user1 view
USER2> 
select * from user1.view1;
select * from user1.view1
                    *
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.01


USER2> 
CREATE OR REPLACE FORCE VIEW user2.view3
AS SELECT * FROM user1.view1;

Warning: View created with compilation errors.
USER2> 
SELECT * FROM user2.view3;
select * from user2.view3
                    *
ERROR at line 1:
ORA-04063: view "USER2.VIEW3" has errors

--connect sys as sysdba

Enter password:
Connected.
SELECT * FROM user2.view3;
select * from user2.view3
                    *
ERROR at line 1:
ORA-04063: view "USER2.VIEW3" has errors

ALTER VIEW user2.view3 COMPILE;
Warning: View altered with compilation errors.

SELECT * FROM user2.view3;
select * from user2.view3
                    *
ERROR at line 1:
ORA-04063: view "USER2.VIEW3" has errors

CREATE OR REPLACE VIEW user2.view3
AS SELECT * FROM user1.test1;
select * from user1.test1
                    *
ERROR at line 3:
ORA-01031: insufficient privileges

-- now connect user1 and grant privileges

Enter password:
Connected.
USER1>
grant select, insert, update, delete on user1.test1 to user2;

GRANT succeeded.

--connect user2 to recreate the view and test again

Enter password:
Connected.
CREATE OR REPLACE FORCE VIEW user2.view3
AS SELECT * FROM user1.test1;
VIEW created.
SELECT * FROM user2.view3;
COLUMN1A   COLUMN1B
---------- ----------
ABC        DEF
1 ROW selected.

-- now it works. It was not the SYS as SYSDBA that did not have privileges. User2 did not have privileges to user1.test1 table.