Cross Reference All LBI Reports to Security

Sort:
You are not authorized to post a reply.
Author
Messages
ALB
Veteran Member
Posts: 130
Veteran Member
    Is there a way to create a list of all LBI reports and the security tied to each? Some of our security is by individual user and some by groups.
    Greg Dey
    Advanced Member
    Posts: 23
    Advanced Member
      I've been using one I created in crystal for awhile. This is something I called report_access:
      -- User Access
      select
      ra.reportid,
      ra.accessvalue as r_user
      from lawson_rs.ers_reportaccess ra
      where instanceid = 0
      and ra.accesstype in ( 'user','usergroup')
      union
      -- Security Groups
      select
      ra.reportid,
      sg.securitygroupname as r_user
      from lawson_rs.ers_reportaccess ra, lawson_rs.ers_securitygroups sg
      where instanceid = 0
      and ra.accesstype = 'securitygroup'
      and sg.securitygroupid = ra.accessvalue
      union
      -- Custom Groups
      select
      ra.reportid,
      cgm.memberid as r_user
      from lawson_rs.ers_reportaccess ra,
      lawson_rs.ers_customgroups cg,
      lawson_rs.ers_customgroupmembers cgm
      where instanceid = 0
      and ra.accesstype = 'custom'
      and cg.groupname = ra.accessvalue
      and cgm.groupid = cg.groupid
      Dave Curtis
      Veteran Member
      Posts: 136
      Veteran Member
        I have used the following. It does not bring in the detail of the group members but could be altered to pull in that info as well.

        SELECT rpt.reportid
        ,rpt.reporttype
        ,rpt.reportname
        ,rpt.publisher
        ,rpt.owner
        ,rpt.outputtype
        ,rac.accesstype
        ,rac.accessvalue
        FROM (SELECT *
        FROM ers_reportaccess
        WHERE instanceid = 0) rac
        JOIN (SELECT *
        FROM ers_reports
        WHERE instanceid = 0) rpt ON (rac.reportid = rpt.reportid)

        ORDER BY rpt.reportid
        ,rac.accesstype
        ,rac.accessvalue
        Joan Herzfeldt
        Veteran Member
        Posts: 74
        Veteran Member
          H Angela, When it comes to reports you can pull users and groups who have access to the report AND/OR who have access to the Schedule of that report. (published reports, Instance=(0) and each schedule, instance=(-1) )
          The query I use is very similar to Dave's. However, we don't use the CustomGroups so Greg's query would not work for us.

          To pull the Users assigned to usergroups, I use this query (I write views and only use crystal to make it look pretty.) However, our Username is our employee number, so we created something special to get the actual name attached to the user. Hence you will not have the GET_USERGROUP_NAME function.

          Hope this helps -Joan ( I forgot - you need to access the LawsonFS database not LawsonRS)

          select
          u.USERID as GROUP_NBR
          , u.USERNAME as GROUP_NAME
          , g.USERID as USER_NBR
          , u2.USERNAME as USER_LAW_NBR
          , ghs.GET_USERGROUP_NAME(u2.USERNAME) as USERNAME

          from
          ENPUSERMAP u
          join ENPGROUPMAP g on u.USERID = g.GROUPID
          join ENPUSERMAP u2 on g.USERID = u2.USERID --and u2.ENTRYTYPE = 1

          where u.ENTRYTYPE = 2 --2=groups 1=users

          ALB
          Veteran Member
          Posts: 130
          Veteran Member
            Thanks!
            ALB
            Veteran Member
            Posts: 130
            Veteran Member
              One of the things I noticed is that at least one report which goes out of to many people had no users other than the owner. It appears there are users tied to the schedule. Is there a way to get that information? Perhaps, that is what Joan has with the LawsonFS database. We have users tied to reports by users, by user groups, custom groups, and custom groups on schedules.
              ALB
              Veteran Member
              Posts: 130
              Veteran Member
                I think I understand now. It looks like instance -1 on ers_reportaccess is for the schedule.
                You are not authorized to post a reply.