Thread: Limiting Access
11-12-15, 20:12 #1Registered User
- Join Date
- Nov 2015
Unanswered: Limiting Access
I am working on a database that will be used my multiple users from different divisions of the company that I work for. I have a working product. I was recently given instructions to limit reports to only show results from the division of the user who is searching the data.
I came up with two solutions. Bother were to have the log in page set a tempVar that would automatically populate applicable search criteria. However, this would require making duplicates of all my forms and queries. One set would allow the users full access. The second set would limit users to their division. The log in screen would direct admin to one set and users to the second set. After building it I felt that there was an easier more elegant way.
I tried to create an IIf statement in the query criteria that would either autopopulate a search criteria if the user was a general user or allow division selection if the user was an administator.
In my query, in the field line, I wrote the following sentence which creates an error:
Expr1: [Area]=IIf(DLookUp("UserSecurity","tblUser","Serial ='" & [TempVars]![ReviewerID] & "'")=1,IIf([Forms]![frmVideoTotal]![cboDivision] Is Null, Like"*",[Forms]![frmVideoTotal]![cboDivision]),[TempVars]![UserDivision])
[Area] is the field
UserSecurity a field in tblUser that is either (1 = Admin) or (2= General User)
[ReviewerID] is the ID number of the user from the log in screen
[frmVideoTotal] is the form with the search criteria that populates a continuous form of video data.
[UserDivision] is the division ID of the user from the log in screen.
[cboDivision] is a combo box with the available divisions.
I have gotten it to correctly assess if the user is an admin or a general user. However in the boolean true statement, I can not get it to return all records if the combobox is left Null. I have errors with the use of the term "Like".
Before the criteria read,
[Area] Like "*" & [Forms][frmVideoTotal][cboDivision] & "*" Or [Forms]![frmVideoTotal]![cboDivision] Is Null
And it worked fine. Any ideas?
I have limited experience and plenty of patience.
Thank you for your help,
11-13-15, 03:21 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
So you need:-
some mechanism that identifies which user belongs to which department
Some means of identifying who the current user is
Some means of identifying which data belongs to which department
OR you split the database departmentally and supply a common front end but connecting to different back ends, which store the actual data.
In many ways option 2 is the best route, unless you have to consolidate data at some stage. Its a fair bit of work to retrospectively tie down a pre-existing system securely and effectively. If users can only connect to their data then you remove any risk of them seeing other departmental data.
Option 1 requires several steps
First off you need to know who the user is. Id suggesst using the API call to find the network logon of that user.. googLe dev asish API, as opposed to writing your own logon process. Resist the temptation to use the access user logon currentuser as all that tells you is what credentials someone has supplied in Access. TBH I'm not sure if the network API call still works properly with windows vista on and with tge new midel if security that came in with Access 2007
You need a table based definition for:-
(If a user can see data for more than one department) a separate table for which departments a user can see. If they can only see obe department then that can be folded into the user table
A link that identifies what data each discrete organisation 'owns'. Im guessing that probably means wherever confidential data exists there must be a way of identifying who owns that data.
Resist any temptation to have discrete tables per department in the same system.
If the back end is in a server db then make use if that server back end's security model.I'd rather be riding on the Tiger 800 or the Norton