16.10 - Setting Up Trusted User Applications and Proxy Users (Recommended) - Teradata Database

Teradata Database Security Administration

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Administration
Security
Publication ID
B035-1100-161K
Language
English (United States)

In addition to setting up a logon user for each application, you can optionally set up the application as a trusted user and then define proxy users, so that application end users log on to the database with individual identities and role privileges.

  1. Create a permanent database user for the application, for example, App1User.
  2. Create a profile for the application logon user.
    1. Use the PASSWORD option in the profile to set the PasswordExpire parameter to 0 for the application, to prevent the application password from expiring, which could cause a disruption in service for application end users.
    2. If you want row level security constraints to apply generally to all application end users, you can assign RLS constraints to the application user profile. See Assigning Security Constraints in a CREATE PROFILE Statement.
  3. Create roles for defining database privilege variations among application (proxy) users.
  4. For proxy users that are also permanent database users, you can optionally modify the permanent user profile to assign row level security constraints, if used. See Assigning Security Constraints in a CREATE PROFILE Statement.
  5. Grant the CTCONTROL privilege to the user who sets up trusted user applications, for example:
    GRANT CTCONTROL ON  trusted_user_name  TO  user_name  ;
    The administrator that issues the GRANT CONNECT THROUGH privilege TO a permanent database user must have the DROP USER privilege on the permanent user.
  6. Use the GRANT CONNECT THROUGH statement to define a trusted user and associate the proxy users and roles, for example:
    • For users already defined in the database (permanent users):
      GRANT CONNECT THROUGH  trusted_user_name 
          TO PERMANENT  perm_user_name  [,  perm_user_name]
          WITH  ROLE  role_name   [,role_name] | WITHOUT ROLE;
    • For application users not known to the database:
      GRANT CONNECT THROUGH  trusted_user_name 
          TO  app_user_name  [,  app_user_name]
          WITH ROLE  role_name  [,  role_name];
    • To restrict use of SET QUERY_BAND statements through a trusted user application to those statements included in a trusted request:
      GRANT CONNECT THROUGH trusted_user_name [WITH TRUST_ONLY] ;

    where:

    Syntax Element Description
    trusted_user_name The permanent username that the application uses to log on to Teradata Database and establish a session pool.

    This user must exist in the database before it is referenced in a GRANT CONNECT THROUGH statement.

    perm_user_name The name of a permanent database user being defined as a proxy user.

    You must precede the perm_user_name with a TO PERMANENT clause to identify the user as a permanent user. A user must exist in the database before you can reference it in a GRANT CONNECT THROUGH statement.

    You can specify up to 25 perm users in each GRANT CONNECT THROUGH statement. There is no limit to the number of perm users to which you can grant proxy logon privileges for a trusted_user_name.

    app_user_name The name of an application end user being defined as a proxy user.

    You must precede the app_user_name with a TO clause to identify the user as not being a permanent database user. Users associated with app_user_names do not exist in the database, but the names must follow Teradata Database object naming conventions.

    You can specify up to 25 app users in each GRANT CONNECT THROUGH statement. There is no limit to the number of app users to which you can grant proxy logon privileges for a trusted_user_name.

    WITH ROLE role_name Lists the role names available to the proxy user(s) contained in the GRANT CONNECT THROUGH statement.

    You must specify at least one role name in a GRANT CONNECT THROUGH statement that assigns proxy user status to application end users. The role_name must identify a role that exists in the database. ALL, NONE, and NULL are not valid role names.

    You can specify up to 15 role names in each GRANT CONNECT THROUGH statement, and you can specify up to 15 role names for each proxy user/trusted user name pair.

    If the CONNECT THROUGH privilege for a particular trusted user already exists for the perm_user_name or app_user_name, the database adds any new roles that you specify to the existing roles.

    If you exceed the limit of 15 roles for a user, the statement aborts.

    Use the REVOKE CONNECT THROUGH statement to remove a role from the proxy user for a trusted user.

    For information on specifying roles in a proxy connect and the effects of the specifications on proxy user privileges, see Using the SET QUERY BAND Statement to Enable Session Proxy Roles.

    WITHOUT ROLE If the proxy_user_name is a permanent database user, the proxy user inherits the access privileges, including roles, of the permanent user.
    Do not use WITHOUT ROLE for GRANT CONNECT THROUGH statements that specify an app_user_name.
    WITH TRUST_ONLY Instructs the database to honor SET QUERY_BAND statements that set or update a proxy user only if they are part of a trusted request. This prevents end users from changing their database privileges.

    If you specify the WITH TRUST_ONLY option, you cannot create proxy users in the same request.

    To use this feature, you must set up the query band to tag requests as trusted or not trusted.
  7. You must set up the application to send a SET QUERY_BAND statement to Teradata Database to initiate each proxy user session, identify the proxy user, and optionally specify the operant proxy user role for the session or transaction.

    See Using the SET QUERY BAND Statement to Enable Session Proxy Roles, and the Teradata Orange Book Using Query Banding in the Teradata Database.