Segregation of Duties Report - Access? (Roles)

[mralmackay]

Posted on Access group as per recommendation from other user that
Excel wouldn't be the best tool for this.

I have a requirement, however after some help on how I should be
looking to approach this or if anyone knows of any examples that
already do this on the web.

I need to check roles within our systems to ensure there's no SoD
conflicts with other roles.

Out of the system I will be able to get a report that will show the
following:

e.g.
UserID Role
U001 Accounts
U001 Purchasing
U002 Accounts
U003 Purchasing
U003 Invoicing
U004 Accounts
U004 Invoicing
U004 Purchasing

This would be Table1

What I then need to be do is build up a table (however I want from a
layout perspective) that would say which roles conflict with each
other.

For example

Role1 Role2
Accounts Purchasing
Purchasing Invoicing

(Table2)

I then need to be able to run something that will show any user's
who've 'broken' this rule (e.g. User 1, 3 and 4 in the above example)
and highlight which roles are in conflict.

Is this possible? Appreciate any help/guidance/examples on this.

Thanks in advance, Al.

 

[KARL DEWEY]

I do not see how you are deriving a 'conflict' unless you are talking about a
user with two roles.
If that is the case then use this query ---
SELECT YourTable.User, Count(YourTable.Role) AS CountOfRole
FROM YourTable
GROUP BY YourTable.User
HAVING (((Count(YourTable.Role))>1));

 

[Dale Fye]

I assume that it is OK for users to have multiple roles, but if they have two
roles that violate a particular rule (cannont be assigned to both accounts
and purchasing roles) then you want to identify those individuals and the
rules they are violating.

For table 2, I think I would have two columns (Rule#, and Role)

Rule# Role
1 Accounts
1 Purchasing
2 Purchassing
2 Invoicing

Then you should be able to create a query that looks something like:

SELECT table2.Rule, table1.UserID
FROM table2 INNER JOIN table1 ON table2.Role = Table1.Role
GROUP BY table2.Rule, table1.UserID
HAVING Count(table1.UserID) = DCOUNT("Rule#", "table2", "Role = '" &
table2.Role & "'")

Basically, what this says is count the number of records for each Rule/User
combination, and return only those records where that value is equal to the
number of roles for the rule in question.

This particular method would allow you to create a rule that contains three
roles (i.e., user cannot have all three Accounts, Purchasing, and Invoicing,
but could have a combination of any two of the above). If you are certain
that your business rules will only have two roles, then you could shorten
this to:

SELECT table2.Rule, table1.UserID
FROM table2 INNER JOIN table1 ON table2.Role = Table1.Role
GROUP BY table2.Rule, table1.UserID
HAVING Count(table1.UserID) = 2

HTH
Dale