User permissions dropping out

[Barry]

I have done everything perceivably correct to set up security on a database,
and am testing a "Read Only" user test subject. I must set the permissions
to 'modify table design' for a particular table that is updated when the db
is opened, thru the 'Autoexec' macro (run on an update-table query). Logged
in as Administrator, I set the permission, then exit and login as the Read
Only user OK, until after logging in again, following logging in as
Administrator again. It seems to drop this permission for some reason. I
have the db and workgroup file located in a folder on my desktop. I don't
imagine it is due to IT permissions to the C: drive, since I can create the
files in the first place on my desktop.

 

[Barry]

I believe it is due to the Autoexec macro trying to run before, or at the
same time, that the login is occurring. I have tried going about running VBA
code to delete and run the query in the "On Open" main switchboard form
event, but get an error "Run-time error '3211'; The database engine could not
lock table 'Items'
becuase it is already in use by another person or process".

 

[Barry]

This is what I have for VBA event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Close acTable, "tblWell_ID"
DoCmd.OpenQuery "qryWell_ID"
End Sub

 

[Barry]

Now I am thinking the problem lies in a VBA module that is fired from the
"Autoexec" macro to run an 'update-table query'. I guess I will need to set
permission in the script to allow it to be run by anyone. Can someone help
me out with doing this? Basically, is it possible to set permissions in a
VBA script to run it with any login?

 

[Barry]

OK, I figured out the security problem...what I had to do is set all
permissions, but 'Administer' for the table to be updated each time the db is
opened, for the read-only user. In addition, had to set the same permissions
for <New Tables/Queries> and a linked table from which the make-table query
pulls data from. I think I will be able to adjust the permission settings
more once the db is split.

 

[Joan Wild]

Back up a bit. I don't see why the user needs 'modify table design' to
run an update table query. An Update Query updates *data* in the table,
not the design of a table. So therefore the user needs only update
permission on the table. You could achieve this by removing all
permissions on the tables for the Read-only user and making the Update
Query 'RWOP' (a setting in the properties of the query. This means Run
With Owner Permissions, and will allow the user to update the table.
You then just give them udpate permission on this saved RWOP query.

More at www.jmwild.com/RWOP.htm



Joan Wild
MS Access MVP

 

[Barry]

Joan, I have tried that as well. I guess the permissions needed are due to
the way I have the make-table query set up to run:

When the db is opened, user must login.
The "Autoexec" macro runs:
a splash screen;
RunCode event to run the make-table query;
Opens the Switchboard Form.

Since the last post, I tried splitting the db, which I think will work best.
That way I set read/write/modify permissions for the front end, and set the
restrictions in the backend. This prevents runtime errors, while still
restricts writing to specific objects.

Thanks.

 

[Joan Wild]

Now you're talking about a 'make table' query. That is not the same as
an update query.

Instead of having the user create a table each time, just create the
table needed and run two queries on startup (a delete query to empty the
table, and an append query to add the new records). This way you don't
destroy the table (and its permissions) each time. These two queries
can be RWOP queries so the user doesn't need permissions on the
underlying tables.



Joan Wild
MS Access MVP