Reputation: 153
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: 7015
Reputation: 191560
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