Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2003
    Location
    New York
    Posts
    47

    Unanswered: Restrict users to specific records

    I am creating a database with confidential records in it. Each child has a coordinator, each coordinator a supervisor plus there are people who can see all the records for a specific part of the program and those that can see only specific children but those kids range across different coordinators and of course, a few people who can see everything.

    How can I set up a log in for each person so they only see those kids that they are allowed to see?

    Thanks
    Terri
    Terri Gavin

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    There are a few ways to do this. It depends on the business rules and database layout.

    If the database stores the relationship between supervisor and coordinator, the supervisor simply has access to all records that any of their coordinators can access.

    If the coordinators are assigned records based on criteria such as child's last name, a table that lists the coordinator and criteria range can be used to grant access. This concept would also lend itself well to those poeple that need access to all records for a specific part of the program.

    If there are no relationships/criteria that can be used, you could create a field in the data table for coordinator, supervisor, other person and store the appropriate user ID for each record.

    To implement the restrictions, you need to have the users each log into the database (see other threads re: 'Security'). You will also need a user/employee table that will contain enough information to determine which records the user needs to access. Check this table and build the appropriate filter for forms and reports that the user will open. Make sure that the user does not have direct access to tables and queries.

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    That last line in rogue's post is very important. I don't think there is a way to really restrict selected records from a table. You can restrict people from reading whole tables, but not allow access to the table and lock certain records. If they can hit F11 and bring up the database window, then they can look at all the records in any table they are allowed into.

    We have a few databases here at work that store SSNs. Many people work in this database, and most aren't allowed to view SSNs. We had to put the SSNs in a seperate table related to the Employee table by an autonumber field), and then lock all but one or two people out of the ssn table. You may have to try this kind of approch, or else have a table for each coordinator or something.

    Have you come up with a diagram of the different kinds of users and what their responsibilities will be? That should be an early step when you have a situation like this. Gotta run to a doctor's appt, but will write more later if still needed.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tgavin
    I am creating a database with confidential records in it. Each child has a coordinator, each coordinator a supervisor plus there are people who can see all the records for a specific part of the program and those that can see only specific children but those kids range across different coordinators and of course, a few people who can see everything.
    I would not recommend JET as the database engine in that case unless you are very experienced with JET & Access security and especially if this is a regulatory requirement. Enterprise RDBMSs (such as SQL Server) are much, much better suited for such security requirements.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2003
    Location
    New York
    Posts
    47
    I appreciate the responses.

    I am in the process of doing it with a log in that goes to a form which picks up the login name and acts as a filter than am deactivating the ability to close it or go to design. I am fortunately NOT dealing with very savvy computer users.

    I am definately not very experience wity JET or Access security but I don't think the security will work since they all need stuff out of the same tables. I can not split the tables because the billing on this stuff is a nightmare with it in one table, I don't even want to contemplate doing it from multiple tables or the nightmare of switching kids from one coordinator to another.

    Any other ideas to make this more secure or seemless would be appreciated.
    Terri Gavin

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tgavin
    I am fortunately NOT dealing with very savvy computer users.
    Is this a satisfactory defence to your employers if someone access\ exports\ modifies data they are not entitled to?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2003
    Location
    New York
    Posts
    47
    I am dealing with a non-profit with programs for disabled children and medicaid. It is more an issue of the child's confidentiality than anyone doing something malicious.
    Terri Gavin

  8. #8
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Terri,
    If you are set on using Access to do this database, and it is a requirement to keep these records secure, then you're probably going to have to split the tables up. But it shouldn't be too terribly difficult, although without having seen it I really don't know.

    You said that billing is a nightmare with everything in one table - that could be your problem there. Hopefully everything isn't all in one table. Switching a child to another coordinator should be easy as far as the database goes, as you'd have a field in the child table for CoordID or something, which would associate a the child with a record from the Coordinator table.

    This forum is for helping, and there are many really smart people on here. If you want to proceed, you can tell us about the table structures, etc, and we can certainly help you out. You may learn something in the process, too, which is always good.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jmahaffie
    If you are set on using Access to do this database, and it is a requirement to keep these records secure, then you're probably going to have to split the tables up. But it shouldn't be too terribly difficult, although without having seen it I really don't know.
    Disagree - admin nightmare.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    I'm trying to be encouraging here ...I know it won't be super-easy, but I just don't see any other way to keep certain data off limits using MS Access. If they do have to restructure the db, it's a bit easier to go into with a "you can get through it, and we can help" than an "oh, krap." either one doesn't change the reality of what they have to do, but may change the attitude towards doing it. Agree with that one?

  11. #11
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Using security to keep the users from accessing the data through forms and queries (hide the db window) and applying a filter based on the user ID to all forms and reports will keep them out of records they don't need. For added assurance you can add a check to the current event of all forms. Having separate tables works great if there are specific fields that need to be restricted, but for record level restriction the administration is unmanageable.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - vertical partitioning is fine in any RDBMS. Horizontal partitioning is a real pain unless the number of partitions is fixed, which it is not in this case. I'm not saying it is not a possible avenue, I am totally disagreeing that it is "not terribly difficult".

    JET is not well suited to this requirement, unless the requirement is not really a requirement (which based on post #19 it probably isn't).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    oh, man...i was totally talking about keeping users from accessing certain confidential fields, which i was saying to solve by breaking into seperate tables. vertical partitioning. Not horizontal. I was thinking one thing and writing the other. apologies all around. so to clarify (and obliterate any false hope i've been putting out) if a coordinator should only be able to access records for certain children, and all the children are in one table, then giving access to the table will give access to all the records. Access (JET) won't be able to lock out certain records. <deflate>

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hey JM - no probs. The forum is a place for candidly expressing views to give the OPs as many options as possible (where there is no simple answer anyway). Don't worry too much if someone challenges you, nor beat yourself up if you get something wrong. We only withhold one week's salary per mistake
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    thanks, poots. i'm cool with making honest mistakes once in a while. as a plus, all those posts, albeit misguided, were getting me closer to 200 forum posts. here's to one more...

Posting Permissions

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