Home » SQL & PL/SQL » SQL & PL/SQL » Create a view based on a public synonym (Oracle all versions)
Create a view based on a public synonym [message #337515] Thu, 31 July 2008 02:24 Go to next message
muthu.sp
Messages: 2
Registered: July 2008
Location: Chennai
Junior Member

Hi I'm trying to create a view based on a public synonym, but getting "ORA-01031: insufficient privileges" error.

When i retrieve records from the same public synonym, i could able to do so. But when i try to create a view based on that synonym, it is not possible. Why is it? Can anyone please explain?

11:58:04 IT00053.it0053@SCOTT> connect sys as sysdba
Enter password: ******
Connected.
11:58:14 IT00053.it0053@SYS> create role general_user_role;

Role created.

Elapsed: 00:00:00.14
11:58:33 IT00053.it0053@SYS> connect scott
Enter password: *****
Connected.
11:58:41 IT00053.it0053@SCOTT> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- --------------------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
DEPT_TAB SYNONYM

Elapsed: 00:00:00.01
11:58:47 IT00053.it0053@SCOTT> create public synonym scott_emp for scott.emp;

Synonym created.

Elapsed: 00:00:00.06
11:59:14 IT00053.it0053@SCOTT> grant select on scott_emp to general_user_role;

Grant succeeded.

Elapsed: 00:00:00.14
11:59:35 IT00053.it0053@SCOTT> connect sys as sysdba
Enter password: ******
Connected.
12:00:13 IT00053.it0053@SYS> create user beginning_user
12:00:31 2 identified by beginning_user
12:00:38 3 default tablespace users
12:00:45 4 temporary tablespace temp;

User created.

Elapsed: 00:00:00.04
12:00:53 IT00053.it0053@SYS> grant connect, resource, create table, create view to beginning_user;

Grant succeeded.

Elapsed: 00:00:00.01
12:01:13 IT00053.it0053@SYS> grant general_user_role to beginning_user;

Grant succeeded.

Elapsed: 00:00:00.01
12:01:35 IT00053.it0053@SYS> connect beginning_user/beginning_user
Connected.
12:01:49 IT00053.it0053@SYS> connect beginning_user
Enter password: **************
Connected.
12:02:01 IT00053.it0053@SYS> show user
USER is "BEGINNING_USER"
12:02:06 IT00053.it0053@SYS> select * from scott_emp;

EMPNO ENAME JOB MGR HIREDATE SAL
-------------------- ---------- --------- -------------------- --------- --------------------
COMM DEPTNO
-------------------- --------------------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30

7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

7844 TURNER SALESMAN 7698 08-SEP-81 1500
0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10


14 rows selected.

Elapsed: 00:00:00.21
12:02:16 IT00053.it0053@SYS> create or replace view new_view_1 as select empno, ename, job from
12:02:50 2 scott_emp;
scott_emp
*
ERROR at line 2:
ORA-01031: insufficient privileges


Why is this error?

Your help will be much appreciated...

Thanks and regards
Muthu
Re: Create a view based on a public synonym [message #337517 is a reply to message #337515] Thu, 31 July 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In order to create a view on an object you must have been granted DIRECT privilege on the object.

This is in the prerequisite of CREATE VIEW, please refer to the documentation before posting.

In addition, don't use predefined roles (connect, resource, dba), above all resource, create your own.

Regards
Michel
Re: Create a view based on a public synonym [message #337747 is a reply to message #337517] Fri, 01 August 2008 00:39 Go to previous message
muthu.sp
Messages: 2
Registered: July 2008
Location: Chennai
Junior Member

Thanks Mr.Michael.

Now I have tried the grant rights directly to the user creating view, and it works.

Thanks for the help.

Regards
Muthu
Previous Topic: How to split the serial number
Next Topic: Regarding columns of number and string datatype comparisons
Goto Forum:
  


Current Time: Fri Dec 09 21:41:41 CST 2016

Total time taken to generate the page: 0.10046 seconds