Security Administration - Accessing the Teradata Database Through a Middle-tier

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Security Administration - Accessing the Teradata Database Through a Middle-tier

Middle-tier applications may stand between end-users and Teradata Database; accepting requests from users, constructing queries from those requests, passing the queries to the database, and then returning results to the users. The middle-tier application logs on to the database, is authenticated as a permanent database user, and establishishes a connection pool. The application then authenticates the individual application end-users, some of whom may request access to the database through the connection pool.

By default, all end-users accessing the database through a middle-tier application are authorized database privileges, and are audited in access logs, based on the single permanent database user identity of the application.

For sites that require end-users to be individually identified, authorized, and audited, the middle-tier application can be configured to offer trusted sessions. Application end-users that access the database through a trusted session must be set up as proxy users and assigned one or more database roles, which determine their access rights in the database. When a proxy user requests database access, the application forwards the user identity and applicable role information to the database.

Establishing Trusted Sessions

The following sequence describes the steps involved in establishing Trusted Sessions:

1. Since the application authenticates its users, a SET QUERY BAND statement must be used to send relevant user information to the database, including:

• Username: Used to associate the session with a proxy user to define the user privileges and identify the proxy user in query and access logging.

• Role information: Used to define the proxy user privileges for the session.

• The duration of the query band.

Developers or application programmers must embed code in the middle-tier application program to derive the required information, insert it into a SET QUERY_BAND SQL statement, and then forward the statement to the database.

For detailed information on using SET QUERY BAND in a middle-tier application to facilitate trusted sessions, see SQL Data Definition Language Advanced Topics and the Teradata Orange Book, Using Query Banding, Including Trusted Sessions

2. User DBC grants the CTCONTROL privilege to one or more administrators, allowing them to grant trusted user status to middle-tier applications, and to define proxy users and their database privileges.

3. A system administrator with the CTCONTROL privilege uses the GRANT CONNECT THROUGH statement to grant the right to establish trusted sessions to a particular middle-tier application, for selected users.

4. An application with the right to establish trusted sessions logs on to Teradata Database as a permanent database user. Once authenticated, the application creates a connection pool for servicing end user requests.

5. A proxy user logs on and is authenticated by the application. When the proxy user requests a service involving access to Teradata Database, the application gets a connection from the pool and issues a SET QUERY_BAND to set the PROXYUSER.

6. The database authorizes database privileges based on the role(s) defined for the proxy user. A trusted session persists for the life of the query band. Database access privileges in a trusted session are determined by the proxy user role declared in the query band, or if WITHOUT ROLE is specified, by the privileges granted to the matching database user. During a trusted session, the proxy user identity is recorded in all access and query log entries.

Granting Privileges to Establish and Use Trusted Sessions

The implementation of trusted sessions requires the following database privileges.

CTCONTROL Privilege

The CTCONTROL privilege links an administrator to a middle-tier application that is being set up to conduct trusted sessions. The CTCONTROL privilege is required for any administrator that will later use the GRANT CONNECT THROUGH statement to complete the setup of trusted sessions on the application. The CTCONTROL privilege can be granted only to permanent Teradata Database users. It cannot be granted to roles or to any other type of database objects.

The general form of the GRANT CTCONTROL statement is:

GRANT CTCONTROL ON trusted_user TO security_administrator

where:

• trusted_user is the Teradata Database user name for the middle-tier application being set up for trusted sessions.

• security_administrator is the Teradata Database user name for the administrator being given privilege to administer proxy users and associated roles for the application.

Note: The GRANT CTCONTROL statement can specify only one trusted user, but up to 25

administrator users, per statement.

For details on use of the GRANT CTCONTROL statement, see SQL Data Control Language of the Teradata 13.0 Security Administration publication at:

http://www.info.teradata.com/DataWarehouse/eTeradata-BrowseBy-Results.cfm?pl=&PID=&title=%25&release...

Example

The following example grants user Admin_01 the privilege to grant the CONNECT THROUGH privilege to users of a specific middle-tier application, which uses mta as its logon username.

GRANT CTCONTROL
ON mta
TO Admin_01

CONNECT THROUGH Privilege

