Segregation of Accounting Duties Analysis DB Help

Question

Segregation of Accounting Duties Analysis DB Help

brisims34

SSC-Addicted

Points: 479
More actions
January 4, 2014 at 6:45 pm

#302758

Hi All,
I am currently trying to create a database that will help analyze segregation of duties (SOD) conflicts for users and their roles/rights assigned. Within accounting, an SOD conflict would resemble one users ability to perform creation, approval and finalization of a particular transaction. For example, if a use has the combination of rights which allows them to create a vendor, process an invoice and print a check would present an SOD conflict. This will unfortunately depend on the structure of the ERP I am pulling the data from which is shown in the ERP Security structure attachment (all many-to-many relationships except for the AuthToTask - TaskDefinition... I know its not 3rd normal form and I could probably build a VBA routine to normalize it but this is how I get the data from the ERP). I also have a definition of the combination of rights which contain the SOD conflicts for different financial processes which I need to compare to. From what I can tell the Task Definition has the "Rights" to the granularity level I need and are contained in an "Auth" grouping. Since this is a 1-many relationship I need to first determine if an Auth contains any Tasks which present SOD Conflicts from the SOD rule definitions. Second, in the attached graphic you will see a Many To Many relationship between "RoleToAuth" and "AuthToTask". For this level I need to determine if there are combinations of tasks across Auth's within a Role that present SOD Conflicts for each role. Finally, you will see there is a many-to-many relationship between "UserToRole" and "RoleToAuth". For this level I need to identify any tasks assigned through roles then subsequently Tasks which present an SOD Conflict across all roles for each of the users. I have also attached the SOD rule definition schema I was thinking I could use for the comparison but if I need to change please let me know (see file named: "SOD analysis ERPSecStructure Plus SOD Rule structure.png"). I would like to get the DB structure down as well as any query logic to capture these conflicts in a concise manner at each level. Reporting should only show the entities with conflicting Tasks (entities being Auth's, Roles, and/or Users).
Other Notes:
The "Task Definition" table contains the Task ID and Name as well as the following boolean function columns which determine their rights: FunctionReadCol, FunctionAddCol, FunctionEditCol, FunctionDeleteCol, FunctionProcessCol, FunctionFinalizeCol, FunctionViewCol. Addtionally, this table contains a FunctionsCol which summarizes by the first letter the functions assigned in the boolean columns. (e.g., if FunctionReadCol =1, FunctionAddCol =1, FunctionProcessCol = 1 and FunctionFinalizeCol =1 then the FunctionsCol will be "RAPF")
Any help is GREATLY appreciated!
Brian

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

The Many-to-Many table doesn't mean that this isn't in 3rd Normal form.

For the "conflict" resolution that you seek, you're going to need to setup some sort of a "rules" table with a list of roles that are not allowed when other roles are present. This table will need to be checked before trying to adding a person to a new role.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

brisims34 SSC-Addicted Points: 479 More actions · Answer 2

Jeff,

Thanks for the reply! I am working on the tables for the SOD ruleset for comparison. However, I am looking to build something that will facilitate a population user review for each SOD conflict to identify all users which have this conflict so I can change their rights to properly segregate critical functions/tasks within a process. What you described is on the front end of the process during the provisioning process. What I really need is a way to identify all users with an SOD conflict at any point in time. Thanks for the help you have suggested so far!

Brian

brisims34 SSC-Addicted Points: 479 More actions · Answer 3

So it's been a little while and wanted to post again to push it to the top section of the forum. If anyone has any ideas, that would be very helpful. Even if not full tested just to get a conversation/group think tank started on this I think it would help tremendously. I have tried and will continue to try different avenues of approach but am only one mind.

Thanks for any help!

Brian