none
Record Level Security RRS feed

  • Question

  • I have a database that requires Teachers to enter grades for their students. They have to select the class from a combo box. How could I restrict them to see only the classes that they are teaching. I will also like to restrict users to the forms and buttons that they have to interact with.
    Monday, July 31, 2017 12:57 AM

Answers

  • Thank you for the assistance. I used the TempVars collection to store the UserID of the person who logged in and used it as a criteria in the query that supplies the data for the combo box.
    • Marked as answer by Gordon Swan Thursday, August 3, 2017 11:13 AM
    Thursday, August 3, 2017 11:13 AM

All replies

  • You would have them login, or ask Windows who they are.

    Some data tables such as tblGrades or tblStudents would have extra fields to indicate who the teacher is.

    Then provide forms that use this information to show only their own data.


    -Tom. Microsoft Access MVP

    Monday, July 31, 2017 1:15 AM
  • In addition to Tom's comments, if you are running Access 2007 or higher, you will have to build your own user level security if you haven't done so already, based on windows login and/or database username and password, with a users' table at the core containing information such as last name/first name, login name, password and a unique user ID. That unique user ID would then be used by vba code to restrict what records, forms, buttons etc a user can see and their level of access.

    IF this is something that you are not inclined to tackle, there are third party utilities out there with forms, code, etc that you can import into your databases to provide user management/security down to the control level.  (And if you already have basic user login security, such utilities can be modified to work with your existing user tables to give you more advanced user level security.) 

     

    Miriam Bizup Access MVP

    Monday, July 31, 2017 10:11 AM
  • Hi Gordon Swan,

    As suggested by Tom van Stiphout (MVP).

    you can try to make login for users.

    you can properly design the table for it and try to fetch the data from it.

    you can then try to store the user in variable and try to use it through out the application.

    below is small example to get the idea.

    now I use code below.

    Private Sub Command4_Click()
    Me.Combo2.RowSource = "select class_name from class_data where teacher_name='" & Me.Text0 & "'"
    Me.Combo2.Requery
    End Sub

    Output:

    you can see that currently I am taking the data from textbox but you can store the username in variable or you can fetch it from database and directly pass in the query instead of giving any choice to user to enter name.

    so that they don't have any chance to make entry in some one else class.

    this is just an example. try to understand it and implement it in your database. you can modify it as per your need and requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 31, 2017 10:17 AM
    Moderator
  • You might like to take a look at SecDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Take a look first at SecDemoSimple_PseudoLogIn.accdb.accdb in the zip archive.  In this a row entered by a user is assigned to that user only, but can be assigned to other users also via the button on the main form for changing permissions.  You would not include this functionality, so that each row is accessible by the original user only.

    This file simulates user login, but for demonstration purposes allows users to log in as another user.  In an operational context this would not be allowed of course.  For an illustration of automatically logging the current user in on the basis of their Windows login name see the SecDemoSimple_AutoLogIn_Projects.accdb variant of the file in the zip archive.  This assigns users to projects, with each row being accessible by members of one or more projects only.

    The basic SecDemo07.accdb file allows users to be assigned to one or more groups, with access to each row being to one or more groups.

    For restricting users' access to forms or buttons within a form, you could put code in the forms' modules to cancel the opening of the form if an unauthorised user attempts to open it, identifying the user by their windows login name, or, in the case of buttons, disabling the buttons similarly.  Alternatively you can create different versions of the front end, restricting them to the functionality available to different classes of user.  Each user can then be provided with the relevant front end.

    You will of course need to ensure that the users' access to table via the navigation pane is unavailable, and that they do not have access to code.  For the latter distribute the front ends as .accde files.

    Ken Sheridan, Stafford, England

    Monday, July 31, 2017 11:08 AM
  • Thank you for the assistance. I used the TempVars collection to store the UserID of the person who logged in and used it as a criteria in the query that supplies the data for the combo box.
    • Marked as answer by Gordon Swan Thursday, August 3, 2017 11:13 AM
    Thursday, August 3, 2017 11:13 AM