Security View Sample Queries
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 the 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 ID and name, the user name, and the instance permissions for that collection. The CollectionID column is used from the v_Collection view and the InstanceKey column is used from the v_UserInstancePermNames view to join the two views.
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 this 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 (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 queries together, a list of user permissions for all collection classes and instances can be obtained.