Merge over Different Schemas 1

[jaylrob]

ProcedureA and TableA are located in SchemaA. ProcedureA performs a merge between View B of SchemaB and TableA. ProcedureA is giving a ORA-00942(table or view does not exist) for the following line ... USING (SELECT * FROM B.VIEWB) D

Code excerpt
---------------------
MERGE INTO A.TableA C
USING (SELECT * FROM B.ViewB) D
ON (C.dealerid = D.di_dealer_id)
WHEN MATCHED THEN UPDATE SET

C.dealername = D.di_dealer_name,
C.dealerno = D.di_dealer_no,
C.fedid = D.di_fed_id,
C.oldate = D.di_online_dte, ...


Permissions given are as follows

Schema A
----------------
ProcedureA - execute on User A
TableA- Insert, Delete, Update, Select on User A

Schema B
----------------
ViewB - Select on User A, Select on UserARole


I believe appropriate permissions have been given. Any suggestions??

 

[SantaMufasa]

Jay,

If I was troubleshooting this, I would start out by doing a:

DESCRIBE <schema.table_name>

...on each of the tables in your query. Whichever one(s) won't "Describe" is/are the one(s) requiring your focus.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jaylrob]

Thanks. Interesting results. In SQLPlus, if logging in as UserA, all DESCRIBE returns results for all tables. If logging in under the owner of the procedure, I am not able to DESCRIBE the view (which is in the other schema).

 

[SantaMufasa]

Correct, when you are embedding in a procedure, access to objects, then the permissions on the objects go by user, not by procedure premissions.

If you do GRANTs on the objects involved to the end users (or a ROLE to which the end users are members), then you should be okay.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]