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;