Creating Ms Access System tables

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?????


Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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?
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Bharat BhushanSolution ManagerCommented:
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.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
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 ManagerCommented:
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.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
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.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.