Example: Setting the Role for a Trusted Session Using a PROXYROLE name:value Pair - Teradata Vantage - Analytics Database

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;