1

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

1
  • 1
    Ensure that the newly created view is owned by the user which has appropriate privileges on the underlying views and tables.
    – YasirA
    Jul 16, 2013 at 7:38

2 Answers 2

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. :)

0

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.

1
  • Solved. Both replies served the purpose. Second one was quite elaborate. Thanks guys for your time and concern.
    – Ali
    Jul 17, 2013 at 4:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.