_IV_SUBJECT_MEMBERSHIP System Query

The system query _iv_subject_membership returns information on privilege subject relationships.

Users, groups and roles are the privilege subjects and their relationships affect user privileges.

Each record in the result describes one relation to another. Both subjects are redundantly characterized. You can use the following constants for subject categories: CATEG_USER (1), CATEG_GROUP (9) and CATEG_ROLE (10).

Column description

Column name	Type	Contents
`subject_name`	`CHAR(31)`	Name of the subject who is a member (user or group)
`subject_categ`	`SMALLINT`	Category of the subject who is a member (user or group)
`subject_uuid`	`BINARY(12)`	UUID of the subject who is a member (user or group)
`subject_objnum`	`INT`	Number of the subject who is a member (from the USERTAB table)
`container_name`	`CHAR(31)`	The name of the containing subject (group or a role)
`container_categ`	`SMALLINT`	The category of the containing subject (group or a role)
`container_uuid`	`BINARY(12)`	UUID of the containing subject (group)
`container_objnum`	`INT`	The object number of the containing subject (from the USERTAB for groups, from the OBJTAB for roles)
`container_schema`	`CHAR(31)`	Schema name of the containing subject (role)

Examples

This query displays a list of groups David is a member of.

SELECT container_name FROM _iv_subject_membership 
WHERE subject_name='DAVID' AND subject_categ=CATEG_USER 
    AND container_categ=CATEG_GROUP

This query displays a list of users belonging to the Accountant group.

SELECT subject_name FROM _iv_subject_membership 
WHERE container_name='Accountant' AND subject_categ=CATEG_USER 
    AND container_categ=CATEG_GROUP

This query displays a list of subjects that are a member of the Author role in the current application.

SELECT subject_name,CASE WHEN subject_categ=1 THEN 'User' ELSE 'Group' END AS Categ 
FROM _iv_subject_membership 
WHERE container_name='AUTHOR' AND container_categ=CATEG_ROLE 
    AND container_schema=CURRENT_APPLICATION

A condition whether David belongs to the Accountant group:

EXISTS(SELECT * FROM _iv_subject_membership 
       WHERE subject_name='DAVID' AND subject_categ=CATEG_USER 
       AND container_categ=CATEG_GROUP AND container_name='Accountant')

See

_IV_LOCKS System Query

System Queries

_IV_PROFILE System Query