ORA-01031 DURING A SELECT

(Doc ID 781053.1)

Last updated on NOVEMBER 10, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.
Information in this document applies to any platform.
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4

Goal

View created in Schema 3 has objects from Schema1 and Schema2.

This view needs to accessed from Schema4 and during select it reports ORA-01031.

SQL> conn prath2/prath2
Connected.

SQL> create view p1scott as
2 select a.DNAME,a.LOC,b.PRNAME
3 from scott.dept a,prath1.prath b
4 where a.DEPTNO=b.PRNO;

View created.

SQL> select * from p1scott;

DNAME LOC PRNAME
-------------- ------------- -------
ACCOUNTING NEW YORK engg
RESEARCH DALLAS team
SALES CHICAGO manager


SQL> grant select on prath2.p1scott to prath3;
Grant succeeded.

SQL> conn prath3/prath3
Connected.

SQL> select * from prath2.p1scott;
select * from prath2.p1scott
*
ERROR at line 1:
ORA-01031: insufficient privileges

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms