ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role

(Doc ID 271587.1)

Last updated on OCTOBER 30, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 12.1.0.2 [Release 8.1.7 to 12.1]
Information in this document applies to any platform.
Checked for relevance on 25-Apr-2013


Symptoms

While trying to create a view on a table for which the select privilege is granted via a role the following error occurs:

This fails with SYSTEM user creating view on dba_free_space as well. (Explicitly Grant SELECT on view to SYSTEM to make it Work.)

connect / as sysdba 
create user A identified by A; 
create user B identified by B; 
grant connect,resource to A;
grant connect,resource,create any view to B; 

create role R; 
grant R to B;

connect A/A 
create table A (A number); 
grant select on A.A to R; 

connect B/B 
select * from A.A;
no rows selected   =========> Selecting from the table is possible with the privilege acquired via a role

create view V as select * from A.A;
create view V as select * from A.A
*
ERROR at line 1: ==========> Creating a view does not work with the privilege acquired via a role !!
ORA-1031: insufficient privileges

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms