SYSTEM$REFERENCE function: Creating a Reference with Mismatched Object Types Fails¶

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');

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