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>

1 comment:

Jack Logan said...

There may be hack to do this:
ON Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production:

1. Login into my dev sid with my login; My login is granted a role that has select privileges on a table. I did not have a direct grant of the table to my login. I can select from the table and see the expected number of rows

2. I create a DB link which links
back to the the same sid:

CREATE DATABASE LINK "UJACKDEVDEV.WORLD"
CONNECT TO DBUJACK
IDENTIFIED BY
USING 'DEV';

3. Test the link referencing a table:
select * from ASCHEMA.ABS_DATA@UJACKDEVDEV.WORLD;

Returns the expected rows.

4. I then create a view
create view jklvialinkabstract as select * from ASCHEMA.ABS_DATA@UJACKDEVDEV.WORLD;

5. I perform a select on the view

select * from jklvialinkabstract;

and see the same expected rows.

=====
I created a view based on a table in another schema without the direct grant.
I'm unsure why it works and how
security would be affected nor if
view beomces invalid (easy enough to do).
Encountered this when I was writing an application to get/put database across different sids.

Jack Logan