report on Space Permissions by User (with Security Groups)

Version 2

    We wanted a report that listed permissions for Space users, including which Security Group they got their access from and also including User Overrides.  So I spent a LOT of time this week creating this SQL script, which may help you.

     

    • We are using On-premises Jive SBS 6.0.3.1.
    • It became too difficult to explode out the details of the User Overrides.as I am reverse engineering because the Core DB is not that well documented.
    • There is an effort by Jive Inc. to document better what each CORE DB does and what the various values are for Type Codes -- I'm told by support that this is project DOCS-314. If you want this effort done sooner, ask Jive to do so. No promises date.
    • Note that Jive does not create permissions for people appearing in the User table due to LDAP/AD syncing who have not yet logged into Jive, even if their LDAP group has been granted permission to a Space

     

     

    See the attached spreadsheet for sample data

     

    Below is the script:

     

    use jivecore
    GO

     

    IF OBJECT_ID('tempdb..#ALL_SPACE_Permissions_prelim') IS NOT NULL DROP TABLE #ALL_SPACE_Permissions_prelim
    IF OBJECT_ID('tempdb..#ALL_SPACE_Permissions') IS NOT NULL DROP TABLE #ALL_SPACE_Permissions
    GO

     

    SELECT distinct e.objectID,e.groupID,U.userID
         into #ALL_SPACE_Permissions_prelim 
      FROM jiveEntitlement E
      join jiveCommunity SP on E.objectID = sp.communityID
      join jivegroup G on e.groupID = G.groupID
      join jivegroupuser GU on G.groupid = GU.groupid
      join jiveuser u on GU.userID = U.userID
          where objectType = '14'
      and e.groupID > 0
      and communityID <> 1 /* not the system-level space */ 
    GO

     

    SELECT  objectID, groupID, userID, PL.name as 'Permission_'
         into #ALL_SPACE_Permissions
    FROM #ALL_SPACE_Permissions_prelim AP
      join jiveGrantedPermLvl GPL on AP.groupID + AP.objectID = GPL.grantedId  + GPL.targetObjectId
      join jivePermLevel PL on gpl.permLevelId = pl.permLevelId
        where
      GPL.targetObjectType = 14  /* spaces */
       and gpl.grantedObjectType = 4 /* Security Group, not User overrides */
      and PL.system = 0 /* SPACE-level permissions */
      and AP.objectID = gpl.targetObjectId /* be sure only picking up rows from jiveGrantedPermLvl that are same object */
    GO

     

    Insert into #ALL_SPACE_Permissions
    ([objectID],[groupID],[userID],[Permission_])
    select distinct e.objectID,convert(bigint,'9999999999999') as GRP_ID_Placeholder,e.userID, '*User_Override*' /* 9999999999999 is a dummy for Group ID for User overrides */
      FROM jiveEntitlement E
      join jiveCommunity SP on E.objectID = sp.communityID
      join jiveGrantedPermLvl GPL on e.userID = GPL.grantedId and gpl.grantedObjectType = 3
      where objectType = '14'
      and e.userID > 2000
      and GPL.targetObjectType = 14  /* spaces */
      and communityID <> 1 /* not the system-level space */ 
    GO

     

    SELECT
    sp.name as Space_,
    SPG.Permission_,
    U.firstName + ' ' +u.lastName as User_,
    G.name as SecurityGroup_NULL_MEANS_USER_OVERRIDE,
    (CASE g.federated WHEN 1 THEN 'FEDERATED' ELSE '' END) as Is_Group_FEDERATED, sp.communityID,
    g.groupID as groupID_NULL_MEANS_USER_OVERRIDE,
    U.userid, U.username,

    GETDATE () as WHEN_RUN

    FROM
    #ALL_SPACE_Permissions SPG
      join jiveCommunity SP on spg.objectID = sp.communityID
      join jiveuser u on SPG.userID = U.userID
      left outer join jivegroup G on SPG.groupID = G.groupID

     

    order by sp.name, G.name,  U.firstName + ' ' +u.lastName, g.federated desc, USERNAME