2

I already executed the following commands:

grant create view to scott;
conn hr/hr;
grant select on employees to scott;

but I conn to scott:

create or replace view company_phone_book as
select employee_id emp_id, last_name||','||first_name name, phone_number, email
from hr.employees;

gives me the error:

ORA-01031: insufficient privileges. enter image description here

  • 4
    I think you need to grant the select privilege with "grant option" to the table in order to be able to use it in a view. – a_horse_with_no_name Feb 24 '16 at 9:08
  • 1
    which user is executing that create view statement? and in your screenshot, at first you have created view as select * from emp, not employees. – JSapkota Feb 24 '16 at 9:57
  • @a_horse_with_no_name That was very helpful, can you add your solution as an answer so I can upvote it? – Air Jul 29 '16 at 18:12
  • @ JSapkota I am in order to prove the ordinary create a view to normal. – zhanzezhu Aug 1 '16 at 8:07
2

SCOTT will have the ability to expose the new view to other users, thus indirectly granting data access on HR to other users. SCOTT therefore needs WITH GRANT OPTION on the grant.

GRANT SELECT ON employees TO scott WITH GRANT OPTION;
0

I think some grants are missing.

Can you try creating the VIEW after giving the following GRANT:

GRANT CREATE VIEW TO hr;

And also re-structure your CREATE Statement as follows:

create or replace view hr.company_phone_book as
select employee_id emp_id, last_name||','||first_name name, phone_number, email
from hr.employees;

Hope this works !!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.