PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 08/19/2016 11:42 AM by  SurvivorAC
Cross Reference All LBI Reports to Security
 6 Replies
Sort:
You are not authorized to post a reply.
Author Messages
SurvivorAC
Systems Analyst
Owensboro Health
Veteran Member
(221 points)
Veteran Member
Posts:105


Send Message:

--
08/15/2016 9:08 AM
    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
    Private
    Private
    Advanced Member
    (60 points)
    Advanced Member
    Posts:22


    Send Message:

    --
    08/15/2016 11:50 AM
    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
    HRIS Lead
    University of Maryland Medical System
    Veteran Member
    (292 points)
    Veteran Member
    Posts:102


    Send Message:

    --
    08/15/2016 3:40 PM
    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
    System Analyst
    Genesis Health System
    Veteran Member
    (134 points)
    Veteran Member
    Posts:50


    Send Message:

    --
    08/16/2016 7:36 AM
    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

    SurvivorAC
    Systems Analyst
    Owensboro Health
    Veteran Member
    (221 points)
    Veteran Member
    Posts:105


    Send Message:

    --
    08/18/2016 9:38 AM
    Thanks!
    SurvivorAC
    Systems Analyst
    Owensboro Health
    Veteran Member
    (221 points)
    Veteran Member
    Posts:105


    Send Message:

    --
    08/19/2016 10:55 AM
    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.
    SurvivorAC
    Systems Analyst
    Owensboro Health
    Veteran Member
    (221 points)
    Veteran Member
    Posts:105


    Send Message:

    --
    08/19/2016 11:42 AM
    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.