How to limit usability of Access objects per user? (3 Viewers)

[P J Bryant]

This frequently comes up and unfortunately a lot of people don't realize the back draws on this method.
There are a lot of practical concerns worth mentioning.

First, relying on Active Directory ties your application user directly to the Windows login. In environments where PCs are used as shared terminals (e.g., clinics, hospitals, schools, universities, companies like ours with a lot of employees, ....), this becomes a limitation. Staff typically move between stations and only log in/out of the application, not Windows. Requiring a full Windows login/logout cycle for each user is slow and disrupts workflow, making AD-based control less suitable in these scenarios. Just imagine a user needs to do a search on Orders when he's not on his desk and is on the shop floor. How long does it take to log out of windows and login with his account just for a search. Or an edit on a record.

There are also some technical drawbacks:

• Nested groups may not resolve easily, depending on how membership is retrieved
• There can be performance hits at startup when querying AD for group membership
• It requires domain connectivity to function properly
• If AD is unavailable, cached credentials may not include full group information, leading to incorrect or incomplete permission assignment

So while AD groups are great for centralized management, they don’t always align well with real-world usage patterns, especially in shared workstation environments.

If someday I want to use active directory security for my database, instead of querying the server directly, I add Users and AD group memberships tables to my BE, then sync it peridically with AD. This way, my app can be used on its own.

Of course it depends on circumstances, but as the original post didn't have those details my answer remains a valid proposition.

It's one I have used many times for many clients since the 90's and has always worked really. Clearly YMMV; but if it suits the OP's setup, then it could work well given the granularity the solution offers a dev.

In my experience clients' IT support companies like it because they are not required to implement yet another security mechanism.

In terms of the your concerns:
* Nested groups - this has never been an issue, as if well designed a group can control multiple security boundaries. Nested membership can actually be a boon, reducing the number of groups to be maintained. The user having multiple group membership is fine as this increases the granularity of the solution.
* I have never seen a performance hits at startup; it's a single API call i know has been run 100's of thousand times without any fault ever being reported.
* Naturally it requires domain connectivity as that's how it works.
* TBH - if AD is unavailable then the client has a much bigger problem than just their access app.
* Although once can cached the tables, as it is only a single API call on startup which happens in an instance I've always preferred to store on launch of the app as it means a security change only requires the user to close the app and restart to gain (or lose) the apporpriate privileges.

 

[The_Doc_Man]

First, relying on Active Directory ties your application user directly to the Windows login. In environments where PCs are used as shared terminals (e.g., clinics, hospitals, schools, universities, companies like ours with a lot of employees, ....), this becomes a limitation.

While you see it as a limitation, you have no problem with imposing three-factor authentication on financial things. We've already discussed that aspect of your society. Do you remember to log out when you finish your transaction that required the extensive validation sequence through your smart phone?

Failure to log out is not only risky; in some environments, it is illegal. For example, at any office dealing with personally identifying information, you have to show that you are legally allowed to view it. This applies specifically to military, financial, and medical data. It is why you use smart-cards to log in to your 'puter. Given the limited ability of Access to do "real" object-level security, I would think you would either stop using Access or force people to at least use secure logins so that when you perform an action log, you blame any repercussions of that action on the right person.

 

[KitaYama]

While you see it as a limitation, you have no problem with imposing three-factor authentication on financial things. We've already discussed that aspect of your society. Do you remember to log out when you finish your transaction that required the extensive validation sequence through your smart phone?

Failure to log out is not only risky; in some environments, it is illegal. For example, at any office dealing with personally identifying information, you have to show that you are legally allowed to view it. This applies specifically to military, financial, and medical data. It is why you use smart-cards to log in to your 'puter. Given the limited ability of Access to do "real" object-level security, I would think you would either stop using Access or force people to at least use secure logins so that when you perform an action log, you blame any repercussions of that action on the right person.

Doc, I'm sorry. I can't understand what you mean. Are you trying to tell me it's better to create a database that needs a login/logout to windows for each user to be able to use a database?
If yes, you answered yourself by saying Access is not safe enough for that kind of secure data.

I would think you would either stop using Access or force people to at least use secure logins

I think you've misunderstood the whole conversation. I never told I need that level of security. It was others who suggested using Active directory's user level security, and I explained why it's not a good suggestion (in my opinion)