VERIFY_ROLE_FOR_USER scalar function
The VERIFY_ROLE_FOR_USER function returns a value that indicates whether any of the roles associated with the authorization ID identified by the SESSION_USER special register are in (or contain any of) the role names specified by the list of role-name-expression arguments.
The schema is SYSIBM. The function name cannot be specified as a qualified name.
- role-name-expression
- An expression that specifies a role name (SQLSTATE 42815). 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 (SQLSTATE 42815). The content of the string is not folded to uppercase and is not left-aligned.
The result of the function is an integer. The result cannot be null. The result is 1 if any of the roles associated with the authorization ID identified by the SESSION_USER special register are in (or contain any of) the role names specified by the list of role-name-expression arguments. Otherwise, the result is 0.
Example 1
The tellers in a bank can only access customers from their own branch. All tellers are members in
the role TELLER. A row permission is created by a user with the
SECADM authority to enforce this
rule.
CREATE PERMISSION TELLER_ROW_ACCESS ON CUSTOMER
FOR ROWS WHERE VERIFY_ROLE_FOR_USER(SESSION_USER,'TELLER') = 1 AND
BRANCH = (SELECT HOME_BRANCH FROM INTERNAL_INFO
WHERE EMP_ID = USER)
ENFORCED FOR ALL ACCESS
ENABLE
Example 2
The determination on whether the user can see the row is determined by the name of the role in
the ACCESS_ROLE column of the table being protected. A row permission is created
by a user with the SECADM authority to check the session user is in this
role.
CREATE PERMISSION ROLEACCESS ON CUSTOMER
FOR ROWS WHERE (VERIFY_ROLE_FOR_USER(SESSION_USER, ACCESS_ROLE) = 1)
ENFORCED FOR ALL ACCESS
ENABLE