CODEWITHSUNDEEP
sqloracle11goracle-sqldeveloper
SQL_rookie
SQL_rookie

Reputation: 153

Unable to create view - insufficient privileges

i'm unable to create a view - i'm getting insufficient privileges. Workflow -> create role -> grant create view to role -> create user -> add role to user -> try to create view and i get the error. Any help would be greatly appreciated

Created a role 

CREATE ROLE enrolment;

Granted the below privileges to the role - base table hr.students

GRANT insert, select, update
ON hr.students
TO enrolment;

Also granted create view privilege to the role

GRANT create view 
TO enrolment;

Created the below user

CREATE USER enrol1
IDENTIFIED BY pw4321;

Granted the role to the account

GRANT enrolment
TO enrol1;

When i try to create the below view on base table hr.students, i'm being told i have insufficient privileges. I can run the select statement on the base table.

CREATE OR REPLACE VIEW vu_student_name
 AS SELECT student_id, lastname
    FROM hr.students;

Upvotes: 0

Views: 7022

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

From the documentation:

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.

You only have the base table privileges granted through the role. They need to be granted directly to enrol1.

Upvotes: 5

Related Questions