Group Level Security

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I have searched the forum before posting this message. I
have even searched other forums also. I am not sure if I
used the right keyword to search for this topic.

I apologized for posting a long message.

We have split MS Access application. A user wants to
implement security that a user from one group cannot
UPDATE or DELETE the record created by a user from another
group in the department, but can view a record created by
another group.

I don't think this can be implemented using MS Access
security without creating additional tables and code. I
just wanted to make sure that I am correct on my thinking
before proposed a solution which will require making lots
of changes to the application. I had the following
thoughts in mind on how to do this:

1. A table to store dept. Also AutoNumber to create a
dept #.
2. A table to store user name, dept #, effective date and
end date. End date NULL or in future.
3. A form to setup a user or move a user between the
groups.
4. Add a column to store the dept # of the user name who
created the record in each table or tables which stores
the data to be protected.
5. Add function to open the form in Read only mode or Edit
Mode depends on who created the record and who is trying
to retrieve the record.

Thanks

Jay
 
You just need to include, in each record, a field to say who created that
record (or which department they belong to - whatever). You do this by
defining a field for that purpose, then populating that field from the
Form_BeforeUpdate event of your form(s).

Then have code in Form_Current, to compare that field to the current user
(or current user's group - whatever). Then lock (Lock property) or unlock
each control, depending on whether the current user should or should not be
able to edit that record.

A similar technique using other events would stop the user deleting records
he shouldn't delete.

The only problem is, determininging *who* the current user is. If you
creates some users, & put a password on the Admin user, this will require
evebnrone to log in with a valid username & password. Then you can get the
currebnt user's username, from the CurrentUser() function. If you do *not*
requirethe users to log on when then start the database, then, you will have
to work out some other way of determining who the current user is.

All of this is well within the ability of Access, without creating extra
tables; but sure, you will need extra code.

HTH,
TC
 
Thanks for the information. See my comments/questions.
-----Original Message-----
You just need to include, in each record, a field to say who created that
record (or which department they belong to - whatever). You do this by
defining a field for that purpose, then populating that field from the
Form_BeforeUpdate event of your form(s).

This is fine.
Then have code in Form_Current, to compare that field to the current user
(or current user's group - whatever). Then lock (Lock property) or unlock
each control, depending on whether the current user should or should not be
able to edit that record.

A similar technique using other events would stop the user deleting records
he shouldn't delete.

This is fine.
The only problem is, determininging *who* the current user is. If you
creates some users, & put a password on the Admin user, this will require
evebnrone to log in with a valid username & password. Then you can get the
currebnt user's username, from the CurrentUser() function. If you do *not*
requirethe users to log on when then start the database, then, you will have
to work out some other way of determining who the current
user is.

I thought this is where I would need extra tables. The
CurrentUser() will only give me the name of the user, but
not the Group name they belong to.

Is there anyway to get the Group name for the current
user? I read about ADOX and I can get that informaion
from User object, but I don't know how to get current user
object using ADOX.
 
Answers below.

Jay said:
Thanks for the information. See my comments/questions.


This is fine.


This is fine.

user is.

I thought this is where I would need extra tables. The
CurrentUser() will only give me the name of the user, but
not the Group name they belong to.

Is there anyway to get the Group name for the current
user? I read about ADOX and I can get that informaion
from User object, but I don't know how to get current user
object using ADOX.
(snip)

Access "user level security" has the concept of users & groups. A user can
be a member of any # of groups. Each group can have any # of users. This is
all under your control.

If you set up some users & groups, & give the Admin user a password, then, e
ach person will be required to log-on with a valid username & password when
they run the db. At that point:
- the CurrentUser() function gives the name of the currently logged-on user,
and
- you can use VBA code to determine the group or groups to which that user
belongs.

So, I'm sure that uyou could do what you need, without the need for extra
tables.

The only downside is, that user-level security is really quire a complex
beats. It would not be so bad here, because all you want is the bits
pertaining to defining new users & groups, & making all users log on wiuth a
valid username & password. You do not need all the other bits, such as,
establishing detailed permissions to the XYZ table for user 111, 222 & 444
(you get what I mean).

Alternatively, you could certainly store the user & group names in your own
tables, & use those (not user-level security). Normally, I would advise
*against* that. However, in this case, you do not need the extended
functions of user-level security, so I feel that the extra tables would be a
fair way to go - if you don't want to mess with the other technique.

HTH,
TC
 
Back
Top