Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2015

    Question Unanswered: Limiting User Access at a Record Level

    Hi. I am creating an Action Item Database that will be located on a Sharepoint portal so multiple users can access and I'd like each user to have a login/ password to 1) keep unauthorized users out of the database and 2) limit what users can see. This login/ security should ideally work so that user "Bob Smith" logs in with his assigned login/ password and he will only view/edit/ run reports for those Action Items that he has generated (field named "GeneratedBy") or have been assigned to complete ("ResponsibleLead") which are both fields in table "tbl_ActionItems". I can not tie the security levels to a users network login; this has to be security within the database. Any suggestions as to how to do this? I don't necessarily need specific code at this point so much as I need a logical starting point for setting this up. Any pointers/ suggestions are greatly appreciated.

    To further elaborate what I am looking to do:

    In "tbl_ActionItems", we have 3 records added (there will be multiple fields of information for each), but:
    Action Item 1 - was Generated by Bob, and Assigned to Kathleen
    Action Item 2 - was Generated by Dan, and Assigned to Ed
    Action Item 3 - was Generated by Joe, and Assigned to Bob

    Bob logs in and he will see only Action Item 1 (because he generated it) and Action Item 3 (because it was assigned to him to complete). Role Based controls won't work as the "security" has to be assigned to each record.

    Thank you

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Somehow you must be aware of what user is running Access. There ought to be a way to map the user running Access to the people in your database. Build a query that returns only the rows that were created or assigned to the current user, and only permit the users to access that query instead of the underlying table.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so you need to roll your own "security / data access model" within the application. but still use the network logon to get the user id....

    effectively you need to develop some sort of schema based on groups and permissions. mimicing the workgroup approach may be a good way to go
    but it depends on your business rules

    you can find the current user using the network API call
    you would need to create a table containing users (you could use this to authenticate who can use the application), and also define who can generate and or have work assigned to them
    the problem with all these sort of applications is how you provide the overview. say Item 1 & 3 are managed by Maria and she should be able to see stuff relevant to her, Item 2 is managed by Burt, and Whachmacallit sees all rows
    also how do you provide an admin function that can say look at all rows but not modify data (eg a developer or possibly accountant [not that you need do much to encourage an Accountant to look but not touch])

    the actual process to lock or unlock or limit data viewed is realtively trivial
    you can put filters on forms so that the user only sees data they should (in the forms on open event)
    as with most questions in the systems world "is this dooable?" the answer is nearly always "yes". but you nreally, really need a clear idea of what you are protecting against, what are the key factors
    One of the main areas of exposure in plain vanilla Access is security. you can fix most of those issues using a server backend, but in doing so you loose most of the advantages of using Access in the first place. By security I dont' just mean malicious theft or manipulation of data, but data integrity, auditability and some reliability. So yes it can be done. but define not sketch out, your requirements before commiting to table / form design. bolting on security after an app is created is never clever and nearly always leaves massive security vulnerabilities
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2015


    Thank you everyone. You are both right. Once I pin down the approach, code will be simple. It is the set up. Let's hope I got a good enough night's sleep to figure this out today.

  5. #5
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 11
    So more thinking for you
    What did and do is use the tag control
    I group the user into 3 groups user, manager, admin then in each tag I put the Access control user or manager or admin
    Then as the form load I loop tho all control and set the visible to True or False.
    If admin see all
    If manager see manager user
    If user only see user controls

Tags for this Thread

Posting Permissions

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