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
from trx_table a left outer join dbaUserB.cancelled_trx b on a.trx_no=b.trx_no
where b.trx_no is null;
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
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
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.