Example: Setting the Role for a Trusted Session Using a PROXYROLE name:value Pair - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

The following example set shows how to change the proxy role in a proxy session using SET QUERY_BAND requests. All of the examples in this set are run in Teradata session mode.

This example uses the PROXYROLE name:value pair in a query band to set the proxy role in a trusted session to a specific role.

     SET QUERY BAND='PROXYUSER=fred;PROXYROLE=administration;'
     FOR SESSION;

This example uses the PROXYROLE name:value pair in a query band to change the proxy role in a trusted session that is set by a session query band.

     SET QUERY_BAND='PROXYUSER=cluser1;PROXYROLE=role1;' FOR SESSION;
     BEGIN TRANSACTION;
      SET QUERY_BAND='PROXYROLE=role2;' FOR TRANSACTION;
      SELECT * 
      FROM table;
     END TRANSACTION;

This example shows that the PROXYROLE value in a query band can be changed multiple times in a transaction.

      BEGIN TRANSACTION;
       SET QUERY_BAND='PROXYUSER=cluser1;PROXYROLE=role1;' FOR TRANSACTION;
       SELECT * 
       FROM table;
       SET QUERY_BAND='PROXYUSER=cluser1;PROXYROLE=role2;' FOR TRANSACTION;
       SELECT * 
       FROM table2;
      END TRANSACTION;

This example also uses the PROXYROLE name:value pair in a query band to change the proxy role in a trusted session that is set by a session query band.

     SET QUERY_BAND='PROXYUSER=cluser1;PROXYROLE=role1;' FOR SESSION;
     SELECT * 
     FROM table;
     SET QUERY_BAND='PROXYUSER=cluser1;PROXYROLE=role2;' FOR SESSION;
     SELECT * 
     FROM table2;

This example fails because it attempts to change the role for a transaction trusted session using a session-based query band.

      BEGIN TRANSACTION;
       SET QUERY_BAND='PROXYUSER=cluser1;PROXYROLE=role1;' FOR TRANSACTION;
       SELECT * 
       FROM table;
       SET QUERY_BAND='PROXYROLE=role2;' FOR SESSION;
      END TRANSACTION;