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,
UIP.PermissionName
FROM v_Collection COL INNER JOIN
v_UserInstancePermNames UIP
ON COL.CollectionID = UIP.InstanceKey
ORDER BY COL.CollectionID
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.