security by filter?

  • Thread starter Thread starter Tom Stoddard
  • Start date Start date
T

Tom Stoddard

I would like to prevent users from being able to edit or delete records for
which they are not responsible. I'm not sure if access security is the way
to do that but it seem logical to start here.

I've used replication in the past to create partial replicas so that remote
users only get data that is related to accounts for which they are
responsible. I've read about how to set up security on an access database
but I've never done it because I haven't had the need to until now. From
what I've read so far, there doesn't seem to be any way to accomplish what I
need to do through access security alone.

I'm guessing that I will need to set up security so that users will have to
logon and thereby identify themselves. I suppose I'll have to rewrite many
of my queries to filter records based on which user is logged in. This seems
to be a lot of work. Is there an easier way?
 
If you don't want to use Access user-level security, the other way
would be to roll a simple scheme yourslf. Have the user identify
themself (somehow) when they start the db, then use VBA code within
each form to determine what they can or can not do to the current
record (edit, delete, whatever).

The advantage of this method is that you don't have to learn Access
user-level security - a significant undertaking. The disadvantages are
that:

(1) Anyone could go straight into the database & edit the data directly
(bypassing all your forms completely), and

(2) You'd be coding things which are easier done - once you know how to
do it - in Access security. For example, Access security can easily
force the person to log on with a valid username/ password, & you can
easily determine the username of the currently logged-on user, from
anywhere in your code or in queries, via the CurrentUser() function. In
a "roll your own" scheme, you'd have to code all that yourself.

Personally, I'd incline to (1). But it /is/ a significant challenge to
learn it initially.

HTH,
TC
 
Personally, I'd incline to (1). But it /is/ a significant challenge to
learn it initially.
Click to expand...

I don't mind learning access user-level security but I don't see how it can
enable be to restrict access to only selected records within a given table.
Am I missing something?

I anticipate having to use a combination of user-level security and coding
to accomplish this. Is there a way to do it without code?
 
You need to remove the delete permission from the whole
table, then provide a RWOP (run with owner permission)
query to do deletes.

Give delete permission to the Owner of the query.
Look in the query property sheet for the RWOP property.

The query can select only records for which the user is
responsible, using the Access CurrentUser or getting the
Windows User from an API function.

(david)
 
Back
Top