ORA-01031 Insufficient privileges while CREATING a VIEW?

7
votes

When I try to create a view that including different tables I'm getting the following error: Error at Line 1: ORA-01031 Insufficient privileges.

Could anyone tell me what could be the problem. I tried following the another stackoverflow post mentioned here but it's pertaining to different schemas.

ORA-01031: insufficient privileges when selecting view

Please let me know as I'm new here.

My Query is as follows:

ORiginal Question:Create a view to select employee ID, employee name, hire date, and department number.

MY SOLUTION: 

CREATE VIEW SIMPVIEW AS
SELECT EMPNO, ENAME, HIREDATE,DEPTNO
FROM EMP;
6
sqloraclevieworacle11g
Do you have the create view privilege? Do you own the emp table, or are you accessing it in another schema (e.g. the HR sample schema)? If it's in another schema, do you have the select priv against it through a role or directly? – Alex Poole
Thanks for your reply. Yes, I'm running queries on my computer with Oracle 11g installed. So, I'm my own DBA but since I'm new I'm not sure what's going wrong. – Tan
And yes, I have created emp table. – Tan
Apparently the user you are using doesn't have the create view privilege. You need to grant that the same way you granted the create table privilege. Check the manual for details: docs.oracle.com/cd/B28359_01/server.111/b28286/… – a_horse_with_no_name
Actually, I didn't grant any privilege to create table privilege. Also, I'm logged in as an administrator on my computer. – Tan

6 Answers

6
votes

Then probably you may not have the privileges to perform the CREATE VIEW command in your database schema... Log in into SYSDBA account and issue the command 

GRANT CREATE VIEW TO <dbusername>;

Here <dbusername> should be replaced with the name of the user you want to give access to the CREATE VIEW command.

2
votes

You can check if your user has VIEW creation privileges using select * from session_privs.

Note that to be able to create a view, the user that is creating it needs to have been granted SELECT privileges on all the objects being used, as well as the mentioned CREATE VIEW privilege. You can also check that by querying to USER_TAB_PRIVS with the user getting the error.

1
votes

when I wanted to execute the above query in sql developer I faced issues as I did not have enough privileges to create a view or other oracle object schema such as trigger, packages, procedures etc. I found the error to i.e. “Error at Line 1: ORA-01031 Insufficient privileges”. so, I needed the all privileges to practice all these queries and programs. I took the following steps in order to solve my problem:

  1. As I logged in as a user name ‘scott’, so my name is ‘scott’ not ‘Dhruv’. My ambition was to grant all the privileges to me i.e. to the user ‘scott’.
  2. For that, I need to enter in the database as a DBA. Now, question is! How to log in as DBA. For this, I opened command prompt and I logged in the database as sysdba by following the below steps:

a) In window run, I typed cmd to open command prompt. I typed: sqlplus /nolog which means that I logged in without providing required credentials.
b) I authenticated myself for my underlying O/S and entered in database as DBA. For that, I typed in command prompt: connect / as sysdba; c) I evaluated who is the DBA user in my database if exists. For that I typed: select name from V$database; d) Here we go after this command. I finally granted myself (scott) to create view in sql developer by typing the command: grant create view to scott; e) Finally, I granted myself all the privileges by typing: grant all privileges to scott;

Snapshot of command prompt: I have attached.

Finally, I executed and created my view: I have attached

1
votes

I had this error, and the solution was to grant select WITH GRANT OPTION to a table from another schema that was included in the view.

0
votes
  1. At first You need to give the user authentication so you need to know who dba in normal the system give this authentication so make conn system/ *password*
  2. give grand or authentication by put grant create view to *DataBaseUsername*;
  3. make the connection to your user and apply your command
-1
votes

You have to give select any table privilege to the user. Then the view will compile successfully. No need to explicitly grant select to the user to all the objects.