Password protection (win XP Of 2003)

Topic

New Reply

I need some help is setting up password protection for an application. I need to control exclusive entry to the billing operation by one of two or three authorized cashiers. Also, only the manager should be able to delete, add or change authorized cashiers. No cashier should be able to see or modify any password data of the others.

In digging through past posts, I find a lot of disscussions but, unfortuately, the attachments are not available. In particular, I was looking at posts 506825 and 532989. Any chance of re-freshing those?

We are currently in Access 2003 but will be moving to 2007 in the near future. I understand that the 2003 security is not available in 2007, so would prefer to prepare something portable to 2007.

Reply | Quote

Replies

I don’t have the attachments from those posts any more. Perhaps Support4John still has the one from post 532,989.

Reply | Quote

I don’t doubt it. It has been a long time since Nov 2005.

Could you look at my problem, in the meantime.

Reply | Quote

One way you could do this is as follows:
– Create a backend with tables only. Protect this database with a password so that users can’t open it directly.
– Create different frontends with tables linked to the backend (you will have to provide the password once when linking the tables).
– You could create a frontend with forms in which the user can add, edit and delete data, and another frontend in which the forms are read-only.
– Intermediate roles are possible too.
– Protect the frontends by setting the appropriate options in Tools | Startup…, and set a different password for each frontend.
– Give each user a copy of the frontend corresponding to their role, and the password for it.

Keep in mind that this protection is not perfect – there are password crackers that will unlock the databases. So someone determined to break in will probably be able to do so. It should keep users from casually browsing or editing data they shouldn’t, though.
Another disadvantage of using different frontends is maintenance – if you want to add a form or report, you’ll have to add it to each frontend.

Reply | Quote

Food for thought! Thanks.

I set up a back-end, gave it a PW, closed it, and then opened the front-end. I get a “Not a valid password” error message opening the front-end.

The maintenance is a problem, but I can live with that. Is it possible for one of the cashiers to copy his db, change the password and name, and create an unauthorized user?

Reply | Quote

You will have to relink all the tables in the frontend after you have set a password in the backend.

In this setup, a user could simply give someone else a copy of his/her frontend and the associated password. If a cashier does this, this will give the other person cashier rights. But in a database that has been fully protected by user-level security, the same would be true: a cashier could simply give his/her username and password to someome else.

Reply | Quote

I can’t open the front-end because of the “Invalid password” error, so I can’t re-link to the back-end.

Reply | Quote

Can you bypass the startup options by holding down Shift while opening the database?

Reply | Quote

Yes, that gets me into the db; however, when I open the Linked Tables Manager, I ge an “Application-defined or object-defined error” message. Also, if I open one of the linked tables, I get the “Not a valid password” error again.

Reply | Quote

Try the following:
– Delete all linked tables from the frontend.
– Select File | Get External Data | Link Tables…, navigate to the backend, select the tables, and click OK. You should be prompted for the backend password.

Reply | Quote

OK. That works. I will go with the multiple front-end solution for now.

I worry about the cashier creating another user and doing bad stuff under a
false cashier’s name. All movements are tagged with the current cashier’s
ID, and that can easily be circumvented with the copied db. Am I paranoid?

Thanks again for the help.

Reply | Quote

I often add two fields to a table:

LastModified (Date/Time)
LastModifiedBy (Text)

and fill these fields in the Before Update event of each form that manipulates the table:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.LastModified = Now
Me.LastModifiedBy = Environ(“username”)
End Sub

That way you can see which Windows username last modified a record.

If security is important, I’d either choose for user-level security in Access (which can’t be used in the new .accdb format in Access 2007, but is still supported for databases in Access 2000 or Access 2002/2003 format), or move the backend to a more robust database system such as SQL Server.

Reply | Quote

If they need more, we will go to SQL.

In the meantime, let me figure out the Environ function that you mention. I was thinking of using a hard coded constant for the cashier ID, but I would like to figure out the Environ, anyway.

Reply | Quote

You can look up Environ in the VBA help. It returns the value of the specified environment variable.

Reply | Quote

Is this the one?

Reply | Quote

I don’t think so, but hopefully it gives the OP some ideas.

Reply | Quote