SYSTEM$REFERENCE function: Creating a Reference with Mismatched Object Types Fails (Pending)

Attention

This behavior change is in the 2023_08 bundle.

For the current status of the bundle, refer to Bundle History.

You can create a reference to an object using the SYSTEM$REFERENCE function. A reference authorizes access on an object to a stored procedure, class instance, or application that cannot otherwise access the object. The reference is passed as an identifier to an owner’s rights stored procedure, class instance, or application with specific and limited privileges on an object.

The object_type argument of the SYSTEM$REFERENCE function should match the type of the object the reference identifies. In the following example, t1 is a table and matches the TABLE object type:

SELECT SYSTEM$REFERENCE('TABLE', 't1', 'SESSION', 'SELECT');
Copy

The SYSTEM$REFERENCE function behaves as follows:

Before the change

If you create a reference using the SYSTEM$REFERENCE function and the object_type argument is TABLE, and the object name resolves to any table-like object type (that is to say, TABLE, VIEW, EXTERNAL TABLE, or MATERIALIZED VIEW), the function succeeds.

After the change

If you try to create a reference using the SYSTEM$REFERENCE function and the object_type argument is TABLE, but the object name resolves to a table-like object type other than TABLE (that is to say, VIEW, EXTERNAL TABLE, or MATERIALIZED VIEW), the function fails.

For example, if you use the TABLE object type for view v1 with the following statement:

SELECT SYSTEM$REFERENCE('TABLE', 'v1', 'SESSION', 'SELECT');
Copy

The statement results in the following error:

505028 (42601): Object type VIEW does not match the specified type TABLE for reference creation

Ref: 1315