17.10 - Session Constraint Values for Trusted User Applications and Proxy Users - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

All users logging on through a middle-tier trusted user application inherit the security constraint assignments and access privileges of the application logon user (trusted user), and can use the SET SESSION CONSTRAINT statement to switch from the default constraint values to other values available to the trusted user.

See Using SET SESSION to Change the Session Security Constraint Value.

Initial SET QUERY_BAND Processing

If the trusted user application submits an initial SET QUERY_BAND statement (a standard operation for trusted user applications) the system resets the session constraint values to an empty set, and takes the following actions:

  • If the statement does not set a proxy user, the session uses the default constraint value for the trusted user according to the normal permanent user constraint hierarchy. See Session Constraint Values for Permanent Database Users.
  • If the statement sets a proxy user, and the user is also a permanent database user, the system uses the default constraint value for the permanent user, according to the normal permanent user constraint hierarchy, including profile. See Session Constraint Values for Permanent Database Users.
  • If the statement sets a proxy user, and the user is not also a permanent database user, the system is unable to set a session constraint value and also will not accept a SET SESSION CONSTRAINT statement. As a result, proxy users that are not also permanent database users cannot access row level security tables.

    Subsequent SET QUERY_BAND Processing without Update Option.

If the trusted user application submits a subsequent SET QUERY_BAND statement without an update option, the statement causes the constraint values currently active for the session to reset to an empty set. The new constraint values for the session are determined according to the rules shown in Initial SET QUERY_BAND Processing.

Subsequent SET QUERY_BAND Processing with Update Option

If the trusted user application submits a subsequent SET QUERY_BAND statement with an update option, the resulting constraint values for the update depend on whether the session currently has a proxy user assigned:

  • If the session did not previously define a proxy user and a SET QUERY_BAND update adds a proxy user that is a permanent database user, the system determines session constraint values using the normal constraint hierarchy for the permanent user. The user can change the value to other permanent user constraint values using SET SESSION CONSTRAINT.
  • If the session did not previously define a proxy user and the SET QUERY_BAND update adds a proxy user that is not a permanent database user, there are no constraint values allocated to the session. The user cannot access a row level security table.
  • If the session previously defined a proxy user and the SET QUERY_BAND update defines a new proxy user, the constraint values for the session depend on whether the new proxy user is also a permanent user:
    • If the user is a permanent user, the system determines session constraint values using the normal constraint hierarchy for the permanent user. The user can use a SET SESSION CONSTRAINT statement to change constraint values active for the session to other available user or profile values.
    • If the user is not also a permanent user, no constraint values are allocated to the session. A SET SESSION CONSTRAINT statement to change constraint values active for the session will be rejected with an error.

      END TRANSACTION Processing

If the user executes a statement terminating a transaction during a trusted session, the constraint values for the session depend on whether the user is a proxy user.

  • If the proxy user is assigned to the session only for the current transaction, the constraint values assigned to the session revert to those set at the initial connect of the session, that is, those for the application logon user.
  • If the proxy user is assigned to the session for all transactions then the constraint values are not changed, and are the values assigned to the proxy user.