Dynamic CSV String Business Rules and Pseudo Logic
?
0 likes?32 views
The document discusses the process for dynamically generating a CSV string for row-level security based on a user's Active Directory group. It will use a reference table linking AD users to employee data to determine access levels for total company, division, region or account based on the user's AD group. For divisions and regions it will return the primary assignment, with plans to address secondary assignments later. The process for accounts will be defined in more detail later. Examples are given of test users and their expected access levels.
Dynamic CSV String Business Rules and Pseudo Logic
- 1. RLS Prototype ETL | Dynamic CSV String Business Rules and Pseudo Logic Source data will be a reference table pulling data from two (2) existing HR systems: TriMax and D365 FO. The enterprise process and ETL used to gather and create the information in this reference table (EmployeeBranchXRef) are not in scope for the RLS Prototype ETL project. Any changes and modifications to this table will be documented and made to a dev version of this table described below. No changes will be made to the existing table. For this reason, the dev version will need to have its own enterprise process and ETL before this can be released to production. This reference table (EmployeeBranchXRef) is currently located on COLPCSQL03-01EAS01. However, since we intend on modifying this table to meet our specific needs determined below, a copy of this table and its current data have been created on AZRDCSQL01-02EASDEV01. It is recommended that a new name (RLS_EmployeeBranch_XRef) be given to this reference table and a new enterprise process and ETL be designed and created. Our initial goal is to get to 80% of the RLS AD User¨s gathered daily from four (4) AD Groups loaded dynamically using the business rules and pseudo logic below. These business rules and pseudo logic are divided into the four groups below: Total Company, Division, Region, and Account. For Total Company, no CSV_String is required. These customers will be determined from their AD Group. For Division / Region, our basic approach is to use the AD_UserSAM to join to the NetworkID from the reference table, RLS_EmployeeBranch_XRef. This join connects the AD User to Employee data from HR systems by using a reference table. From that same reference table, we can collect the user¨s DivisionID, DivisionDesc, RegionID (to be added), and RegionDesc (to be added) to be used with Division and Region sections described below. AD User ? Employee (HR) ? Division, or Region If other HR information is determined to be needed for the dynamic process, then additional columns and data will need to be added to the reference table. This basic approach is diagramed below: Row Level Security | Dynamic CSV String The RLS Prototype ETL process will use the data located in the RLS_EmployeeBranch_Xref table to load the CSV_String column located in the RLS_AD_ETL. NetworkID and AD_UserSAM will be used to join the two tables for lookups. RLS_AD_ETL (ETL Table) RLS_ETL_KeyPK AD_UserName AD_UserEmail RLS_Level RLS_ETL_Code RLS_ETL_Code_Desc RLS_ETL_Code_Category CSV_String AD_UserSAM RLS_EmployeeBranch_XRef TriMaxIDPK NetworkID EmployeeID BranchCode BranchDesc DivisionID DivisionDesc CompanyID Employee Name Future Defined Attributes... RegionID RegionDesc
- 2. For Account(s), our approach is to use the AD_UserSAM (?) to join to the DimCustomer table. We will circle back and define this section in more detail including a diagram similar to the one above. AD User ? Packaging Consulting Group (DimCustomer) ? Account(s) Business Rules and Pseudo Logic The logic to determine users specific access (CSV_String) is controlled by the AD group they are assigned to. There are four (4) AD Groups that divide what security bucket users fall into. These buckets of users are then used to determine which specific object(s) (division, region, account) they have access to according to the AD group they came from. To accomplish what objects a user should have access to (objects added to the CSV_String), we will examine the RLS_EmployeeBranch_XRef table associated with a specific user, for division and region RLS_Levels. For account(s), we will examine the DimCustomer table. Total Company If the user is in a Corporate branch (typically a numeric code less than 2000) and they are in the Corporate AD group, they have Corporate access. Total company does not require a CSV_String since the ETL process relies on the members of the Total Company AD Group to allow access. It would be possible to populate this with a CompanyID. Test Users from Test AD Groups (temporary, waiting on Real AD Groups with our 3 Test Users) AD_UserName AD_UserEmail AD_UserSAM RLS_Level Bill Stultz bstultz@tricorbraun.com tricorbraunbstultz TotalCompany Jill Pellerito jpellerito@tricorbraun.com tricorbraunjpellerito TotalCompany Jason White jwhite@tricorbraun.com tricorbraunjwhite TotalCompany Division If the user is in the Division AD group, we will determine the primary (default) division of the user by using the DivisionID in the RLS_EmployeeBranch_XRef table. This should give us the specific Division the user has access to. I can only think of a couple of individuals that have access to more than one Division at a time. This is another one-off case we would need to review. This Division AD Group process will only add the primary (default) division assigned to the user. This is what we have determined to meet the 80%. Addressing users that have multiple divisions will be addressed in a future development phase possibly using a lookup table that is maintained manually. Test Users from Test AD Groups (temporary, waiting on Real AD Groups with our 3 Test Users) AD_UserName AD_UserEmail AD_UserSAM RLS_Level Shawn Viola sviola@tricorbraun.com tricorbraunsviola Division
- 3. Region If the user is in the Regional AD group, we will determine the primary (default) region of the user by using the RegionID in the RLS_EmployeeBranch_XRef table. This should give us the specific Region the user has access to. There will be individuals with access to multiple Regions, we will also have to determine how to deal with those. This Region AD Group process will only add the primary (default) region assigned to the user. This is what we have determined to meet the 80%. Addressing users that have multiple regions will be addressed in a future development phase possibly using a lookup table that is maintained manually. Test Users from Test AD Groups (temporary, waiting on Real AD Groups with our 3 Test Users) AD_UserName AD_UserEmail AD_UserSAM RLS_Level Paul Herrick PHerrick@tricorbraun.com tricorbraunPHerrick Region Dolores Mcdaniel dmcdaniel@tricorbraunwinepak.com tricorbraundmcdaniel Region Jim Muster jmuster@tricorbraun.com tricorbraunjmuster Region Account If the user is in the Account AD group, then we will determine which accounts they are assigned to directly. This is an 80% issue. We have cases where two people are technically assigned to an account, but through our system only one is actually assigned. We will need to determine how to approach this. Account level individuals will be given access to any accounts they are specifically associated with. For the Account AD Group process, we will circle back and address this again once Brian had more time to consider. We will have another meeting for this once the sandbox processes for Total Company, Division, and Region AD Groups (discussed above) has been developed. Test Users from Test AD Groups (temporary, waiting on Real AD Groups with our 3 Test Users) AD_UserName AD_UserEmail AD_UserSAM RLS_Level Kim Vega kvega@tricorbraunwinepak.com tricorbraunkvega Account Jayme Herr jherr@tricorbraun.com tricorbraunjherr Account Kari Sinner ksinner@tricorbraun.com tricorbraunksinner Account ** The exceptions (non-80%) may be dealt with in an exception table or some other mechanism. **