VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER

The VERIFY_TRUSTED_CONTEXT_FOR_USER function returns a value that indicates whether the authorization ID that is associated with first argument has acquired a role in a trusted connection and whether that acquired role is included in the role names that are specified in the list of the second argument.

                                                              .-------------------------.      
                                                              V                         |      
>>-VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER--(--+-SESSION_USER-+----,--role-name-expression-+--)-><
                                            '-USER---------'

The schema is SYSIBM.

SESSION_USER or USER: Specifies the value of the SESSION_USER (or USER) special register.
role-name-expression: An expression that specifies a role name. The existence of the role name at the current server is not verified. role-name-expression must return a built-in character string data type or graphic string data type that is not a LOB. The string must have a length that does not exceed the maximum length of an SQL identifier. The content of the string is not folded to uppercase and is not left justified.

The result of the function is a large integer. The result cannot be null.

The result is 1 if the user that is identified by the SESSION_USER (or USER) special register has acquired a role under a trusted connection that is associated with a trusted context and that role is in the list of role-name-expression. Otherwise, the result is 0.

The VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER function is deterministic within a trusted connection. It is not deterministic across trusted connections. The function can be referenced in a CREATE MASK or a CREATE PERMISSION statement and is considered for table expressions or the merging of views.

Example 1: Assume that the following statements have been issued to create specific roles and the trusted context CTX1:

CREATE ROLE EMPLOYEE;
COMMIT;

CREATE ROLE MGR;
COMMIT;

CREATE ROLE PAYROLL;
COMMIT;

CREATE TRUSTED CONTEXT CTX1
	BASED UPON CONNECTION USING SYSTEM AUTHID ADMF001
	ATTRIBUTES (ADDRESS '9.30.131.203', ENCRYPTION 'LOW')
	DEFAULT ROLE EMPLOYEE
	ENABLE
	WITH USE FOR SAM, JOE ROLE MGR WITH AUTHENTICATION;

COMMIT;

Joe, who is a manager, issues the following dynamic query through the trusted connection CTX1 to view the salaries of the employees in the DSN8910.EMP table that are in his department:

SELECT SALARY FROM DSN8910.EMP
	WHERE VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,'MGR','PAYROLL')= 1
	AND WORKDEPT = ?;

Example 2: For the following example, suppose that a user with SECADM authority needs to control access for specific users who execute a statement that is accessing a table:

Is the current user, B, using role X to run a statement owned by user C

SESSION_USER = B AND
VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER, 'X')

Is the current user, B, using role X to run a statement owned by role D

SESSION_USER = B AND
VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER, 'X')

Is the current user, B, using role B to execute a dynamic statement

SESSION_USER = B AND
VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER, 'B')