VERIFY_ROLE_FOR_USER

If the only way to acquire a role is under a trusted connection that is associated with a trusted context, the VERIFY_ROLE_FOR_USER function is equivalent to the VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER function.

                                              .-------------------------.      
                                              V                         |      
>>-VERIFY_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 any of the roles that are associated with the user that is identified by the SESSION_USER (or USER) special register is in the list of roles specified by role-name-expression. Otherwise, the result is 0.

The VERIFY_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_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_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_ROLE_FOR_USER(SESSION_USER, 'X')

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

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