The following sample queries demonstrate how to join security views to other views.

Joining Security Views

The following query lists the user name, object name, and class permission name that the user has on the secured object. The v_SecuredObject view is joined to the v_UserClassPermNames view by using the ObjectKey column.

SELECT UCP.UserName, SO.ObjectName, UCP.PermissionName

FROM v_SecuredObject SO INNER JOIN v_UserClassPermNames UCP

ON SO.ObjectKey = UCP.ObjectKey

ORDER BY UCP.UserName, SO.ObjectName, UCP.PermissionName

Joining Security and Collection Views

The following query lists all collections, by collection ID and collection name, the user name, and the instance permissions for that collection. The v_Collection collection view is joined to the v_UserInstancePermNames security view by using the CollectionID column and the InstanceKey column, respectively.

SELECT COL.CollectionID, COL.Name AS CollectionName, UIP.UserName,


FROM v_Collection COL INNER JOIN v_UserInstancePermNames UIP

ON COL.CollectionID = UIP.InstanceKey


The output from the preceding query will list all instance permissions for individual collections. If a user has class permissions for the collections object (which includes all instances), another query will need to be run to get all of the permissions for users on the collections object. (An object key of 1 refers to the collection object.)

The following query can be run from the v_UserClassPermNames view to list all user class permissions for the collections object.

SELECT UserName, PermissionName

FROM v_UserClassPermNames

WHERE ObjectKey = 1

When using the two preceding queries together, a list of user permissions for all collection classes and instances can be obtained.

See Also


Security Views