1

How can I implement security accounts in an Employee Database in Microsoft Access 2010 which allows for different users to login with different permission sets. e.g:-

I have a database which has Managers, Supervisors, and clerks or assistants, and only 1 full administrator.

I have already set a table: tblAccount, tblEmployee

**EmployeeIDP**
Name
Surname
StartedOn
FinishedOn
EmployeedType - Look up:- Admin/Manager/Supervisor/Clerk/Temporary

**AccountIDP**
Username
Password
**EmployeeIDF**
LastLogin
Permissions  - look up:- Full Access/Manage accounts/Read accounts only/No access

Without trying to give you the whole specification of the database how can I prevent certain users from accessing the accounts table or form?

I have already implemented a login form and each user is logged using VBA but this was easier than trying to implement permissions to tables/fields etc...

I want to prevent clerks from seeing other accounts, and prevent supervisors from adding/deleting, Managers can add new users an do anything other than delete tables or change the structure of the database.

Obviously the administrator can do anything.

Is this even possibly without advanced VBA.

user2054388
  • 105
  • 2
  • 13
  • Could be mistake but the most you can do in Access is control what records are visible based on who logged in a form using queries/filters. You cannot prevent users from directly viewing/editing the table. – ashareef Aug 12 '14 at 14:58
  • 1
    It looks like it is time to move to SQL Server Express for the back-end. It will be quite a lot of work, but easier than trying to use an MDB for the back. MDBs allow fancier security. – Fionnuala Aug 12 '14 at 16:13
  • @Fionnuala I would but can you use Server Express commercially for free or does this require licensing fees?, I have explored this option but quite frankly I was in the deep end trying to understand the model specification that Microsoft use. I just wanted to keep things simple really. Access interface is a must though. – user2054388 Aug 12 '14 at 16:51
  • 1
    Apparently you can http://stackoverflow.com/questions/2011716/can-i-use-free-sqlserver-express-in-commercial-app – Fionnuala Aug 12 '14 at 17:21

3 Answers3

0

Depends on the access you give the users. You said you already have a login form that tracks access.
Are all your forms controlled from a startup dashboard page?
Are the users viewing only authorized buttons/links on the dashboard based on their log in?
You could hide all objects (queries, tables forms, reports), give access only through the dashboard, create a .mde or a accde front end where you distribute an encrypted frontend to users. You can then control who can open which data sets, through the forms. This won’t stop a determined programmer but for your usual users, it will work as you get a more robust system.

Avagut
  • 924
  • 3
  • 18
  • 34
  • I am an Access fan, but I still would hesitate to take this route unless it was quite unimportant that a junior user could look up a senior users data. – Fionnuala Aug 12 '14 at 17:24
  • It actually works quite well but it requires more setup work for the vba login management and db structure. Users, user groups and session variables. Got loads of help when I was setting up a system here [link](http://stackoverflow.com/questions/20774816/access-control-using-user-rights-groups) – Avagut Aug 12 '14 at 17:35
  • The trouble is that it is very easy to work around, especially in the newer versions of MS Access. – Fionnuala Aug 12 '14 at 17:37
  • Well, that is possible. As I said, it would be a stop gap til user2054388 gets onto SQL Server. And even with SQL Server he will still need to manage access to the various tables through MS Access.... – Avagut Aug 12 '14 at 17:47
  • With suitable roles principals and permissions on SQL Server, it gets a lot easier. Do not get me wrong, if security is not the main concern, Access is the answer for me. – Fionnuala Aug 12 '14 at 17:56
0

I have a hidden form that opens after user logs on. The form has field that can be referenced by any process.

DoCmd.OpenForm "frm_global_variables", acNormal, "", "", , acHidden
Kaw4Life
  • 209
  • 2
  • 17
0

I answered this in past how to implement user level security in MS Access 2007 There was once workgroup security feature from Microsoft, but it is no more there, was discontinued. But now we will have build our logic & coding for the roles & security feature.

I created a privilege table along with log-in table. Each screen in the database will have Read-only or Read-write privilege to each user. I inserted all the screen names into privilege table. Another table UserPrivilege will have users and their privileges. Assigning privilege to an user will be done only by Admin user.

A function at start of each form check swhether a specified user is allowed to view or edit form. If he/she is given read-only, we will lock all controls looping thr' controls on the form. Else, nothing to do. OR keep all the controls read-only at design them and unlock them thr' code for write privilege.

The database window is kept hidden when a version to end user is delivered. This prevents usual , simple view to tables in the database, opening forms , reports object in database window. After making mde/accde few more tweaks can be done so that user is not easily able to view tables directly. by-passing startup, special keys etc.

Community
  • 1
  • 1
Sham Yemul
  • 463
  • 7
  • 30