Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Ensure that the newly created view is owned by the user which has appropriate privileges on the underlying views and tables. –  Yasir Arsanukaev Jul 16 '13 at 7:38

2 Answers 2

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

share|improve this answer

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.

share|improve this answer
    
Solved. Both replies served the purpose. Second one was quite elaborate. Thanks guys for your time and concern. –  Ali Jul 17 '13 at 4:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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