Object Permissions (NEWBIE)

[JRob]

Procedure A and Table A are located in Schema A. Procedure A performs a merge between View B of Schema B and Table A. Procedure A is giving a ORA-00942(table or view does not exist) for the following line ... USING (SELECT * FROM B.VIEWB)

Is this a permissions issue? When opening the schema containing the view, I notice that the view does not state that it is referenced by Procedure A. Should that be the case?

Any direction? Thanks.

 

[Marco Kalter]

You must grant the select privilege on view B to user A:

grant select on VIEWB to A;

 

[JRob]

I have verified that VIEWB has given select privilege to A.

VIEWB - gives select to A

TABLEA - has select, update, insert, delete to A

I am able to select independently from view and/or table, but merge still returns 00942 error

 

[Theod]

Please show the merging select statement.
Maybee there is syntax related bug.

We are using also Views over severals schemas and it works (normaly).

 

[Marco Kalter]

Also make sure that the select privilege is granted directly to user A, and not indirectly via a role. This is an Oracle PL/SQL compiler restriction.

 

[JRob]

SELECT privilege has now been granted directly to USER A. SELECT was previously granted to the role, which I left as is.

Merging select statement...

MERGE INTO plados.plados_dealer_info T
USING (SELECT * FROM olnd.olnd_plados) A
ON (T.dealerid = A.di_dealer_id)
WHEN MATCHED THEN UPDATE SET

 

[JRob]

Should the select privilege given to the role be removed?

 

[Theod]

In the Oracle

 

[JRob]

Thanks. I had only posted the first few lines. My sql contained the complete merge as referenced in your reply.

MERGE INTO plados.plados_dealer_info T
USING (SELECT * FROM olnd.olnd_plados) A
ON (T.dealerid = A.di_dealer_id)
WHEN MATCHED THEN UPDATE SET

T.dealername = A.di_dealer_name,
T.dealerno = A.di_dealer_no,
T.fedid = A.di_fed_id,
T.oldate = A.di_online_dte,
...