Lock users out of the table

[Rick]

I have about fifteen users on my database. Each has their own front end MDB
file. I have many edits built in to my forms to prevent users from entering
invalid data. Two of my users have discovered that they can simply open the
table and bypass my edits. I have tried locing them out of the table, but
then when they open the form they get an error indicating that they don't
have permission to read data from the table.

Joan Wild mentioned in an earlier post...

If you want to implement user security, you can create queries set to run
with owner permissions. Give the users permission to use the queries, and
you can remove all permissions from the table.

I guess I don't understand the phrase, "set to run with owner permissions".

Can anyone tell me what I am doing wrong?

Thanks!

Rick Bear

 

[Joan Wild]

Hi Rick. Your forms have a record source - either a query or a table. If
it's a table then create a query based on the table and then...

If it is a query, then open that query in design view. Right-click an empty
area of the upper pane and choose properties. This will open the properties
dialog where you will see a property named Run Permissions - change this
option to Owner.

Remove all permissions to the users for the table(s) and give them whatever
permissions you want on this query.

 

[Rick]

Joan:

Thanks! Now does that mean that they would have the same permissions as the
person listed as the query's owner. In other words, I am the owner and I
can add, change, and delete data. The users currently can't delete because
they don't have that access to the table. Would I need to take away delete
from me on this particular query and then do my deletes i a different form?

Rick

 

[Guest]

it means they can use a table referenced in the query that they don't have permission on. but here is another problem. now they only have to open the query and avoid the restrictions set in the form. here is a way around that. in the source query, put in a field like this qryLock:[Forms]![frmName]![txtLock]. Then put a number in the criteria row for this field. Now go to the form. As you can see I called it frmName. Somewhere on the form place a text box and for the data source put = "then put the number here that you placed in the criteria of the query". Make sure the text box is invisible. Now, when the form calls the datasource query the hidden text box on the form supplies the unknown parameter to the query. For someone to use the query with out the form opened, they would have to guess the number to populate the query with data. Of course, if the form is opened, then the query will poplulate. So to fix that, remove the database window on startup using code so the shift key will not bypass that feature. hope this helps

 

[Joan Wild]

No, it doesn't mean that. They run the query with the query owner's
permissions (that is the permissions that the owner has on the underlying
tables). However, they still need the necessary permissions on the query
itself. In other words, even though you as owner can insert, update, and
delete records in the tables, giving the users a RWOP query does not
automatically give them the ability to insert, update, and delete records
when they run the query. The only way they could do that, would be if they
were given insert, update, and delete permissions on the *query*.

When they run the query, it retrieves records from the table based on the
table permissions that the query owner has. What the user can do while
running the query depends on their permissions on the query. Imagine if the
owner of the query had only Read Data on the table, and users were given
Read/Update on
the query, they'll still only have Read Data (because they are being
restricted by the query owner's
permission on the table).