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

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 row level security 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  ;

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;

     The administrator that issues the GRANT CONNECT THROUGH privilege to a permanent database user must have the DROP USER privilege on the permanent user.
   • 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] ;

   Syntax Element	Description
   trusted_user_name	The permanent username that the application uses to log on to Teradata Vantage 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 Vantage 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 users 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 fails. 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 Vantage 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.