Sunday, May 11, 2014

ORA-01031 Creating a View on a Table Accessed via a Role

This example shows that you cannot create a view on a table which you access via a role. It was tested on Oracle 11. First I created a role:

SQL> create role andrews_role
  2  /

Role created. 

SQL>

Then I created a user called ANDREW1, who will create a table:

SQL> create user andrew1
  2  identified by reid1
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.

SQL> grant create session, create table
  2  to andrew1
  3  /

Grant succeeded. 

SQL> 

Next I created a user called ANDREW2, who will try to read that table directly and via a view:

SQL> create user andrew2
  2  identified by reid2
  3  /

User created.

SQL> grant create session, create view,
  2  andrews_role to andrew2
  3  /

Grant succeeded.

SQL>

I connected as ANDREW1, created a table and granted SELECT access on it to ANDREWS_ROLE:

SQL> connect andrew1/reid1
Connected.
SQL> create table numbers
  2  as select '1234' col1 from dual
  3  /
 
Table created.
 
SQL> grant select on numbers to andrews_role
  2  /
 
Grant succeeded.

SQL>

At this point, ANDREW2 could see the NUMBERS table via ANDREWS_ROLE: 

SQL> conn andrew2/reid2
Connected.

SQL> select * from andrew1.numbers
  2  /
 
COL1
----
1234
 
SQL>

... but he could not create a view to look at ANDREW1.NUMBERS:

SQL> create view numbers
  2  as select * from andrew1.numbers
  3  /
as select * from andrew1.numbers
                         *
ERROR at line 2:
ORA-01031: insufficient privileges
SQL> 

ANDREW1 then granted the SELECT privilege directly to ANDREW2:

SQL> conn andrew1/reid1
Connected.
SQL> grant select on numbers to andrew2
  2  /
 
Grant succeeded.

SQL>

This allowed ANDREW2 to create a view to look at ANDREW1.NUMBERS:

SQL> conn andrew2/reid2
Connected.
SQL> create view numbers
  2  as select * from andrew1.numbers
  3  /

View created.

SQL> select * from numbers
  2  /

COL1
----
1234

SQL>

No comments: