Security convenience view which describes security for Folders and Universes.
The underlying SELECT statement behind this view can be found further below the following table.
Column Name |
Data Type |
Description |
|---|---|---|
ACCESSLEVEL |
nvarchar |
Access level assigned to the folder for the given user or group |
CMSID |
varchar |
The actualy name of the CMS host machine without the port number, if used. В This is used in conjunction with OBJECTID or CUID to uniquely identify an object across environments |
FOLDER_OBJECTID |
int |
Foreign key to FOLDERS.OBJECTID (or SI_ID in the CMS) |
FOLDERID |
int |
Unique ID globally of the folder from FOLDERS.ID |
FOLDERKIND |
varchar |
Type of Folder (Favorites, Inbox, Public) |
ISADVANCED |
varchar |
Does the folder have advanced security on it? |
ISINHERITED |
varchar |
Is the folders rights inherited? |
ISINHERITED_ROLE |
varchar |
Is the folder's access level inherited from parent roles? |
ISINHERITFOLDERS |
varchar |
Is the folder's access level inherited from parent folders? |
ISINHERITGROUPS |
varchar |
Is the folder's security inherited from parent usergroups? |
ROLEID |
int |
The roleid of the access level belonging to this folder security record |
ROLETYPE |
varchar |
The type of role (EXPLICITY or EFFECTIVE) |
USERGROUPNAME |
nvarchar |
The name of the user or group with access (explicit/effective) |
USERS_GROUPS_CMSID |
varchar |
The CMSID of the user/group name |
USERS_GROUPS_OBJECT_ID |
int |
The SI_ID of the user/group name |
(Note: This example illustrates the SQL Server version of the V_FOLDER_SECURITY view)
create view [V_FOLDER_SECURITY] as
select f.cmsid as CMSID,
f.id as FOLDERID,
f.objectid as FOLDER_OBJECTID,
f.kind as FOLDERKIND,
case when sur.roleid = -1 then N'No Access' else r.name end as ACCESSLEVEL,
sur.roleid as ROLEID,
usersandgroups.name as USERGROUPNAME,
usersandgroups.cmsid as USERS_GROUPS_CMSID,
usersandgroups.objectid as USERS_GROUPS_OBJECT_ID,
case when sur.ISINHERITED = 1 then 'YES' else 'NO' end as ISINHERITED_ROLE,
case when su.ISINHERITED = 1 then 'YES' else 'NO' end as ISINHERITED,
case when su.ISADVANCED = 1 then 'YES' else 'NO' end as ISADVANCED,
case when su.ISINHERITFOLDERS = 1 then 'YES' else 'NO' end as ISINHERITFOLDERS,
case when su.ISINHERITGROUPS = 1 then 'YES' else 'NO' end as ISINHERITGROUPS,
sur.ROLETYPE
from
sec_users su
inner join folders f on f.id = su.object_id and f.objectid = su.object_cms_id
inner join sec_user_roles sur on sur.object_id = su.id
left outer join roles r on r.objectid = sur.roleid
inner join (
SELECT
ID
,CMSID
,OBJECTID
,KIND
,NAME
FROM
USERS --UNION
UNION
SELECT
ID
,CMSID
,OBJECTID
,KIND
,NAME
FROM
USERGROUPS
) USERSANDGROUPS on SU.USER_OR_USERGROUP_ID = USERSANDGROUPS.OBJECTID and f.cmsid = usersandgroups.cmsid ;
GO
See also