Accessing Pre-26ai Collection Owned By Other Users (Database Schemas)

Any collection (a 26ai JSON collection table or view, or a pre-26ai collection backed by a conventional table or view) can be accessed in SODA when connected as the user that owns it.

In addition, a pre-26ai collection can be a mapped collection, which is a collection defined (mapped) on top of an existing table belonging to any schema. This effectively allows accessing collections owned by another user (database schema). You can control which operations on a collection are permitted for other users (database schemas) by granting them different privileges on the backing table. For backward compatibility, mapped collections continue to be supported on 26ai.

Note: Mapped collections are not supported for JSON collection tables and views, which cannot be accessed cross-schema.

The following example illustrates this. It uses SODA for PL/SQL to create both the original and the mapped collection.

In this example, user john creates collection john_coll in database schema john, then grants user janet access privileges to its backing table.

User janet then maps a new collection, janet_coll (in schema janet), on top of the backing table of john_coll. (The original and mapped collections do not need to have different names — both could share the same name.)

Step 1

When connected to the database as user john, run PL/SQL code to create collection john_coll backed by table john_coll. (The backing table name is derived from the collection name — see Default Naming of a Collection Table.)

DECLARE
  col SODA_COLLECTION_T;
BEGIN
  col := DBMS_SODA.create_collection('john_coll');
END;

The above assumes that compatible parameter is set to below 23. Otherwise, the default collection is based on JSON collection table, which does not support collection mapping. If compatible is set to 23 or higher, you can explicitly request an pre 26ai collection based on collection table using metadata. For example, the will created 19c default collection of any release, include 26ai or higher:

DECLARE
  collection SODA_COLLECTION_T;
  metadata VARCHAR2(4000) := '{ "keyColumn":{"name":"ID","assignmentMethod":"UUID", "sqlType" : "VARCHAR2"},
                                "contentColumn" : {"name" : "DATA", "sqlType" : "BLOB"},
                                "versionColumn":{"name" : "VERSION", "method" : "UUID"},
                                "lastModifiedColumn":{"name":"LAST_MODIFIED"},
                                "creationTimeColumn":{"name":"CREATED_ON"}}';
BEGIN
  collection := DBMS_SODA.create_collection('john_coll', metadata);
DBMS_OUTPUT.put_line('Collection specification: ' || json_query(collection.get_metadata, '$' PRETTY));
END;
/

Step 2

When connected to the database as user john, grant user janet access privileges to collection john_coll and its backing table of the same name, john_coll. (You can choose which privileges to grant. For example, to only allow only read access to john_coll from schema janet you would grant only privilege SELECT.)

GRANT SELECT, INSERT, UPDATE, DELETE ON john.john_coll TO janet;

Step3

When connected to the database as user (schema) janet, Create a new collection janet_coll in schema janet that’s mapped to the original collection (john_coll in schema john).

The second argument to method create_collection() is the collection metadata. Among the things it specifies here are the schema and backing-table names of the collection to be mapped to. The last argument, CREATE_MODE_MAP, specifies that the new collection is to be mapped on top of the table that backs the original collection.

DECLARE
  col SODA_COLLECTION_T;
BEGIN
  col := DBMS_SODA.create_collection(
           'janet_coll',
           '<john_coll_metadata>',
           DBMS_SODA.CREATE_MODE_MAP);
END;

Placeholder <john_coll_metadata> here stands for the metadata of collection john_coll. To obtain it, execute this SQL query when connected as user john:

SELECT JSON_DESCRIPTOR FROM USER_SODA_COLLECTIONS WHERE URI_NAME = 'john_coll'.

The same data is written to and read from either collection, that is, when connected as user janet using collection janet_coll or when connected as user john when using collection john_coll. Read and write operations are performed against the same table.