Query Bands, Trusted Sessions, and Row-Level Security - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
Vantage enforces row-level security for proxy users. The following list outlines this support at a high level.
  • A proxy user who is also a permanent user with assigned constraints can perform DML requests on row-level security-protected tables.
  • Row-level security constraints can either be assigned to a permanent user who is also a proxy user directly or indirectly by means of a profile that is assigned to the permanent user.
  • If a proxy user is an application user, then its object-level security privileges are only defined by the roles associated with a session by a SET QUERY_BAND request.
  • Proxy user access to row-level security tables follows the same rules as those that apply to trusted session users who access tables that are not protected with row-level security constraints.
  • The row-level security constraints that are initially active for a session depend on the constraints that are either directly assigned to the user or that are assigned by means of a profile defined for the user, or both.

    This might be the empty set if the neither the user nor the profile are assigned constraint values.

  • Vantage determines the row-level security constraint values that are initially active for a session based on the following factors.
    • The initial connection of a session.
    • Execution of an initial SET QUERY_BAND request in the session.
    • Execution of a request that terminates an active transaction in the session.
    • Execution of a subsequent SET QUERY_BAND request that specifies the UPDATE option in the session.
    • Execution of a subsequent SET QUERY_BAND request that does not specify the UPDATE option in the session.

Each of these determining factors is described in more detail in the following five bullets.

You can change the active constraint values for a session to any of those directly allocated to the connecting user or to those allocated through the user profile with a SET SESSION CONSTRAINT request.

The following factors determine the row-level security constraint values that are initially active for a session.
  • The initial connection of a session.

    The constraint values are taken from the set that is allocated to the profile for the connecting user and from those directly allocated to the connecting user.

    This might be the empty set if the user and profile have no allocated constraint values. You can submit a SET SESSION CONSTRAINT request to change the active constraint values for the session to any of those directly allocated to the connecting user or to those allocated through the profile for the connecting user.

  • The first SET QUERY_BAND request that is executed in the session.

    Execution of an initial SET QUERY_BAND request can also define the initially active row-level security constraints for the session.

    The request causes the constraint values that are currently active for the session to be the empty set. The new constraint values that are assigned to the session depend on whether there is a proxy user assigned as the query band. If so, the assigned constraints depend on whether the proxy user is a permanent user or an application user.

    IF the SET QUERY_BAND request … THEN …
    does not set a proxy user the constraint values for the session are those of the connecting user.

    You can submit a SET SESSION CONSTRAINT request to change the active constraint values active for the session to any of those directly allocated to the connecting user or to those allocated by means of a profile.

    sets a proxy user and the user is a permanent user the constraint values for the session are those allocated to the profile for the proxy user and from those directly allocated to the proxy user, which might be the empty set.

    You can submit a SET SESSION CONSTRAINT request to change constraint values active for the session to either those directly allocated to the proxy user or to those allocated through the user profile.

    sets a proxy user and the user is an application user no constraint values are allocated to the session.

    You cannot submit a SET SESSION CONSTRAINT request to change the constraint values active for the session. Otherwise, the system aborts the request and returns an error to the requestor.

  • When you execute a request that terminates a transaction, the constraint values for the session depend on whether the session has an assigned proxy user and whether the query band is specified for the session or for only the current transaction.
    IF a proxy user is assigned … THEN the constraint values …
    FOR SESSION are not changed.

    The constraints remain those that are assigned to the proxy user.

    FOR TRANSACTION revert to those set at the initial connection of the session.

    The constraints are those that are assigned to the connecting user.

    The result of executing a subsequent SET QUERY_BAND request on the constraint values that are currently active for a session depends on whether you specify the UPDATE option or not.

    IF you submit the SET QUERY_BAND request … THEN the constraint values currently active for the session …
    with the UPDATE option depend on whether the session currently has a proxy user assigned.

    Refer to the next table for the possible outcomes.

    without the UPDATE option are set to the empty set.

    The new constraint values for the session are the same as those defined by the rules in the preceding table.

  • When you submit a SET QUERY_BAND request and also specify the UPDATE option, the constraint values that are currently active for the session depend on whether the session currently has a proxy user assigned or not.

    The following table describes the possible outcomes when there is a proxy user and a new proxy user is defined.

    FOR this type of user … THERE are …
    application no constraint values allocated to the session if you specify the UPDATE option.

    You cannot submit a SET SESSION CONSTRAINT request to change the constraint values that are active for the session. Otherwise, the system aborts the request and returns an error to the requestor.

    permanent constraint values allocated to the session if you specify the UPDATE option.

    These are taken from the constraints that are allocated to the profile for the new proxy user and from those directly allocated to the proxy user.

    This might be the empty set.

    You can submit a SET SESSION CONSTRAINT request to change the active constraint values for the session to either those directly allocated to the proxy user or to those allocated through the user profile.

  • When you submit a SET QUERY_BAND request without also specifying the UPDATE option, the constraint values that are currently active for the session are reset to the empty set.

    The new constraint values for the session are the same as those defined by the first SET QUERY_BAND request that is executed in the session. These constraint values are itemized for the second bulleted item in this list.