Privileges on expression-based indexes

Special consideration must be given to privileges when you use expression-based indexes.

The authorization that is required to create an index with an expression-based key is the same authorization that is required for a regular index. For details, refer to the Authorization section of the CREATE INDEX topic.

When you create an expression-based index, two more database objects are system-generated and associated with the index. The first is a statistical view, and the second is a package. These additional objects are not system-generated when you create a regular index. A restricted set of privileges is granted on these additional objects.

Statistical view privileges

Normally, the authorization ID must hold either SELECT or DATAACCESS privilege on the table to create a statistical view. The same privilege is required to ALTER the same table to enable query optimization for the view.

For a system-generated statistical view that is associated with an index, these privileges are not required. The statistical view is automatically created if the authorization ID has the required authority to create an index on the table. However, the set of privileges that is granted on the statistical view that is associated with an index differ from a set of privileges on a normal statistical view. Namely, no privileges are granted to any authorization ID on the statistical view, including the owner of the index. The owner of the index is also the owner of the statistical view. No one, including authorization IDs with the SECADM or DBADM authority can modify privileges on a statistical view. An attempt to GRANT or REVOKE a privilege on the statistical view results in an error (SQLSTATE 42501).

The ability to issue RUNSTATS on the statistical view or manually update its statistics is governed by the authorities and privileges on the underlying table.

The TRANSFER OWNERSHIP operation on the statistical view is not allowed and results in SQL20344N, reason code 7. However, TRANSFER OWNERSHIP of an index with an expression-based key implicitly transfers the ownership of the associated statistical view.

Package privileges

No extra privileges are required to run any statement or command in the system-generated package. When an index is created with an expression-based key, any user with privileges on the table can use the package. That is, any user with INSERT, UPDATE, DELETE, or SELECT on the table has EXECUTE privilege on that package. This authorization is implicit as part of the statement or command that is run.

The TRANSFER OWNERSHIP operation on the package is not allowed and results in SQL20344N, reason code 5. However, TRANSFER OWNERSHIP of an index with an expression-based key implicitly transfers the ownership of the associated the package.