16.20 - GRANT CONNECT THROUGH, Trusted Sessions, and User Types - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Control Language

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1149-162K

Unless the WITH TRUST_ONLY option is specified, do not use trusted sessions with applications that permit end users to submit or modify SQL requests sent to Teradata Database.

The GRANT CONNECT THROUGH statement is a special version of the SQL form of the GRANT statement. It allows you to grant the CONNECT THROUGH privilege to the specified permanent user or application user through the specified trusted user.

These users are defined in the following table.

Term Definition
Application user The name of an application user to which the GRANT CONNECT proxy logon privilege is to be granted.

Application user names are not defined in Teradata Database, but they must follow Teradata object naming conventions.

You can specify up to 25 names in a single grant request. The specified names are then added to the grant privileges for the specified trusted user.

There is no limit to the number of application user names that can be granted logon privileges to a single trusted user.

Permanent user A user who is defined to Teradata Database.

In a GRANT CONNECT THROUGH request, this is the name of a user to whom the proxy logon privilege is to be granted.

There is no limit to the number of permanent users who can be granted logon privileges to a trusted user.

Trusted user A permanent user, previously defined to Teradata Database, who receives the CONNECT THROUGH privilege by means of a GRANT CONNECT THROUGH request.

This grants the trusted user the ability to assert the identity of the proxy user specified in the GRANT CONNECT THROUGH request.

Application users and permanent users are collectively referred to as proxy users.

A proxy user is any user who connects to Teradata Database using the session of a trusted user.

A proxy connection is a Teradata Database session in which the privileges and profile attributes that are used are those of a proxy user.

Performance management APIs such as MonitorSession and AbortSession identify sessions by their trusted user name.

For enforcement of Teradata Active System Management rules, for a permanent proxy user, rule qualification based on user name, account, and profile is based on the proxy user’s name, account, and profile.

For an application proxy user, rule qualification by user name is based on the trusted user's name. If the application proxy user has a profile, qualification by profile is based on the proxy user’s profile and qualification by account name on the profile account name. If the application proxy user does not have a profile, qualification based on account and profile is based on the trusted user account and profile.

Proxy User Type Rights and Session Attributes
permanent user
  • Automatic rights for created objects are granted to the permanent proxy user id.
  • Permanent space charged to the logon user is charged to the permanent proxy user id.
  • The permanent user’s profile attributes are automatically assigned to the trusted session. The profile attributes are obtained from the profile assigned to the user or from the user directly. These profile attributes are as follows:
    • Default Account
    • Default Database
    • Spool Space
    • Temp Space
    • Security Constraints
    • Query Band
application user
  • No automatic rights are granted for created objects. The effect is the same as if the application proxy user was an unmapped external user. An application proxy user can create table objects within existing databases, based on role privileges, but there is no granting of automatic rights to the application proxy user. The owner of the database can drop the objects created by application users.
  • The logged on application proxy user does not have permanent space. Any action that charges permanent space to the “logon user” fails.
  • If the application proxy user has a profile assigned, the session attributes are set to those of the profile. Attributes that are not specified in the profile are set to the values of the trusted user. The following profile attributes are automatically assigned to the trusted session:
    • Default Account
    • Default Database
    • Spool Space
    • Temp Space
    • Security Constraints
    • Query Band

    When the application proxy user has a profile, temp and spool usage is accumulated for the proxy user and the limits are based on the proxy user’s profile.

    Application proxy users are unique to a trusted user even if they have the same name. Temp and spool usage accumulation is separate for each of these proxy users.

    If you do not assign a profile to the application proxy user, then the session attributes remain those of the trusted user.

The following built-in functions return information about the proxy connection-related session values:
  • USER returns the name of the trusted user for the session.
  • CURRENT_USER returns the proxy user name if the user is in a proxy connection; otherwise, CURRENT_USER returns the session user name.
  • ROLE returns the current role name for the trusted user.
  • CURRENT_ROLE returns the proxy user current role if the user is in a proxy connection; otherwise, it returns the trusted user role name.

See Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145 for details.

If logon restrictions have been set, such as restricting logons by IP address, the system enforces them only for the trusted user logon.

Such restrictions are not enforced when a proxy username is asserted for the session.