Securing Tables From Data Changes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an app that must log all changes to data (who, when, what). The
problem is securing the tables so someone with Access know-how can't use the
shift key (bypass start-up options) to get to the tables and change data
without a log entry being created. Access 2000 front end linked to SQL 7 back
end. MDE will only secure the forms and reports. Thought of making the tables
hidden but again, someone with access knowledge will know how to turn them
back on. Is there a way to generate a log entry when the table is directly
accessed? Any other suggestions??
 
One way is to implement user-level security and take away the user's access
to the tables. This will mean they can't open or modify the tables in any
way. Also, take away all access to the queries. Then, give them the needed
access to the various forms they should be using.

Since the forms are based on queries that they don't have access to, you
would get an error UNLESS, you set the queries to "run with owner
permission". Then, when they open the form, they will be able to use the
queries and perform the needed tasks.

The Microsoft Security white pages has more details on this, if I remember
correctly.
 
Rick B said:
One way is to implement user-level security and take away the user's
access
to the tables. This will mean they can't open or modify the tables in any
way. Also, take away all access to the queries. Then, give them the
needed
access to the various forms they should be using.

Since the forms are based on queries that they don't have access to, you
would get an error UNLESS, you set the queries to "run with owner
permission". Then, when they open the form, they will be able to use the
queries and perform the needed tasks.
Click to expand...

Not quite. They'll still need at least read data permission on the RWOP
query in order to be able to read data. And it they need to
add/modify/delete records in the form, then they'll need the appropriate
permission(s) on the query.
 
Sorry, 'RWOP'??. I don't know much about user-level security. We've never had
anyone who either knew how, or was inclined to by-pass, the start-up option
of hiding the database window. Could you please be a little more specific?
I'm willing to read knowledge base articles but can't seem to find the
appropriate one(s). Thanks for the help.
 
jkmccnh said:
Sorry, 'RWOP'??. I don't know much about user-level security. We've never
had
anyone who either knew how, or was inclined to by-pass, the start-up
option
of hiding the database window. Could you please be a little more specific?
I'm willing to read knowledge base articles but can't seem to find the
appropriate one(s). Thanks for the help.
Click to expand...


RWOP stands for 'run with owner permissions'. It means that you can remove
all permissions on the tables for your users. If you set the 'Run
Permissions' property to Owner rather than User, that means that anyone who
runs the query, will be able to do so as though they had the query owner's
permissions on the table.

So as long as the query owner has permissions to the underlying tables,
users will be able to access the data (because when they run the query, they
are running it 'with the query owner's permissions'); in other words, as
though the query owner was running it.

That doesn't mean that they will have full permissions to do anything
though. They are still restricted based on the permissions that the user
has on the query. So the owner may have full permissions on the tables, but
if the user has read data permission on the query, they'll only be able to
read data when they run the query.

You set the Run Permissions property in design view of the query (View,
properties menu).
 
Thanks, I'll read up and give it a try. Appreciate you taking the time to
explain this to me.
 
Joan,
I've been testing out user-security but it doesn't seem to address the
problem of someone using tables/queries to change data thus by-passing the
logging that occurs with the form use. The use of queries with RWOP, and
removing permissions from the tables stops them using the tables directly but
it opens up the same data change opportunity thru the query (since they have
to have full query permissions in order for the form to work). Am I missing
something? I have the query set to use my (owner's) permission, my
permissions give me full access to the tables... but I have to also set
read/edit/add/delete permissions to the workgroups query permissions? What's
ther purpose of using owner's permission then? I also tried saving the form's
data source as a SQL statement but got the "you don't have permission..."
message when I tried to run the form. I must not be understanding something
correctly...can you help?
 
Hi jkmccnh,

jkmccnh said:
Joan,
I've been testing out user-security but it doesn't seem to address the
problem of someone using tables/queries to change data thus by-passing the
logging that occurs with the form use.
Click to expand...

Users should not be using the queries directly. All data interaction should
be via the forms/reports.
I have the query set to use my (owner's) permission, my
permissions give me full access to the tables... but I have to also set
read/edit/add/delete permissions to the workgroups query permissions?
Click to expand...

You would only need to set these permissions if users need to be able to
edit/add/delete records. In other words, if all they're doing is reading
the data via the form, then they only need read permissions. But you are
correct, that if they need to delete records via the form, then they need
delete permissions on the record source (the RWOP query).
What's
ther purpose of using owner's permission then?
Click to expand...

As I said, users shouldn't even have access to the queries tab. You should
lock down your application. Some things to consider:

Backup your database; you can easily lock yourself out playing around with
these features.

Create custom menus/toolbars for use throughout your application.
Create a startup form (a main menu form if you have one) that is opened on
startup.
Use the features in Tools, Startup to
set the startup form
set your default menu (the custom one you made)
disable all the checkboxes about allowing built in menus, toolbars,
changes etc.
hide the db window (ensure the custom menu you create does not
include the Windows, Unhide item)
Click on the Advanced button and uncheck the allow special keys
(this will disable the F11 key, among others)

If you need to bypass these startup features, you can hold the shift key
down while you open the db. If you feel that your users may use this to
bypass your settings, you can disable the shift key bypass - there's an
example in help for doing this(look for AllowBypassKey) or at
http://www.mvps.org/access/modules/mdl0011.htm
and
http://www.mvps.org/access/general/gen0040.htm

You can also create a MDE from your database, which will prevent changes to
forms, reports and modules (If you do this, be certain to keep your original
mdb in case you need to make changes).
I also tried saving the form's
data source as a SQL statement but got the "you don't have permission..."
message when I tried to run the form. I must not be understanding
something
correctly...can you help?
Click to expand...

RWOP can only be used on saved queries. When you attempt this with a SQL
statement as the recordsource, at runtime a temporary query is created.
Since it's the user that creates this query, they don't have permissions on
the underlying tables. They are the creator/owner of this 'query', so RWOP
is pointless.
 
Joan, thanks for all your patience. Disabling the shift key bypass seems to
be the missing data point for me so I'll do some more searching and reading.
I had already implemented custom start up options/forms/menus/toolbars, and
had hidden the database window...unfortunately a troublesome employee knows
how to use the shift key bypass in order to gain access to data directly
through tables/queries.

Numerous posts on disabling the shift key bypass mention the auto exec
macro...is this something that can be used to test a person's "rights" for
enabling/disabling? Or is it too late by the time that runs?
 
jkmccnh said:
Numerous posts on disabling the shift key bypass mention the auto exec
macro...is this something that can be used to test a person's "rights" for
enabling/disabling? Or is it too late by the time that runs?
Click to expand...


Disabling the shiftkey will only apply the *next* time the mdb is opened, so
the autoexec won't really help you here.

There is code at
http://www.mvps.org/access/modules/mdl0011.htm
and
http://www.mvps.org/access/general/gen0040.htm

you can use to disable it. If you're having trouble understanding how to
implement, see this message at Google.
http://groups-beta.google.com/group....setupconfig/msg/dc95f1c4e74a2698?hl=en--Joan WildMicrosoft Access MVP
 
Back
Top