permission issue due to one role

Most permissions issues are due to a missing role or privilege.

But in the following test case you need to revoke the right to get more privileges.

create table tt(x number);
create view v as select * from tt;
create role rw;
grant all on v to rw;

I’ve created a read-write role on a view. The owner of the role is the DBA, but the owner of the view is the application. Next release, the role may prevent an application upgrade

SQL> create or replace view v as select * from dual;
ORA-01720: grant option does not exist for 'SYS.DUAL'

Ok, if I drop the role, it works

SQL> drop role r;
Role dropped.
SQL> create or replace view v as select * from dual;
View created.

It is not always a good thing to grant privileges on a view, when you are not the owner of that view

Categorized as security

By Laurent Schneider

Oracle Certified Master


  1. You should see the same effect if the owner makes the grant and not SYSTEM. You’ll also see if it granting directly to a user and not to a role. The GRANT OPTION requirement for underlying tables is in force for any grant done on a view.

  2. correct. What I pointed is that a grant may work on one view, and if the view is to be changed, with CREATE OR REPLACE VIEW, the CREATE OR REPLACE VIEW failed. Not the grant

Leave a comment

Your email address will not be published.