Creating Ms Access System tables

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
Hi All Experts

We are all aware that the MS Access is not all good where security is concerned, now for those who have created their own login forms for users, some will agree with me that we normally use a table with some basics columns in it as follows:

(1) PK
(2) User Name
(3) Password
(4) User rights

Normally this table is found in the BE .

My question is that , what is the danger  if we make this table as a System table and move it to the FE since we are able to lock navigation pane, cant this table much safer there? We are able to protect linked tables , queries and macros by locking the navigation pane , couldn't it be an opportunity to do the same with this login table?????


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
If you hide the table by naming it USysYourTable, it can be found if the user has the knowledge and an Access full install (not runtime) no matter where the table is located.

Further, you should never store the password itself, only an encryptes hash of it.
Lets start by making a subtle but important point. A JET(mdb) or ACCDB(ACE) database is very difficult to secure. Access can connect to many backends, one of them (the default for simple databases) is the JET/ACE backend.

This is really a feature of the DATABASE, and not of Access. If you connect Access to e.g. SQL server as a backend, you get the full range of security that an advanced SQL server solution can offer.  Sadly most people believe that Access=JET/ACE which is just not true.

With that out of the way, a good reason NOT to put the user table in the frontend, is that you would need to create a new frontend to add a new user. If people have taken a copy of your frontend, then you can't "block" them by use of your login form, because their local table will still have their user name.

What is it you are hoping to GAIN by placing the table in the frontend?
To add on to Gustavs password comment, consider whether you truly need a password. E.g. if people are using your app from their own PC, you could get their username from the PC. I.e. if Anders is logged on to the PC, and Anders is in the user table, then he has access to use the App. That way we don't need the whole hazzle of storing a password (and resetting it when users forget).

I only truly see the need for a password if multiple people are sharing the same PC with the same login.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Bharat BhushanSolution Manager

Hmm, well not in my eyes and it removes control over that table which is used for more than just log-in AND the advanced Access user could still get to it there since there is no way to truely lock the navigation pane.
You have made my day I cannot manage my happiness any more , who said the navigation pane can never be locked , please just read my article on protecting macros , link tables and queries you will learn how do to it for  free.


Bharat BhushanSolution Manager

Thanks @Hankwembo Christopher

Yep, seen that code before and there is STILL a way to get to the navigation pane.  While the average user may not have access to the code or know how to use it if they do... and the advanced user will be able to unlock it. An advanced user will be able to if they want to.

The question comes down to what are you trying to protect.  If it's the code, you can put a password on that and no one will EVER see it without the password.  
The passwords? Then use SQL Server or Active Directory.
The objects?  Well that makes no sense because anyone can figure that out from looking at the forms
The data? It belongs to the company so if they are trying to prevent employee stealing their data they have another problem and they might want to deal with that because nothing can prevent an employee form simply using pencil and paper.
After locking the database with the lock you have seen just compile the database in accde that is it .
Next concern ?

The question comes down to what are you trying to protect.

I'm sure you are aware that normally the queries, macros and link tables are not protected by ACCDE , that is what need protection.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial