PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 03/21/2017 8:38 AM by  Joan Herzfeldt
Verifying LBI Maintain Rights
 2 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:

--
03/20/2017 12:46 PM
    We are moving from on-premise to the Cloud without realizing that we were expected to maintain LBI security from that initial cut. We have some reports which are bursted. It appears that some people were partially set up. Does anyone have any SQL they use to confirm LBI Rights match?
    Dave Curtis
    HRIS Lead
    University of Maryland Medical System
    Veteran Member
    (292 points)
    Veteran Member
    Posts:102


    Send Message:

    --
    03/20/2017 3:55 PM
    I suppose it would depend on what elements you have your rights built with but here is the start of what I think you are looking for.

    I use this to compare the LBI rights to structure that the rights are built on (such as supervisor codes, department etc).

    Might get you started at least...

    SELECT rm.ruleid,
    rm.rulename,
    rm.ruledescription,
    rm.ruleowner,
    rm.startdate,
    rm.enddate,
    rm.status,
    rv.rulegrouping,
    rv.structureid,
    st.structurename,
    rd.elementorder,
    rv.elementid,
    el.elementname,
    rd.operand,
    rv.elementvalue1,
    rd.elementvalue2
    FROM rsuser.ers_rulemaster rm,
    rsuser.ers_rulevalues rv,
    rsuser.ers_elements el,
    rsuser.ers_structure st,
    rsuser.ers_ruledetail rd
    WHERE rm.ruleid = rv.ruleid
    AND rv.elementid = el.elementid
    AND rv.structureid = st.structureid
    AND ( rv.ruleid = rd.ruleid(+)
    AND rv.elementid = rd.elementid(+)
    AND rv.structureid = rd.structureid(+)
    AND rv.rulegrouping = rd.rulegrouping(+))
    ORDER BY rm.rulename,
    st.structureid,
    rv.rulegrouping,
    rd.elementorder,
    rv.elementid
    Joan Herzfeldt
    System Analyst
    Genesis Health System
    Veteran Member
    (134 points)
    Veteran Member
    Posts:50


    Send Message:

    --
    03/21/2017 8:38 AM
    Hi Angela - This is similar to Dave's but I found and linked the role and username as well.

    select distinct
    M.RULEID
    , U.CONSUMERID AS USERORROLE
    , ghs.GET_USERGROUP_NAME(u.CONSUMERID) as USERNAME
    , M.RULENAME
    , M.RULEOWNER
    , M.STARTDATE --datetime field for CR
    , M.ENDDATE --datetime field for CR
    , D.STRUCTUREID, S.STRUCTURENAME
    , D.ELEMENTID, E.ELEMENTNAME
    , D.ELEMENTORDER
    , V.SEQUENCEID
    , D.OPERAND
    , V.ELEMENTVALUE1
    , D.ELEMENTVALUE2
    , V.RULEGROUPING

    FROM ERS_RULEMASTER M
    JOIN ERS_RULEMAPPINGS U ON M.RULEID = U.RULEID
    JOIN ERS_RULEDETAIL D ON M.RULEID = D.RULEID
    JOIN ERS_RULEVALUES V ON D.RULEID = V.RULEID and D.STRUCTUREID = V.STRUCTUREID and D.ELEMENTID = V.ELEMENTID
    JOIN ERS_STRUCTURE S ON D.STRUCTUREID = S.STRUCTUREID AND S.STATUS = 'A'
    JOIN ERS_ELEMENTS E ON D.ELEMENTID = E.ELEMENTID

    where M.STATUS = 'A'
    You are not authorized to post a reply.