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


  • 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.

  • 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 Reply

Your email address will not be published.