yonas yonas - 15 days ago 7
SQL Question

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

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 :)

Answer

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.