Filtering record access by user

[Seth Schrock]

I'm venturing into an unknown area and would like some advice as to what path I should take. I have a database that tracks our loans. Information like who the loan officer is and who the loan processor is are the key information here and is stored in tblLoans. The end goal is to have it so that each loan processor can only see the loans in tblLoans that they are assigned to and each loan officer can only see the loans in tblLoans that they are assigned to.

I would like to be able to use the username that was used to log into Windows as the filter so that I don't have to mess with storing passwords and so that the users won't have to remember another password. I do have a table of employees that stores the Windows username so that I can match them up and see what access they should have. My thinking is that I would have a form open when the database opens that would get the Windows user name and then find the match in the employees table (would also need something for if no match was found, but that will go in a different thread). Once the match is found, then it would get the employee position (Loan Officer or Loan Processor). Based on the employee position, the records would be filtered by either the LoanProcessor field or the LoanOfficer field (I'm not sure if this should be done through a filter in the form or through a query criteria. Help on this would be appreciated). Once this "splash" form has gotten the user information, it would close and open the home form.

I could also create two separate databases: one for loan officers and one for loan processors.

I'm not looking for any exact solution here, but more of a direction (possibly one that I haven't thought of). Once I have a direction, I can search online for how to do it and post in here (in different threads) if I get stuck.

***Edit***
I could have up to 20 (could grow later) users in this database at once. Should I look into putting the backend into either a MySQL or PostgreSQL database and do the permissions through the built-in features? I know Access lists that it can have up to 255 users connected at the same time, but all of the discussions that I have found say that Access gets really slow when there are a lot of users (undefined number) and that it really isn't built for this type of thing. 20 users is the about five times the number of users that I have ever designed for so I don't have any personal experience in what kind of performance issues Access would have.

[NeoPa]

There's very little to say Seth, other than to admonish you to avoid asking multiple questions in one thread ;-)

As far as the design is concerned what you are talking about seems solid. How and what you filter is just the details. The filtering can work on both Loan Officers and Loan processors, without too much complication. You may want to consider how you store the user ID. Sometimes, when the project crashes for instance, project level data is reset, but as long as you test for this before using you should be OK. That's another question anyway.

If you take the standard approach of a single BE (where all the data is actually stored) and multiple copies of the FE (the project) such that each user has their own copy, then things ought to work well enough as long as you code with multiple access in mind.

[TheSmileyCoder]

The general idea of your approach is sound enough. A few things I wish to note though.

Performance wise I have had 30 users at a time doing heavy reading from a Access backend on a network share without issues.


Now that you start mentioning loans however, I would advice that you consider the safety of the data. SQL servers have more detailed user control built in, but they (imo) also have a steep learning curve. You need to at least consider the scenario of what would happen if a user got through to the backend, and manually changed the balance on a few loans?

If you need more control over what can be read, and especially over-written then I would consider if you need a different backend.

[Anas Mosaad]

I'm not sure of you know about RCAC feature of DB2 10.1 LUW -not sure if it's already bundled with the express-C edition or not but don't think. I'm sure that any RDBMS that have such feature will be perfect for your case.
Before RCAC we were using an old trick. Create a view that query data for a specific user. Grant that user access to that view and revoke him access from the original table. Not sure if that would work in your case.

[Seth Schrock]

@NeoPa Apologies for the multiple questions. I guess my mine problem was knowing whether to use a filter or the WHERE clause in a query. Your answer gave me the answer that I needed on that and is much appreciated.

@Smiley Thanks for the performance info. As far as security, our core system takes care of the payment, balance, fees, etc. information. My database is just to track where it is in the process (waiting on the appraisor, who has the loan file, etc.). The biggest reason for the user access control is so that the loan processors can't see how many loan the other processors have been assigned and complain if they have a heavier load.

@Anas Mosaad I'm not sure what RCAC is. I quick google search came up with a ton of different things and a list of common acronyms didn't come up with anything that seemed to be related to databases.

[Anas Mosaad]

RCAC = Row and Column based Access Control

[Seth Schrock]

Thanks Anas. I will look into it and post specific questions in a different thread.

Thanks everyone for your input.

[Anas Mosaad]

I'm looking forward to your questions :)