insufficient privileges in oracle 10g

Question

i have created a table which is as belows .

The structure of the table is as follows :

Create Table advanced
 (Id  varchar(15),
 Name varchar(20),
 Dept  varchar(15),
 Cgpa  float,
 Birth_date date,
 Mob_no  int,
 Dist varchar(20),
 Salary  number(8));

When I want to create view as follows the sql plus gives me error 'insufficient privileges ' .

Create View advanced_data as
(
select name,dept,dist,salary from advanced
);

How can I solve this issue ?

As a general rule, don't discard the ORA-... error code. It's a key tool to search for a solution. — Álvaro González, Nov 19 '15 at 08:26

Rohit Batta · Accepted Answer · 2015-11-19T08:31:45.713

This seems, that your user is not having permission for creating views.

To solve this follow these steps: Login to sqlplus by sys user as sysdba and then run this command.

GRANT CREATE VIEW TO <user Name>;
GRANT CREATE ANY VIEW TO <user Name>;

Oracle DBA Blog The ORA-01031: "insufficient privileges" error occurs when you attempt to execute a program or function for which you have not been granted the appropriate privileges.

For the DBA, the ORA-01031 can happen if the target OS executables do not have read and execute permissions (e.g. (770) in UNIX/Linux), and ensure that the oracle user is a member of the dba group (e.g. /etc/group). There are similar permission in the Windows registry.

Inside Oracle, the "ORA-01031: insufficient privileges" error can be avoided by signing on "as sysdba" with unlimited database privileges.

score 1 · Answer 2 · answered Nov 19 '15 at 08:25

From here:

To create a view, you must meet the following requirements:

You must have been granted the CREATE VIEW (to create a view in your schema) or CREATE ANY VIEW (to create a view in another user's schema) system privilege, either explicitly or through a role.

You must have been explicitly granted the SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view or the SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges. You may not have obtained these privileges through roles.

Additionally, in order to grant other users access to your view, you must have received object privilege(s) to the base objects with the GRANT OPTION option or appropriate system privileges with the ADMIN OPTION option. If you have not, grantees cannot access your view."

So you need to execute the below query to get the permission for the user to create a view:

GRANT CREATE VIEW TO username

The Oracle docs also explicitly states that:

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

insufficient privileges in oracle 10g

2 Answers2