SQL92_SECURITY

SQL92_SECURITY specifies whether users must have been granted the SELECT privilege on a table in order to specify certain clauses in a DELETE, INSERT, MERGE, or UPDATE statement.

Property	Description
Parameter type	Boolean
Default value	TRUE
Modifiable	No
Modifiable in a PDB	Yes
Range of values	TRUE | FALSE
Basic	No

The SQL standard specifies that security administrators should be able to require that users have the SELECT privilege on a table when executing a DELETE, INSERT, MERGE, or UPDATE statement that references table column values in certain clauses. This requirement is enforced when SQL92_SECURITY is set to TRUE.

Values

• TRUE

  • A user must have the SELECT privilege on the target table in order to reference its columns in:

    • The WHERE clause of a DELETE or UPDATE statement

    • The right hand side of an assignment in the SET clause of an UPDATE statement

    • The RETURNING clause of a DELETE, INSERT, or UPDATE statement

  • A user must have the SELECT privilege on the source and target tables of a MERGE statement in order specify the RETURNING clause.

  • A user must have the SELECT privilege on the target table of a MERGE statement in order specify the DELETE clause, or the WHERE clause of the WHEN MATCHED THEN UPDATE clause.

• FALSE

  • A user with the DELETE privilege on the target table of a DELETE statement may reference columns of the target table in the WHERE clause of the DELETE statement.

  • A user with the UPDATE privilege on the target table of an UPDATE statement may reference columns of the target table in the WHERE clause or on the right hand side of any assignment in the SET clause of the UPDATE statement.

  • A user with the READ or SELECT privilege on the target table of a DELETE, INSERT, or UPDATE statement may reference columns of the target table in the RETURNING clause.

  • A user with the READ or SELECT privilege on the source and target tables of a MERGE statement may specify the RETURNING clause.

  • A user with the DELETE privilege on the target table of a MERGE statement may specify the DELETE clause.

  • A user with the UPDATE privilege on the target table of a MERGE statement may specify the WHERE clause of the WHEN MATCHED THEN UPDATE clause.