MS Access 97

You can use the Access security by opening your database then clicking on Tools, then Security. Before doing this be sure that you make a copy of the System.MDW file (which is located within the Windows/System directory).

To use this security your users must be connected to the same .MDW file as the one you used to set up security for the database. You can rename the .MDW file with your application name and save it on the server. Start up the application with the switch to use the appropriate MDW file.

Access security is not foolproof and can be a hassle to use, but it does work. Be sure to at least set a password for the (default) Admin user, otherwise someone can log in as Admin user with no password and have access to everything. Access automatically has every new database set up with the Admin user and no password and with rights to everything (the Admins group). Attach the Admin user to a group with almost no rights for further safety (or even eliminate the Admin user).

Use groups to control access to different areas for your different users, since it is likely that more than one user should have the same rights to specific tables, forms, reports, etc.

Note that you cannot control users' access to specific columns in tables.

Rather than use the Access security method I like to build my own login screen using the user list from a hidden table in the database. When the user logs in I save the userid and the groupid. I can then control the ability tochange or even view specific columns in a table (controls on a form/report) through the code in back of the forms.

Whatever method you use for security, be sure that your users only have access to an .MDE version of your Access application, not the .MDB file. They cannot see or modify the VBA code behind your forms, reports, etc., in the .MDE version.

In addition to Bob Sellmann's answer, I would like to offer the following:

Removing or limiting permmissions from the admin account is a must. But doesn't help alot unless you go one step further - change the ownership of all objects in the db.
You need to take this extra step in order to offer a good security level. The ownership of the objects also belong by default to admin. Changing permissions for the admin will not change the ownership. So you need to change the ownership as well - ifyou do not, anyone can still get into the db and get permissions if the workgroup is changed to the default, even if admin permmission are revoked - the owner ALWAYS has full permmissions. Therefore, I can import the objects in a apparent secured MDB and get full permmisions for those objects. And a basic database password will not help prevent this (easy to crack).

And once ownership is changed to an account of your own (use a different unique name for ownership - not using the same name for any other accounts in the mdw shipped with the mde/mdb), use a different workgroup for the rest and do not ship the workgroup that has the owner info along with the mdb/mde. Keep your own account with full permissions, not allowing the users to have access to this account.

User-level security sets security for objects or resources within an Access database or MDE file by establishing a level of access for groups or individual users. User names and passwords are authenticated against a database of user and group account information in an Access workgroup information file or system database.

Setting User-Level Security and How user-level security works:

When a user opens a secured database, Access determines the user’s level of access to an object (including the database itself) by checking the set of permissions assigned to that user for that object. Different users and groups can have different permissions for the same objects. When a user tries to perform an action on an object — such as opening a form, running a query, or modifying the data in a table — Access checks to see whether the user, or any of the groups to which the user belongs, has the necessary permissions to carry out the operation.

Storing workgroup and permission information
Access stores information about users and groups in a database called a workgroup information file, commonly referred to as the system database. A workgroup information file stores the following information:

Name of each user andgroup

List of users that make up each group

Encrypted logon password of each user

Security identifier (SID) of each user and group

See this link for the full instructions and explanation:

http://www.microsoft.com/office/ork/2000/three/65t3_6.htm

----------

Another possibly useful bit of information:

Restricting User Access with Startup Options

http://www.microsoft.com/office/ork/2000/three/65t3_2.htm

(REMOVE SPACES from the pasted URLs.)

Maxwell