CODEWITHSUNDEEP
oracle-databasecollectionssoda
MattFir
MattFir

Reputation: 11

Oracle SODA: collection created in a schema to be visible to another schema

I have a schema (owner_schema) that is able to create collections using SODA api PL/SQL. Now I want to have another schema (user_schema) to be able to access the collection created by the owner_schema and be able to insert/read/delete documents: The user_schema must not be the owner of the collections, and it must not have any CREATE privileges (CREATE_TABLE, etc.).

The problem is that even if user_schema has SODA_APP role, he cannot see collections created by owner_schema. I have already tried to grant select over the backing table to user_schema, but it doesn't work (user_schema calls openCollection so I guess the grant must be over the collection, not the table). I see that querying the JSON$USER_COLLECTION_METADATA view the owner_schema see the collections, and user_schema has this view empty. Tried also to give grants over the COLLECTION_METADATA table to user_schema but no luck. I read about mapped collection but it suppose user_schema to run create_collection proc which needs CREATE_TABLE priv.

Is it possible for a schema to operate over collections created by another schema without owning it and without having any privileges like CREATE_TABLE?

Upvotes: 1

Views: 127

Answers (1)

u971668
u971668

Reputation: 1

Maybe create a DBMS_SODA wrapper package in the owner schema (run as definer)... e.g. OWNER_SODA ... with the DBMS_SODA procedures/functions you want. Also grant the SODA_APP role privileges directly to OWNER (PL/SQL packages not using definer's roles).

Upvotes: 0

Related Questions