Access 2016 - Users and permissions?


Keywords:ms  access 


Question: 

My employer is strictly limited to MS Office products and SharePoint. I am unable to utilize a different database solution.

I am trying to implement and share an Access database to allow multiple users to enter and edit data. However, I would (of course) like to limit the ability of users to edit the database itself (tables, forms, etc...), and only able to view and edit specific data via forms.

Unfortunately, it appears Microsoft has removed users and security from newer access databases?

Apparently, I would have to use a 2000-2003 database to implement security, but then I would lose all of the features of Access 2016.

So my question is, is there a way to implement users and security within the database in an Access 2016 database?


4 Answers: 

The user security in Access was NOT designed to prevent users from modify the application. The user security was designed to say what users can open a report, or say a given form. You could use this security system to ALSO prevent users from messing with the application, but that not really the concept or intent of the security system.

So you never really did need (or want) to use the long deprecated security system as ALSO something that prevents users from messing with your application. So keep these concepts separate.

If you want to lock up the application and prevent users from messing around? Well, first up, we assume your database is split. At that point you will distribute a compiled “executable” of your application to each workstation.

The compiled version means that code, forms, reports cannot be changed by users. And if your application provides some “navigation” type of form to launch + use the given report or form, then your application quite much can hide the access parts you don’t want users to see.

So for example, in this screen shot, I provided custom menu bar, and users cannot see or “go behind” the scene to mess with the application part. This “act” of locking down the application has ZERO to do with the older security system, and that security system as noted not required, or in fact even recommended for the simple task of locking down the application.

enter image description here

So as a starting point, make sure you always distribute a compiled version of your application (an accDE in place of an accDB). How fancy you get with your application is up to you. The above screen shot was access 2003, but here is a screen cap of a 2010 application – and in this case I even provided a custom ribbon. Users cannot mess or see any other parts of the application.

enter image description here

User level security was a system to allow some users to launch a particular form or report, and other users to not launch such forms or reports. The security system was not for locking down the application and preventing users from modifying forms, reports and code. While the security system also could be used to prevent users from modifying reports, if you wanted to keep users out from messing around then you had to still use the compiled accDE, and you still had to spend time hiding the access interface. (so the security system really did not save you any time and effort in this regards).

 

Not sure how Access-savvy your users will be - some people can figure out ways around certain methods if they're good enough with a computer.

Anyway, typically your Access database would be split into a front-end (forms) and a back-end (tables). Have your front-end point to your back-end tables and hide them in your front-end. This is important because Users in the front-end can't alter the design of tables in the back-end.

You can also create your own "Login" form with a table of Users and Permissions, if you wanted.

See 10 tips for securing a Microsoft Access Database

 

This is to some extend addressed by Peter's Software: LASsie.

However, for full security you will need an SQL Server as backend. The Express Edition is free to install and use.

 

okay - so the very old User Security within Access has long been deprecated - and for a good reason. You definitely do not want to consider attempting to go backwards to that.

is there a way to implement users and security within the database in an Access 2016 database? - yes - that's what application designers do. There must first of course be Identity Awareness of who the user is - either by calling in Active Directly name or creating a log in.

After that it gets both creative and complex. To restrict the data they see - then there must an identity field in the table records by which to manage via query. To restrict which form/report objects that can use requires logic in User Interface as to visibility / functionality.

Is there some magic bullet embedded feature that does this? no... it is all design & code work