LBI Security Groups

Sort:
You are not authorized to post a reply.
Author
Messages
Tim
Basic Member
Posts: 12
Basic Member
    What table in the LBI meta model relates to  ERS_SECURITYGROUPS and identifies all of the users assigned to it?

    I would like write a SQL query to identify the individuals that are assigned to a security group.

    Thanks,

    Tim

     

    Matthew Nye
    Veteran Member
    Posts: 514
    Veteran Member
      Tim,

      I noticed you said you found the ERDs for LBI. What do you mean by this? As far as I know there is no published ERD for the LBI repository. If you have them I would LOVE to get my hands on them.

      The table youre looking for is ERS_REPORTACCESS. They use a fake report ID to map all security groups.

      SELECT SECURITYGROUPNAME GroupName, ACCESSVALUE UserName
      FROM ERS_REPORTACCESS ra
      INNER JOIN dbo.ERS_SECURITYGROUPS sg ON ra.JOBNAME = sg.SECURITYGROUPID
      WHERE ra.REPORTID = -99 AND INSTANCEID = -99
      ORDER BY SECURITYGROUPNAME, ACCESSVALUE

      Again, this is just my own discovery. None of this is documented (read: supported) by lawson. Much of the SQL they use is prepared in their compiled code so its a bit difficult, if not time consuming, to translate.

      hth
      Matt
      If any of my answers were helpful an endorsement on LinkedIn would be much appriciated! www.linkedin.com/pub/matthew-nye/1a/886/760/
      Tim
      Basic Member
      Posts: 12
      Basic Member
        Thanks Matt! I really appreciate it.

        I have verified your query against some roles we have set up and the users assigned. It is accurate.
        I knew that ERS_REPORTACCESS held the users and groups that had access to a report, but would have never guessed that a fake report ID would be used to define the users assigned to a group. I was expecting a table similar to ERS_LISTUSERS.

        Here is where I found for ERDs...there are on this site.
        1. Go to https://www.lawsonguru.co...ch/DatabaseERDs.aspx
        2. There are tabs defined for LBI-Framework Services, LBI-Reporting Services, and LBI-Smart Notifications.

        I don't see a lot of relationships the reporting services diagram.

        Thanks for your help!

        Tim
        Matthew Nye
        Veteran Member
        Posts: 514
        Veteran Member
          Hmm, interesting. Hadnt see that John put these together. Lawson has never published any documentation similar to the ERP applications (data definition or tech docs). RS has very few defined relationships as they are enforcing data integrity on the business logic layer.

          I would encourage you to check your logic for any programming you do against the repository everytime you upgrade LBI as the ERD has been known to change dramatically between even minor versions.
          If any of my answers were helpful an endorsement on LinkedIn would be much appriciated! www.linkedin.com/pub/matthew-nye/1a/886/760/
          Ruma Malhotra
          Veteran Member
          Posts: 412
          Veteran Member
            In the table ERS_REPORTACCESS, ers_Securitygroups
            reportid=-99 and instanceid =-99 gives the list of all users that belong to a security group.

            You are not authorized to post a reply.