How to report on many to many relationship?!

[LizzyJ]

Hi,

I have been trying to design an Access 2013 desktop database to report on user access to shared folders within the company I work for. Acess to folders is granted by users being added to a permission group and groups being added to folders.

What I want is to be able to query the data and find out which users have access to which folders. My tables are currently:

tblGroups
GroupID: PK Autonumber
GroupName: Short Text
GroupType: Lookup (Permission; Distribution; Mailbox Sharing)

tblUsers
UserID: PK Autonumber
FirstName: Short text
Surname: Short text
Username: Short text
Groups: Lookup- tblGroups.GroupName

tblFolders
FodlerID: PK Autonumber
RootFolder: Short text
FullRootFolderAccess: Lookup- tblGroups.GroupName
ReadOnlyRootFolderAccess: Lookup- tblGroups.GroupName

What I want to be able to pull back out of the system is the answer to the question 'who has access to which folders' and 'which folders does [insert name] have access to'.

Ideally I don't want to have to create a junction table- I want to be able to add a new user based on the tblUsers and that be that.

Any help greatly appreciated.

If I do have to have a junction table is there anyway it can autopopulate from the other data entered?

Thanks

LizzyJ

(Access 2013 is the version I am using)

 

[DreamingSpires]

Hi Lizzy

Can a person only belong to one group? And can a folder only belong to one group for FullRootAccess and one group for ReadOnly?

 

[plog]

Ideally I don't want to have to create a junction table

Good news, you don't need a junction table, you need 2 junction tables. The way to define a many-to-many relationship is via a junction table. And you have 2 such relationships:

many users can belong to many groups
many groups can belong to many folders

The structure of those junction tables is simple (there's nothing to "auto-populate"):

UserGroups
UserGroupID, autonumber, primary key
UserID, number, foreign key to User table
GroupID, number, foreign key to Group table

FolderAccess
AccessID, autonumber, primary key
GroupID, number, foreign key to Group table
FolderID, number, foreign key to Folder table
AccessLevel, text/number, determines level of access to folder (Read Only, Full, etc)

You will also remove the corresponding fields that are in the other tables that are accounted for above (e.g. tblUsers.Groups, tblFolders.ReadOnly...)

That's how this should be structured.

 

[jdraw]

Further to plog's advice --- do not use lookups at the table field level.

If Folder Access is controlled/restricted by Group -- only Groups are allowed Access to Folders ---then each User must be a member of 1 or more Groups.

I would suggest more meaningful names to your junction tables(but names are subjective)

GroupHasAccessToFolder

UserIsMemberOfGroup or similar.

 

[LizzyJ]

Thanks all.

I will have a go with the junction tables and see if I can create the report I need to pull the information back out (either 'who has access to this folder' or 'what folders does this person have access to').

It looks like there is no way to simply create a user and multi select which groups they are in which was what I was hoping to do...

 

[jdraw]

You have to get the tables (where data is stored) set up correctly.

You can use queries and formatting to get the presentation you want. Get your tables designed and tested to meet your business requirements first.

 

[LizzyJ]

OK, that has worked to display the access information. And I have designed a form for adding a user that can also add them to the groups so that is ideal.

I now want to complicate things slightly with subfolders.

There are some root folders that contain subfolders with further restriction. The same groups are used to designate access to these subfolders. Now the complicating factor is that a user will only have access to a subfolder if they are in a group that gives them access to the root folder AND a group that gives them access to the subfolder. It is possible that a user would be in a group that has access to the subfolder but not actually be able to gain access because they are NOT in a group that has access to the root folder.

I hope that makes sense- any ideas greatly appreciated.


LizzyJ

 

[plog]

I don't believe this changes anything. It just requires you to be more specific in your tblFolders.RootFolder field.

Instead of having this:

C:\FolderA\

If you want to restrict access on a sub-directory, you will need to put this:

C:\FolderA\Sub1

The structure is still valid, you just have to do more data entry to identify the lowest level you want to control access at.

 

[LizzyJ]

Yes I could list all the folders pathways as root folder but that is not ideal. Ideally I want to list the root folders and then the subfolders separately where needed.

And the complication comes when (taking your example) Group 1 has access to Folder A and Group 2 has access to Sub 1. If Joe Bloggs is in group 2 (but not in group1) he will not actually have access to sub 1 because he can't get to it in the first place (because he can't go through Folder A)

Does that make sense?

LizzyJ

 

[plog]

It makes sense and my explanation accomodates it. You need to assign permissions at the most granular level.

If Group1 can get to C:\FolderA\sub1 & C:\FolderA\sub3 & C:\FolderA\sub4, but not C\:FolderA\sub2 then you can't use this "root" directory idea you have, but you can still use your table structure. You need to assign them each individual directory they have access to.

You wouldn't have C:\FolderA in your database at all, you would have multiple records for each sub in it that you want to control access to. You must assign access at the most granular level.

 

[The_Doc_Man]

Windows security has a permission properly "inheritable" but what you really want its converse, "inherited."

