When a permanent database user accesses a row protected by a security constraint, the system determines the session constraint values as follows:
- If the user profile has a corresponding security constraint assignment, the session uses the constraint values specified in the profile definition.
- If the user profile does not have a security constraint specification, the system derives the session constraint values from the user definition.
- Whether user constraint values are defined in a profile or user definition, if multiple constraint values are defined, the system determines the session values as follows:
- For hierarchical (non-set) constraints, the system uses the DEFAULT value specified in the profile, or if there is no profile, in the user definition. If no DEFAULT is specified, the system uses first value listed for the constraint.
- For non-hierarchical (set) constraints, the session uses all constraint values in the profile, or if there is no profile, in the user definition. No DEFAULT can be specified.Requesting users can use SET SESSION CONSTRAINT to change the session constraint value to any constraint value specified in the profile or user definition.
- If neither the user profile nor the user definition contains a security constraint assignment, the constraint value for the session is NULL, and the user can access rows controlled by a security constraint only if assigned the necessary OVERRIDE privileges.
- If a user has OVERRIDE privileges on the object and the operation being performed, the system ignores constraints assigned in the profile or user definition. The session derives security constraint values in one of the following ways:
- For simple inserts, the user must supply the constraint values.
- For compound statements, for example, INSERT ... SELECT or MERGE, the system derives constraint values from the security constraint columns in the source table.
- The user can use SET SESSION CONSTRAINT to specify constraint values.