CODEWITHSUNDEEP
sqloracle11gprivilegescreate-view
David Lacombe
David Lacombe

Reputation: 61

grant create view on Oracle 11g

I use SQL*Plus for school and I use the username Scott. I cannot create views because it says:

ORA-01031: insufficient privileges;

I've searched and searched, but nothing seems to get it right. Any help?

Upvotes: 5

Views: 102109

Answers (4)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

You need to GRANT the CREATE VIEW privilege to the USER which is creating the view.

For example, I create a new user to let it create a session, a table and a view:

SQL> create user test identified by test;

User created.

SQL> grant create session, create table, create view to test;

Grant succeeded.

SQL> conn test/test@pdborcl;
Connected.
SQL> Create Table advanced
  2   (Id  varchar(15),
  3   Name varchar(20),
  4   Dept  varchar(15),
  5   Cgpa  float,
  6   Birth_date date,
  7   Mob_no  int,
  8   Dist varchar(20),
  9   Salary  number(8));

Table created.

SQL> Create View advanced_data as
  2  (
  3  select name,dept,dist,salary from advanced
  4  );

View created.

If I revoke the privilege, you will recieve ORA-01031: insufficient privileges:

SQL> revoke create view from test;

Revoke succeeded.

SQL> conn test/test@pdborcl;
Connected.
SQL> Create or replace View advanced_data as
  2  (
  3  select name,dept,dist,salary from advanced
  4  );
Create or replace View advanced_data as
                       *
ERROR at line 1:
ORA-01031: insufficient privileges

Upvotes: 1

naseem ahmad
naseem ahmad

Reputation: 1

step 1-conn ss/ss as sysdba;
step 2- GRANT CREATE ANY VIEW TO Scott;
step 3- conn scott/tiger
step 4-create or replace view v as select *from emp;

Upvotes: 0

Jan Vitásek
Jan Vitásek

Reputation: 33

To log-in as DBA (database administrator) you can use:

sqlplus / as sysdba

or

sqlplus sys as sysdba

Upvotes: -1

mkubacki
mkubacki

Reputation: 585

As the error states - your privileges are insufficient to create view - you will have to ask database administrator to grant you this privilege. If you can log in as database administrator you will have to execute statement(I can't guarantee correctness, no oracle database at hand)

GRANT CREATE ANY VIEW TO Scott;

or

GRANT CREATE VIEW TO Scott;

Upvotes: 17

Related Questions