Questions on security

[nisa]

Hi there...

I've run into a problem with my db When I'm trying to create users for it, apparently Access won't accept them when they try to log in from different computers.

Is it a general problem with Access, or is it just me who's a noob with this?

 

[odin1701]

Hmm...I never messed with users in access.

What I do for access is have a users table with names, and windows login ID's.

Only the windows login ID's in that table will be able to open the database, and I can assign certain user rights levels in there as well so some people only have access to certain things. If they have no access they get a dialog and the db closes.

This way they can login from any system, as long as they use their own windows login name.

 

[nisa]

Hmm...I never messed with users in access.

What I do for access is have a users table with names, and windows login ID's.

Only the windows login ID's in that table will be able to open the database, and I can assign certain user rights levels in there as well so some people only have access to certain things. If they have no access they get a dialog and the db closes.

This way they can login from any system, as long as they use their own windows login name.

That sounds as what I want to do, but the question is how to do it?

This is my first time ever working with Access, so I don't know how to do half the stuff I need to

 

[odin1701]

That sounds as what I want to do, but the question is how to do it?

This is my first time ever working with Access, so I don't know how to do half the stuff I need to

Run this in some VBA code:

MsgBox Environ("UserName"), vbOKOnly

That will show your current username. Now you should be able to use that Environ function and get the username to compare it with a table of users which contains the username as well as access levels.

So basically if the username doesn't exist, close the database.

If it does exist, what user level (say 1-5) and depending on that user level what I do is enable/disable menus on a custom menu bar. So access level 1 gets access to Data Entry menu. Access level 2 gets Data Entry, Review, Reports. Access level 5 gets Data Entry, Review, Reports, Management, Tech Support, etc.

 

[nisa]

Run this in some VBA code:

MsgBox Environ("UserName"), vbOKOnly

That will show your current username. Now you should be able to use that Environ function and get the username to compare it with a table of users which contains the username as well as access levels.

So basically if the username doesn't exist, close the database.

If it does exist, what user level (say 1-5) and depending on that user level what I do is enable/disable menus on a custom menu bar. So access level 1 gets access to Data Entry menu. Access level 2 gets Data Entry, Review, Reports. Access level 5 gets Data Entry, Review, Reports, Management, Tech Support, etc.

I'm not quite sure I understand I'm afraid...

I've created a form now named userlogins, and set the properties etc. but how do I make it work in pratice?

 

[KeithG]

Your db is not truely secure that way. The safest way to secure your db is with workgroup suecurtiy but it can be a pain in the butt. For example if you hold down shift when opening your db it will bypass all start up option and open to the database window.

 

[odin1701]

I'm not quite sure I understand I'm afraid...

I've created a form now named userlogins, and set the properties etc. but how do I make it work in pratice?

You need a table, not a form.

Here is what I would do in the table:

ID User Access Level
1 user1 1
2 user2 4


ID can just be auto number and a key, but you don't really even need that necessarily.

You just need to compare the user trying to open the database with the users table. If it doesn't exist, you can pop up a message box and then user the command:

Application.Quit acQuitSaveNone

or DoCmd.Quit

to close the database.

This will need to be done using something that runs on startup, like an autoexec macro which runs startup code.

If the username is valid, you can continue to check the access level and load certain menus.

You will need to create your own menu bar and add menus to it in order to tell it which ones to enable/disable.

In the Tools > Startup options you will need to select your custom menu bar and disable allow full menus, as well as disable allowing built-in toolbars and toolbar/menu changes. Select a form to startup, disable the database window and disable access special keys (you will need a utility to re-enable that, or a menu function to re-enable that so that you can get back into your own database to edit it.)

There is really just a ton of stuff to go over so I can't really explain everything. Try doing some google searching on these topics.

 

[odin1701]

Your db is not truely secure that way. The safest way to secure your db is with workgroup suecurtiy but it can be a pain in the butt. For example if you hold down shift when opening your db it will bypass all start up option and open to the database window.

Not if you disable the access special keys, and deploy an MDE file.

You will ideally have a front end and backend. Backend with data, front end with code. The front end will link to the backend with a password in the VBA.

This is the ideal method as the front end can get corrupted and you won't lose data in your backend, which you should maintain.

 

[The_Doc_Man]

Simple problem, based on this symptom:

apparently Access won't accept them when they try to log in from different computers.

I'm going to infer that you actually did the setup correctly with the Access workgroup file and that you really did change around passwords and such. Here is the "missing link" for you.

The last workgroup you joined on a particular computer is a REGISTRY item, not stored in Access or in the MDB file. Each computer has its own different registry. Unique per-machine. So if you change computers, you have to rejoin the workgroup.

You have two ways to handle this:

1. Education

2. Build an ICON for the MDB file as a shortcut, then get into the icon via right-click to edit the command line.

To find out what to put in that command line, look up Access Help on 'command line' options - which will include option -wkgrp your-workgroup-name-goes-here

If they click the ICON to open the file, it will automagically join the right workgroup before attempting to open the MDB. They can copy this icon as long as it points to the MDB on a shared drive. And as long as Access is installed the same way on every computer. (If this is not true, come back for more thoughts on the subject.)