Best Practices for User Access Control in Access Databases - Emmanuel Katto Uganda (1 Viewer)

[emmanuelkatto23]

Hello Access Programmers Community,

My name is Emmanuel Katto. I’m currently developing an Access database application for a small organization, and I want to ensure that our user access control is secure and efficient. As I’m not very experienced with Access security options, I would greatly appreciate your expertise and suggestions.

• What are the best methods for implementing user authentication in Access? Should I rely on built-in features, or is it advisable to use external authentication methods?
• How can I effectively implement role-based access control (RBAC)? What strategies should I use to define user roles and their corresponding permissions on forms, reports, and data?
• Is it possible to create an audit trail in Access to track changes made by users? If so, what’s the best way to implement this feature, and what key data points should I capture?

Thank you in advance for your help!

I look forward to hearing your tips and experiences.

Best,
Emmanuel Katto

 

[The_Doc_Man]

Emmanuel, you have asked a "thesis-level" question. You have a LOT of reading ahead of you and there is no substitution for becoming very close friends with the forum's SEARCH feature. There is no way I can fully answer your question - but I can point the way.

Access, since Ac2007, has stopped using the internal user and group permissions that were in earlier versions of Access. They were controlled by a module known as MDAC - Microsoft Data Access Control - that was found to be VERY insecure and therefore a very risky basis for any system that needed security. These days, you have to either have an Access-based login form OR you can use external trust of your environment.

If you have a domain-based network, your most secure method of user authentication is to use the domain's login. Your search topic might be "access login using domain" and similar search topics. Be aware that you will want to use system API calls because the more common method of getting a user ID from the system can ALSO be easily compromised. Lacking a secure domain, you would need to build your own login form.

In all cases, if you worry about user roles, you need to see about "securing a database" which will describe an "all forms" setup in which you disallow users from seeing ANY INFRASTRUCTURE on your DB. Things like turning off the ribbon, having "dispatch" or "switchboard" opening forms to immediately establish control over your environment, having queries that are dynamically built to include department or other groups as part of their filtration... it is a never-ending saga to find and fix such things.

The search phrase "role-based access" is another good search topic here. To do this, you use your login method to get to an Access-contained table of users and their roles. Then you have to decide exactly what level of access is implied by each role. For example, do some roles have specific whole forms disallowed? Do some forms operate but only on subsets of the entire data set? Do some functions on the form stop working for some roles but other functions on the same form still work? These are the questions you must decide ahead of time.

Audit trails are possible in Access. Again "audit trail" is a good search topic. Your audit capabilities can be quite extensive - and expensive. Decide just how much you want to track. Since Access database files have a 2 GB limit, there are strategies where you keep your audit files in a separate back-end file from the main file. Other strategies include frequent export of the audit history to a text file followed by removal of the exported entries from the DB.

I have done all of the things you asked about. One person working on a living DB meant it took me over a year to get everything close to what I wanted - and during that time, folks asked for more features so it was a never-ending story until the day I retired.

 

[GPGeorge]

I just want to second The_Doc_Man's comments, especially with regard to the extent of the reading, testing and validating ahead of you.

Terms like " best methods" regarding any practice tend to make me nervous. Why? Because for every person's preferred method of doing something, there will be someone else who suggests their method is better. And they both can be right, depending on the specific circumstances and requirements.

Cost is, unfortunately, a factor in all such decisions. Is the cost of implementing any given solution affordable, given the constraints in place? How much is enough for you in your situation and how much is too much?

I once participated in a large project in which we created an audit trail for a process. After spending thousands of dollars of development cost to implement it, the client barely acknowledged it, and probably never actually used it. Why? Because they were checking off a requirement to have auditing in place, and not particularly concerned about the result in itself.

Role-based security can be evaluated the same way. How important is it to the usability, reliability and security of this application in this environment in this situation? What data is being protected? From whom? The answer will come from consulting the end users and the project sponsor. If your organization insists on strict segregation of functions to protect sensitive data, you can invest time (and development cost) in that effort. I'm cynical enough to believe that it is easier for a project sponsor to ask for certain things than it is for that sponsor to justify the cost. So, make sure you're solving the right problem.

A search here at AWF will turn up many excellent discussions of specific implementations deployed by members, though, so what you are looking for is largely a matter of doing your own due diligence.

 

[Pat Hartman]

Access is not secure so your security will deter a determined hacker for only a short time. Think of Access security as helping honest people stay in their lane. This is a link to a very simple type of role based security. It requests a userID and password to open the application and there are tables to control a user's security level. The security level for each main form is defined using the menuing system. If you also use the custom switchboard for your users, it will only select items that the logged in user has view permission for.

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

I think the documentation is included but just in case, here it is.