Use the GRANT CONNECT THROUGH statement to grant the privilege to establish trusted sessions to a middle-tier application, for a proxy user. The GRANT CONNECT THROUGH statement also defines the role or roles available to the user. Database users (administrators) that have been granted the CTCONTROL privilege can execute a GRANT CONNECT THROUGH statement only for the application (trusted_user) specified in the original GRANT CTCONTROL statement.

Note: The administrator issuing the GRANT CONNECT THROUGH privilege to a permanent database user must have the DROP USER privilege on the permanent user.

The GRANT CONNECT THROUGH statement uses the following form:

GRANT CTCONTROL
ON mta
TO Admin_01

where:

• trusted user specifies the permanent user that the middle-tier application uses to login to Teradata Database.

• proxy user identifies the end-user(s) of the middle-tier application who can gain access to Teradata Database through a trusted session. Proxy users can be application end users who are unknown to Teradata Database, or they can be permanent users already defined in Teradata Database.

• role(s) specifies the database roles that are available to be assigned to the proxy users when they connect to the database through the trusted_user application.

Note: The SET QUERY BAND statement sent by the application to Teradata Database initiates the session and specifies which of the roles available to the proxy user are operant for the session or transaction.

For additional information, see “Proxy Users” on page 43 and “Roles for Proxy Users” on

page 41 of the Teradata 13.0 Security Administration publication at:

http://www.info.teradata.com/DataWarehouse/eTeradata-BrowseBy-Results.cfm?pl=&PID=&title=%25&release...

Security Considerations for Trusted Sessions

Consider the following developing security policy for trusted sessions.

• It is the responsibility of the middle-tier application to authenticate end users before connecting them to Teradata Database through a trusted session. Once a trusted session has been established for the application user, Teradata Database controls access to database objects based on the privileges granted to the proxy user.

• Teradata recommends that middle-tier applications not permit end users to submit SQL during a trusted session. If proxy users are permitted to submit SQL, the application needs to prevent them from adding a SET QUERY_BAND statement that would enable the user to assume the identity of another proxy user and utilize that set of user privileges. The application can detect this by issuing an EXPLAIN against the SQL and searching for the QUERY_BAND keyword.

• If logon controls have been set, such as restricting logons by IP address, the system enforces them only for the middle-tier application trusted user logon, not for the application end-user proxy users.

• Use of SET ROLE is not permitted in a trusted session. In a proxy connection, the role is determined by the roles specified in the CONNECT THROUGH privileges granted to the proxy user, along with any role limitations specified in the contents of the SET QUERY BAND statement that is submitted by the application to initiate the session or transaction.

• Trusted sessions established under a username assumable by more than one end user (a group user) will not be able to trace database activity back to the end user in the database access logs. To do this, the SET QUERY BAND statement must be constructed to include the username of the end user.

6 REPLIES
Teradata Employee

Re: Security Administration - Accessing the Teradata Database Through a Middle-tier

Excellent article!
akd
N/A

Re: Security Administration - Accessing the Teradata Database Through a Middle-tier

Any tips on how to deal with a middle tier app which has SSO for users logging into the app and both the database and the mid tier app is integrated with LDAP. It looks like trusted context/query banding would work but does not seems to be the best choice because of some of the security considerations listed above.
Enthusiast

Re: Security Administration - Accessing the Teradata Database Through a Middle-tier

Hi,

I am trying to test few scenarious using my id as a proxy user, but i get below error:

I ran - Grant connect through idw_bo_user to permanent "samir.singh" without role using dbc. i get a message that Grant completed, 1 rows processed.

But when i login to sql assist using idw_bo_user, and run - set query_band ='proxyuser="samir.singh";' for session, i get error that - connect through has not been granted to "samir.singh" through idw_bo_user.

Could you help me here?

Thanks ! Samir

Teradata Employee

Re: Security Administration - Accessing the Teradata Database Through a Middle-tier

let me see if i can find someone to help with this issues. thanks steve

Enthusiast

Re: Security Administration - Accessing the Teradata Database Through a Middle-tier

I think, i figured it out, the double quotes in "samir.singh" is the problem here. Thanks anyway !!

Enthusiast

Re: Security Administration - Accessing the Teradata Database Through a Middle-tier

Hi,

I have posted a question in the connectivity section realted to the same topic so that a broader audience can see it. Could you pleae see if that is something that you can help me with ? Below is the link:

http://developer.teradata.com/database/articles/security-administration-accessing-the-teradata-datab...

--Samir Singh