Advanced Query Question

[ssteinke]

I am trying to restrict the user from only seeing the records that apply to that user. Given the following tables:

tblSecurityControls
SecurityControlID pk
SecurityControl

tblDepartments
DepartmentID pk
Department

tblUsers
UserID pk
UserName

tblRecords
RecordID pk
SecurityControlID fk
DepartmentID fk

tblUserRecords
UserID fk
RecordID fk

You will notice that tblRecords has a one-many relationship with tblUserRecords. This is done because of the following rules: If the SecurityControlID=1 in tblRecords, then the record is PRIVATE and it should only be available to the users identified in tblUserRecords. If the SecurityControlID=2 then the record is accessible only to the Department which created the record. If the SecurityControlID=3, then the record is globally available to ALL Departments. The current user's UserID & DepartmentID are held in global variables during the session, i.e. gUserID & gDepartmentID.

How do I show only the records in tblRecords that are available to the current user given the previously identified rules? Would I need to perform multiple queries?

Thanks in advance,

~Scott

 

[DCrake]

You do not state whether users can be assisgned to more than one department?

If you work on the pretext that one user can only work in one department and each user is assisged an access level denoted as a three digit code

1
12
123
2
23
3

Then in your query in your SecurityControlID will have the following criteria

In(gUserLevel) where gUserLevel = whatever variation has been assigned to them.

 

[ssteinke]

You do not state whether users can be assisgned to more than one department?

Good point. Only one department for each user.

However, the users are not assigned access levels as you suggest.

A record in tblRecords is assigned a Security Level when it is created. The three security levels have different meanings (see above). Each record in tblRecords is also assigned a DepartmentID to show which Deparment created the record.

Broken down: Lets assume 'Bob' is assigned to department 'Sales'

Bob should be able to see the following records:

• Any record where tblRecords.SecurityControlID=1 AND tblUserRecords.UserID='Bob'
  
• Any record where tblRecords.SecurityControlID=2 AND tblRecords.DepartmentID='Sales'
  
• Any record where tblRecords.SecurityControlID=3 (since all records with a 3 can be viewed by EVERYONE).

You will notice that records containing a 1 for a SecurityControlID need to look at another table to determine if the current user is allowed to see the record. (Since a 1 denotes a private record).

However, a record that contains a 2 or 3 only need to look at the current table to determine if the current user is allowed to see the record.

Based on this layout... what is the best way to query the data?

Thanks very much any help... I am too close to it to see the answer

 

[DCrake]

The issue of looking at two different tables is confusing. Why not have a flag in the users table that indicates whether the user can view private records. then when you do the query you would add the condition there to match the users permissions.

 

[ssteinke]

Why not have a flag in the users table that indicates whether the user can view private records.

I love your suggestion, it would certainly be much easier. However, if a record is private, only specific users can see the record (which is the purpose of tblUserRecords). It's not a case where a user who has a certain permission can see the private records. The user MUST be specifically identified in tblUserRecords in order to see that particular record.

I love your thoughts... any others?

 

[MSAccessRookie]

Is something like this what you are looking for? (Substitute for the RED as required)

[FONT=Times New Roman][SIZE=3]Select [COLOR=red][B]WhateverYouAreLookingFor[/B][/COLOR] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]From  [/FONT][/SIZE][COLOR=black][FONT=Verdana]tblRecords Inner Join tblUserRecords [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]On tblRecords.[COLOR=red][B]WhatTheyJoinOn[/B][/COLOR] = tblUserRecords.[COLOR=red][B]WhatTheyJoinOn[/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Where ((tblRecords.SecurityControlID=3) Or[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    ((tblRecords.SecurityControlID=2) And (tblRecords.DepartmentID='Sales')) Or[/FONT][/COLOR]
[SIZE=3][FONT=Times New Roman]      ((tblRecords.SecurityControlID=1) And (tblUserRecords.UserID='Bob')))[/FONT][/SIZE]

 

[ssteinke]

Almost looks too easy! I suppose I may need to replace the Inner Join with a Left Join since not ALL records will have a record in tblUserRecords. I will plug it in later, but it looks good on paper! Thanks for your help guys! The next beer is on me!

 

[MSAccessRookie]

Almost looks too easy! I suppose I may need to replace the Inner Join with a Left Join since not ALL records will have a record in tblUserRecords. I will plug it in later, but it looks good on paper! Thanks for your help guys! The next beer is on me!

If that is the case, then a Left Join might be better. Either way, I hope we have provided a good start for you.