ORA-01031 Insufficient privileges while CREATING a VIEW?

Question

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;

Answer 1

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.

Answer 2

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.

Answer 3

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