A couple of ways to do this come to mind. From a raw functionality viewpoint, you have recursion in this mix - whether you wanted it or not - because of multi-layered and variable inheritance. This will affect your data layout a bit, so I might be complicating your issue for you. But what I am about to show you is pretty much the Windows File System permissions paradigm, so let's take a whack at it.

Start with a folder structure that includes three fields - a PK, the folder name string in question, and an FK pointer to the parent of the folder IN THE SAME TABLE. The full path could always be used for searching for an arbitrary starting point for this search. To keep the references from getting entirely too "stringy" (verbose), every folder needs a synthetic/numeric PK even though you COULD do it by string-search only. The folder table is an inverted hierarchy in which a folder points to its parent - and the root points to itself.

Your list of groups is just that - a list - with a group name and a PK. Flat.

Your Many/Many access table points (via FK) to a folder to which that group has access and (via FK) to the group in question. The access granted for that combination is in the M/M table, because it is defined by the COMBINATION of folder and group, not uniquely by either one.

OK, the recursion is that you have to write a function that gets a folder key and a group ID key. Using the M/M table, search for the folder/group combination to see if the given group has access to the given folder. If not (because no records found with that folder/group key combination) then you are done and can exit the function with "no access" code.

If the group/folder combo is present, the function must follow the parent folder pointer and repeat the M/M table search. Again, if no records match, return "no access." Otherwise, recurse yet again. Eventually you will hit the folder that is its own parent - which means your group has access all the way from the root to the original folder. In which case your function returns "has access."

Bells and whistles here would be to decide whether you track READ separately from MODIFY or FULL CONTROL or even whether you step into the detailed permissions like "passthru" etc. ANOTHER bell/whistle is to populate the M/M table with all child folders (this part HAS to happen else you could never find the folder) and one more flag that indicates that the folder has parents with inheritable permissions - which modifies the recursion. As an alternative, you would simply elaborate all combinations so that every folder has an M/M entry for explicit OR implicit permissions. This latter method differs from the true Windows File System model slightly.

In summary, three tables:

1. Folder (hierarchy) with fully qualified string for folder, PK, and FK to parent folder in same table. Plus anything else you wanted to track regarding folders.

2. Group (flat) with group name, PK

3. Many/Many table with FK to folder, FK to group, and the permissions recorded by the group (which is how you would check the detailed permissions.)

Normally I would say that you would need ANOTHER table that listed a PK for an entry in the M/M table and could then look up a set of permissions selected by that PK. However, I'm a pragmatist, not a purist. This is a case where I might denormalize slightly, knowing that Windows File Permissions are limited in number and I really only need to store (at most) less than 32 flags. Probably less than that, but certainly not more than 32. So I might take an easy way out and just track the individual permission flags either as Boolean variables or as a single LONG for which bit-masking methods were applied.

 

[LizzyJ]

Plog- it is not as simple as that. To gain access to the subfolder the user might need to be in two different groups (one to get into the root folder and a different one to get into the subfolder). Whilst there might still be multiple groups that COULD give access.

The_Doc_Man- thank you for the response. I think what you are describing is what I want- and yes read/modify would be useful (but could be avoided by having group type field including read; modify)

I fear that you have lost me though in the detail... I have managed to create tables to report what I need for the root folders.

For reference most of the root folders do not have additional restrictions but some of them do (hence the need to report on them) and I think this probably is the Windows file system you refer to that I am reporting on.

Any further ideas/ explanation...

LizzyJ

 

[The_Doc_Man]

My thought is that you can write a public function (in a general module) that returns a code. Two ways to skin this cat... either (1) build a three argument function where argument 1 points to folder, argument 2 points to group, and argument 3 is the level of access you were checking. Return T/F from that. OR. (2) build a two-argument function with folder and group pointers and get back some encoded value where 0 is no access, 1 is read access, 2 is read/write access, 3 is other (higher) access, etc.

The key is that using DoCmd.RunSQL, you can write queries that call the function as though it were a field (and in fact it is a computed field). Then you can write WHERE clauses that select on the function return.

I fear I lost you in the detail because you need to navigate the Windows File System with machete and camera. (It's a true jungle...) But what I described is (roughly) the way the file system actually operates. Note that it would be possible to do this the other way and have the hierarchy go from root to leaf-node, but doing it that way you would have to read the directory contents for each search, browsing for the name of child folders until you found (or did not find) the one you wanted.

I also missed one fine point... if you are testing a user, that user can belong to multiple groups, so the recursion function MIGHT actually need to explicitly use the PK of the username, which would then let you do a more complex search in which you first have to look for all groups of which that user is a member, THEN get to the group ID from that, and it would become a loop in each level of the recursion to see if ANY group bearing that user as a member has access to the current folder of interest. Now... tell me that you don't have cases where (despite all of Microsoft's efforts to advise against this) some permissions are held directly by users. Because if so, you will ALSO need to populate the M/M table with some user IDs as though they were single-member groups.

Does this help any better in seeing what I am describing?

 

[LizzyJ]

thank you for all the help.

Have decided to simplify slightly for now in order to get it built quicker but I might take a look at all of the above in the future.

Thank you to plog for the initial advice that solved the original problem!

LizzyJ