Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Unanswered: Concept reality check users/groups

    OK, conceptually this seems pretty easy, but I want to get a little bit of a reality check, or if someone has some pointers on a better way to do this:

    I am thinking of building a password/vendor manager database if you will to store the passwords and info for our various vendors, web sites etc. That part is easy, but, as long as I am doing this, I think it would be smart to provide some level of security - limiting access to records to "authorized user's."

    I have a few related questions:
    1. If I use access's security, is there a way to pull up the username, ID or some other way to identify the user?
    2. I want to have a table of user's/groups where I am thinking we have:
    a) Useruid 'the unique user id for this user (I'll probably be using replication id's
    as I'll explain a bit later.
    b) Usertype 'either 1 for a user or 2 for a group (or possibly group yes/no)
    c) Membership 'comma delimited list of useruid's for groups the userbelongs
    to (this will probably be a memo field because I am unsure of the max
    ength needed).
    3. I'll need to have a user setup form displaying only user type of users, and
    I'll need to parse the list of user groups to populate a subform indicating
    membership yes/no. When a selection is changed, I'll need to update the
    membership field, basically rewriting it with all of the "currently selected"
    groups.
    4. Rather than try to replicate the user lists of members for the groups form,
    I think I'll just create a query to find all the users that have the "group" in
    their member list. and similarly update any user's list of memberships
    whenever they are selected or deselected. If newly selected, I'll simply
    append the groupuid (useruid for the group) into the user's membership
    field, if it is contained deselected, I'll use the replace command for the
    useruid to a) replace the group-useruid with "", and then replace ",," with
    ",".
    5. I'll want to use replication because I want laptop users to be able to have
    a copy of this, and if they are forced to change a password, they can
    update their copy and have it update the master database when they
    sync, but that's another question entirely.

    Is this similiar in any way to what others do for user/group memberships, or is there a far better way to accomplish this?

    Thanks in advance for your thoughts.

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    1) CurrentUser will tell you the userid the person is logged into Access with. look up GetNetworkUserName, and it returns what userid the person is logged onto the network with.

    Are you just trying to keep out people who shoudln't get in, or do you need to allow different people (who are authorized) to access different parts?

  3. #3
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by jmahaffie
    1) CurrentUser will tell you the userid the person is logged into Access with. look up GetNetworkUserName, and it returns what userid the person is logged onto the network with.

    Are you just trying to keep out people who shoudln't get in, or do you need to allow different people (who are authorized) to access different parts?
    I am trying to allow access to different parts and different records. I've done a pseudo security in the past by hiding the database objects and toolbars etc based on user "security level," but that was something that could be bypassed if someone had knowledge - but was sufficient to the task. Similarly I've used the Getnetworkusername before - but I don't necessarily want to confine this to the network user name.

    The plan is to disallow access to design, database windows, etc except for admin group, and to do a lookup query for "authorized" records based on user id. If they double click, we'll open a window with information about the record. Recently I've taken to using unbounded objects on forms, and using update, insert, or delete queries to make changes to data as desired - this creates a little separation where I can do some data validation before updating the actual records. . . so, I don't plan on "filtering" the form, but rather providing a dataset for the form based on the user's access level.

    Thanks.

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    The plan is to disallow access to design, database windows, etc except for admin group, and to do a lookup query for "authorized" records based on user id.
    Access security groups should be sufficient to disallow access to design and to tables/forms/etc. Is it possible to disable access to the database window? If so, and you're using unbound forms, you may be okay. But if the user has read access on the table, then they can read all the records in the table, if they can get to it (via F11 database window). If you can keep them from getting to the tables, you can check their currentuser name, see what group they're in (through code) and use that to pull your records. I'm rambling around in circles, it seems...

  5. #5
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by jmahaffie
    Access security groups should be sufficient to disallow access to design and to tables/forms/etc. Is it possible to disable access to the database window? If so, and you're using unbound forms, you may be okay. But if the user has read access on the table, then they can read all the records in the table, if they can get to it (via F11 database window). If you can keep them from getting to the tables, you can check their currentuser name, see what group they're in (through code) and use that to pull your records. I'm rambling around in circles, it seems...
    I'm not sure, in the startup options I usually tell it not to show the database window, and I use code to hide the toolbars that show it. I am using Access 2003, and pressing F11 - even with a brand new database with a 'blank' form loaded, pressing F11 doesn't seem to do anything.

    Any thoughts on the method for users/groups?

    Thanks

  6. #6
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    I'm not sure, in the startup options I usually tell it not to show the database window, and I use code to hide the toolbars that show it. I am using Access 2003, and pressing F11 - even with a brand new database with a 'blank' form loaded, pressing F11 doesn't seem to do anything.
    OK. So database window locked out (and you're disabling opening using the Shift button). You're protected from users getting into the tables directly.

    As far as user groups and permissions go, it sounds like you're pretty much redoing what the Access security aspect does for you already (unless i'm misreading what you've explained - which i have been known to do). It seems to me that you could implement what you're talking about with access security (set up properly, of course) and two tables. One to store the passwords and another to store the PK of the password record and what groups are allowed to update it.

    So maybe a user logs in, in Group1. Your form could find out what permissions groups he's a member of in the access workgroup, and collect all the records in tblPasswords WHERE fldGroup='Group1' in tblAuthorized. Have to JOIN them on the FK->PK field. It seems like that should work without a WHOLE lot of work. Then again, I'm wrong sometimes...good luck. Did all that make any sense? Too wordy? Maybe I can put together a small db and see how it works...

  7. #7
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by jmahaffie
    OK. So database window locked out (and you're disabling opening using the Shift button). You're protected from users getting into the tables directly.

    As far as user groups and permissions go, it sounds like you're pretty much redoing what the Access security aspect does for you already (unless i'm misreading what you've explained - which i have been known to do). It seems to me that you could implement what you're talking about with access security (set up properly, of course) and two tables. One to store the passwords and another to store the PK of the password record and what groups are allowed to update it.

    So maybe a user logs in, in Group1. Your form could find out what permissions groups he's a member of in the access workgroup, and collect all the records in tblPasswords WHERE fldGroup='Group1' in tblAuthorized. Have to JOIN them on the FK->PK field. It seems like that should work without a WHOLE lot of work. Then again, I'm wrong sometimes...good luck. Did all that make any sense? Too wordy? Maybe I can put together a small db and see how it works...
    It makes sense as much as I read it. I have to read up on Access security it seams, because the point is to create groups and users and limit access of records based on the user/group ie: sales might have access to certain records, purchasing to others, service to another set, and everyone to another set, of course admin would have access to everything (records I mean), not tables/forms or whatnot.

    Thanks again

  8. #8
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Access security is pretty decent, if implemented corrrectly. I put together a quick one to look at, and it seems to do the job. The navigation between records isn't right, and i didn't put in a way to save the changes to the record, but you can handle that, I'm sure. By the way, access isn't implemented correctly on this db. But you'll get the idea. See if it works out...
    You'll need to create a shortcut to join the workgroup as NormalUser to see the records locked out. Gotta go home. Have a great night
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by jmahaffie
    Access security is pretty decent, if implemented corrrectly. I put together a quick one to look at, and it seems to do the job. The navigation between records isn't right, and i didn't put in a way to save the changes to the record, but you can handle that, I'm sure. By the way, access isn't implemented correctly on this db. But you'll get the idea. See if it works out...
    You'll need to create a shortcut to join the workgroup as NormalUser to see the records locked out. Gotta go home. Have a great night
    Awesome, thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •