GRANT CONNECT THROUGH Syntax - Advanced SQL Engine - Teradata Database

GRANT CONNECT THROUGH trusted_user_name [ WITH TRUST ONLY ]
  TO {
       application_user_name [,...]
         {
           WITH ROLE role_name [...] [ WITH PROFILE profile_name ] |
           WITH PROFILE profile_name
         } |

        PERMANENT permanent_user_name [,...]
          { WITH ROLE profile_name [,...] | WITHOUT ROLE }
  } [;]

Syntax Elements

trusted_user_name

the name of the user receiving the CONNECT THROUGH privilege.

trusted_user_name must be the name of a permanent user who is already defined in Vantage, but cannot be user DBC.

This grants the specified trusted user the privilege to assert the identity of the specified proxy user names.

Note that trusted_user_name should not identify an end user. Its intent is to identify a middle tier application such as a CRM application or Teradata Viewpoint.

If you specify trusted_user_name WITH TRUST_ONLY, then you cannot also specify application_user_name WITH ROLE or permanent_user_name WITH ROLE or WITHOUT ROLE in the same request.

WITH TRUST_ONLY

to require for trusted_user_name that all requests to set or remove a Proxy User or Proxy Role must be trusted requests.

This option provides a method of preventing an end user from inserting SET QUERY_BAND requests into the SQL stream and changing the Proxy User or Proxy Role setting without the hosting middle tier application being aware of it.

application_user_name

the name of an application user to whom the proxy logon privilege is to be granted through trusted_user_name.

Specifying application_user_name is not valid if you also specify the WITH TRUST_ONLY option in the same request.

Application user names can be specified any time that creating a permanent user for every end user of a middle tier application would not be manageable.

Application user names are not defined in Vantage, but they must follow Teradata object naming conventions. An application user name can be anything that represents the client connecting to the middle tier, such as a client name or an ATM identifier.

application_user_name must be unique for the specified trusted_user_name.

For more information about application_user_name, see Application Proxy Users.

You can specify a maximum of 25 application user names per GRANT CONNECT THROUGH request, but there is no limit to the number of application user names that can be granted logon privileges to a trusted user.

The system adds the names you specify to the CONNECT THROUGH privileges for trusted_user_name.

Note that you must specify at least one role in the WITH ROLE clause for each application proxy user that you specify.

permanent_user_name

the name of a permanent user to whom the proxy logon privilege is to be granted through trusted_user_name.

Specifying permanent_user_name is not valid if you specify the WITH TRUST_ONLY option in the same request.

permanent_user_name must be the name of a permanent Teradata user, but cannot be user DBC.

permanent_user_name must be unique for the specified trusted_user_name.

For more information about permanent_user_name, see Permanent Proxy Users.

You can specify a maximum of 25 names per GRANT CONNECT THROUGH request, but there is no limit to the number of permanent user names that can be granted logon privileges to a trusted user.

The system adds the names you specify to the CONNECT THROUGH privileges for trusted_user_name.

WITH ROLE role_name

a set of role names to be assigned to the proxy connection.

Specifying WITH ROLE role_name is not valid if you also specify the WITH TRUST_ONLY option in the same request. This option is only valid when specified for application users or permanent users.

The string that you specify for role_name must be the name of an internal role that is already defined in Vantage. An internal role is a role that is managed by Vantage, as opposed to an external role, which is directory-managed. See the information about CREATE ROLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

In this context, ALL, NONE, and NULL are not valid role names.

You must specify at least one role name for application proxy users, while you can specify WITHOUT ROLE for permanent proxy users.

You can specify a maximum of 15 role names per proxy user. Unlike the maxima for permanent and application user names, this is an absolute maximum per proxy user, not a per request maximum.

If a CONNECT THROUGH privilege already exists for the specified trusted_user:permanent_user or trusted_user:application_user name pair, the system adds the roles you specify to the existing CONNECT THROUGH privilege.

Note that changes to a CONNECT THROUGH privilege are effective immediately, so the next request submitted in a proxy connection following a change to a CONNECT THROUGH privilege picks up the new privilege definition.

Note that when roles are dropped, they are removed from the CONNECT THROUGH privilege definition, so a rule can be left with no roles defined for it. Proxy users who have privileges that were set with the WITH ROLE clause, but later have those roles dropped, will have PUBLIC privileges only.

If the addition of new roles causes the number of roles assigned to the privilege to exceed 15, the request aborts and the system returns an error to the requestor. If this happens, you can submit an appropriate REVOKE CONNECT request to remove one or more roles from the privilege.

See REVOKE CONNECT THROUGH for information about how to use this statement.

Vantage activates all of the role names specified in a WITH ROLE clause, either by default or when the proxy connection sets PROXYROLE=ALL.

If a proxy connection specifies PROXYROLE=role_name, the specified role name must be one of the roles granted to the proxy user with the WITH ROLE clause. Vantage then sets the role for the proxy connection to this role.

You cannot specify either NONE or NULL as a role_name for this option.

WITHOUT ROLE

to assign the privileges and roles of the specified permanent user to the CONNECT THROUGH privilege.

WITHOUT ROLE is not valid if you specify the WITH TRUST_ONLY option in the same request, nor is it valid for application users.

If a CONNECT THROUGH privilege already exists for the specified trusted_user:permanent_user with roles, the roles are replaced with the WITHOUT ROLE option.

Note that changes to a CONNECT THROUGH privilege are effective immediately, so the next request submitted in a proxy connection following a change to a CONNECT THROUGH privilege picks up the new privilege definition.

WITH PROFILE profile_name

The trusted session is set to the attributes that are defined in the specified profile_name. The set of profile attributes are as follows:

• Default Account
• Default Database
• Spool Space
• Temp Space
• Security Constraints
• Query Band

If an application proxy user is assigned a profile, a unique proxy user id is created for the proxy user, and in a proxy connection, the limits for spool space and temporary space are the limits defined in the proxy user's profile.

If a rule exists for the application_user_name, you can omit the WITH ROLE clause to assign a profile to the rule. If you add a profile to an existing application proxy user without a profile, the profile is not used in any active connections. Modifying the rule to assign a different profile to the proxy user has the same impact as modifying the current profile.

The rules that determine when changes to parameters in a MODIFY PROFILE request take effect are the same for a trusted session as they are for a regular session. The effect of dropping the profile for a proxy user is the same as the effect of dropping the profile for a user.