VERIFY_GROUP_FOR_USER

The VERIFY_GROUP_FOR_USER function returns a value that indicates whether the primary authorization ID and the secondary authorization IDs that are associated with the first argument are in the authorization names that are specified in the list of the second argument.

                                               .--------------------------.      
                                               V                          |      
>>-VERIFY_GROUP_FOR_USER--(--+-SESSION_USER-+----,--group-name-expression-+--)-><
                             '-USER---------'

The schema is SYSIBM.

SESSION_USER or USER: Specifies the value of the SESSION_USER (or USER) special register.
group-name-expression: An expression that specifies an authorization name. The existence of the authorization name at the current server is not verified. group-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 primary or secondary authorization IDs that are associated with the user that is identified by the SESSION_USER (or USER) special register is in the list that is specified by group-name-expression. Otherwise, the result is 0.

The VERIFY_GROUP_FOR_USER function is deterministic within a connection. It is not deterministic across 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: In the following example, the EMPLOYEE table has column access control enabled. If the connection is established outside a trusted context and Mary, who has a secondary authorization ID of "MGR", queries the social security number of Tom from the EMPLOYEE table, the social security number is returned. When Mary is no longer a manager, the same query displays the last four digits of Tom's social security number.

Assume that a user who has SECADM authority has created the following column mask:

CREATE MASK SSN_MASK ON EMPLOYEE
	FOR COLUMN SSN 
	RETURN
	CASE WHEN VERIFY_GROUP_FOR_USER(SESSION_USER, 'MGR') = 1
		THEN SSN
		ELSE 'XXX-XX-' || SUBSTR(SSN, 8, 4)
	END
	ENABLE;

COMMIT;

An ALTER TABLE statement is then issued to activate the column mask on the EMPLOYEE table:

ALTER TABLE EMPLOYEE 
	ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

Mary connects to DB2®, issues the following query, then disconnects from DB2:

SELECT SSN
	FROM EMPLOYEE
	WHERE NAME = 'Tom';

Mary receives Tom's social security number.

When Mary is no longer a manager, the secondary authorization ID, MGR is removed for her authorization ID. The next time Mary connects to DB2 and issues the following command, only the last four digits of Tom's social security number are displayed because of the column mask SSN_MASK:

SELECT SSN
	FROM EMPLOYEE
	WHERE NAME = 'Tom';