Oracle : Cannot query a view of another schema that also has linked tables to another schema

Question

I have a view created under a user "dbaUserA". View query uses a table from another user "dbaUserB". Both users are dba.

view name: v_valid_trx with query of

 Select trx_no,trx_date,order_item 
 from trx_table a left outer join  dbaUserB.cancelled_trx b on a.trx_no=b.trx_no
 where b.trx_no is null;

I have created a less privilege user "userC" but with select privilege on

dbaUserA.trx_table
dbaUserB.cancelled_trx.
dbaUserA.v_valid_trx

I can select directly trx_table and cancelled_trx from those different schemas however i cannot select on the view v_valid_trx.

Oracle prompts

ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login. *Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label. Error at Line: 1 Column: 21

Been wrestling with these for hours now. I can't seem to understand the cause of the error when i can freely select the base tables.

please help :)

I'd guess dbaUserB needs to grant select on cancelled_trx to dbausera with grant option, though that's a bit of a guess without a complete test case. dbaUserB gave dbaUserA permission to query it, not to share it with anyone else. — William Robertson
Also note your command-line access to the base tables may be via a role (such as DBA) which won't be any use for base tables within views. — William Robertson
when i did the grants for the view i was on console as sysdba all the time. I re-granted the select on the base table to dbauserA now with grant option. Thanks a lot William for the correct guess. :) Did not know that oracle privileges works that way. — yonas

William Robertson William Robertson · Accepted Answer · 2016-11-24T11:31:28

When user A grants permissions on a table to user B, that doesn't give B permission to expose that data to anyone else, even via a view. The extended grant syntax WITH GRANT OPTION must be used to allow this.

For example, say USER_A creates a table and grants SELECT on it to USER_B. (To make it clear who is running each command I've made their SQL> prompts reflect their username.)

USER_A> create table cancelled_trx(dummy varchar2(1));

USER_A> grant select on cancelled_trx to USER_B;

That doesn't give USER_B permission to pass the grant on to USER_C:

USER_B> grant select on USER_A.cancelled_trx to USER_C;
                               *
ERROR at line 1:
ORA-01031: insufficient privileges

The error message is more specific for views:

USER_B> create or replace view test_view as select * from USER_A.cancelled_trx;

View created.

USER_B> grant select on test_view to USER_C
                        *
ERROR at line 1:
ORA-01720: grant option does not exist for 'USER_A.CANCELLED_TRX'

To allow this, the owner (USER_A) would have to

USER_A> grant select on cancelled_trx to USER_B with grant option;

The other point raised by your question is scope of roles. Grants made to roles don't apply within views or definer-rights procedures. Therefore if USER_A granted select on cancelled_trx to ROLE_X, and USER_B had ROLE_X, USER_B could query cancelled_trx but not use it in a view or a stored procedure.

Oracle : Cannot query a view of another schema that also has linked tables to another schema

2 Answers