SYSTEM$REFERENCE function: Creating a Reference with Mismatched Object Types Fails (Pending)¶
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.
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');
The SYSTEM$REFERENCE function behaves as follows:
- Before the change
If you create a reference using the SYSTEM$REFERENCE function and the
object_typeargument 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_typeargument 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
v1with the following statement:
SELECT SYSTEM$REFERENCE('TABLE', 'v1', 'SESSION', 'SELECT');
The statement results in the following error:
505028 (42601): Object type VIEW does not match the specified type TABLE for reference creation