Results 1 to 13 of 13
  1. #1
    newbie30 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    15

    Restricting Ceratin Users from viewing all the forms and merchantnumbers.

    Hi,

    The current company (Comp A) I was working in has acquired another company(lets say comp B) and hence I was given the task of merging all the data together and import the file(Excel) they were using into our access database.

    In every form possible, I gave a combo form and let the Comp A see all the data. However, I need to restrict the Comp B(new company) from viewing only certain forms(just the import button and form) and only their merchant numbers(that begin with %1801%)



    Am using SQL for all the back end. And VBA Access 2010 version.

    Kindly help!

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need a users table, with each user having their logon id and the company they belong to. Then when the database opens, see who is using the database and restrict based on their company.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Further to the reply, how to handle this depends in part on what type of navigation you are using. I don't use Navigation forms (or apparently whatever a switchboard form has become) so I can't help there. But if you're using your own form as a switchboard form and that form contains a means of launching other forms and their process, then show those buttons/navigation links to users based on their identity according to the table as suggested.

    If you want to get fancy, learn how to create a custom object (e.g. dbuser) which has properties such as FName, LName, LoginID (network login id), email, EmplNo, etc. etc.) then you can get these values during any process. Some people prefer TempVars for this. Regardless, you will have to check the credentials of the user for every form or report that opens, which makes me think that a switchboard form is the way to go. Then if they can see it, they can access it, which ought to negate the need for running this check for every access request. Keep in mind that other steps have to be taken if you wish to thwart anyone from bypassing these protections, such as hiding the nav pane (in the least), preventing the shift bypass, shortcut menus etc. I advocate using a split db design where the back end tables have been linked with a password. It isn't foolproof, and I'm not going to reveal how this password can be discovered, but in all things Access, controlling access is a compilation of things and cannot be achieved by implementing one aspect of protection.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    newbie30 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    15
    Thanks for the reply.

    I have created 3 tables here called 1. SecurityUsers which has the login details, uemail, pwd and companyid.
    2. SecurityObjects : which lists all the forms
    3. securityPermissiosn : which the user is given permissions(read, delete, write) based on the object.(Added the companyId field here too)

    I created a global function CheckSecurityAccess that queries the permission table based on the objectId and userid..UserId is the windows userid.(ENVIRON(username)

    For every form in Access I called this function:

    If Not CheckSecurityAccess(17) Then
    CreateSecurityAccessMsgBox "Browse For File"
    Exit Sub
    End If

    To expand to this, how do I also check for the companyId. Say if the user belongs to companyId "4" show him certain forms, if "1" then he can view ALL.

    Thanks

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Get rid of the password, do not require the user to have a password or to log in. It is not necessary as you know who the user is based on their Windows login, and also requires a robust "forgot your password" routine (I recently forgot my Yahoo password and they do not have this service available, so I have lost 20 years of information and contacts - let this be a warning to all). Have the main form bound to the users table and when it opens (the OnLoad event), get the login from Windows and filter to that user. Now you have all the information on that form and you can use it throughout the database.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    To expand to this, how do I also check for the companyId. Say if the user belongs to companyId "4" show him certain forms, if "1" then he can view ALL.
    Include which company the user belongs to in the user table. Since you're not using a custom object, TempVar values might be a solution for you. You can do a DLookup for the company (or any other user value) and create TempVars for each value/property of the user, and assign the DLookup value to each. Seems to me that for any form that is invoked, the Open event can check the user credentials (TempVar value) and Cancel the open event (the load event cannot be cancelled). If you used a switchboard type of form as I suggested, you wouldn't have to worry about any other form. This one would show or not show the ability to open any form.

    Agree with the password thing. Forget it. Even if you use encryption on this field at the table level, it's useless if anyone can get at the table. You are preventing anyone from being able to view the db objects such as tables as much as possible, I hope.

  7. #7
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    An example of filtering certain forms or subform tabs (Me.TabControl.Pages(#)) for that matter based on user type. There is much more to this, I just chopped it down to make it reasonable. In this example there are three types of users "Admin" "Manager" "User", which each have their perks. I actually have more but they were not necessary to convey the concept here.

    Private Sub Form_Load()
    On Error Resume Next
    Dim Permission As String
    Permission = [Forms]![frmLogin]![txtPermissions].Value
    If Permission = "Admin" Then
    Me.Caption = "Admin View"
    Me.TabControl.Pages(0).Visible = True
    Me.TabControl.Pages(1).Visible = True
    Me.TabControl.Pages(2).Visible = True
    Me.TabControl.Pages(3).Visible = True
    Me!txtCaseNum.Visible = True
    Me!txtStaffNum.Visible = True
    [frmCase].[Form]![txtAssignStaff].Visible = True
    [frmCase].[Form]![AssignStaffHighlight].Visible = True
    ElseIf Permission = "Manager" Then
    Me.Caption = "Manager View"
    Me.TabControl.Pages(0).Visible = True
    Me.TabControl.Pages(1).Visible = True
    Me.TabControl.Pages(2).Visible = True
    Me.TabControl.Pages(3).Visible = False
    Me!txtCaseNum.Visible = True
    Me!txtStaffNum.Visible = True
    [frmCase].[Form]![txtAssignStaff].Visible = True
    [frmCase].[Form]![AssignStaffHighlight].Visible = True
    ElseIf Permission = "User" Then
    Me.Caption = "User View"
    Me.TabControl.Pages(0).Visible = True
    Me.TabControl.Pages(1).Visible = False
    Me.TabControl.Pages(2).Visible = False
    Me.TabControl.Pages(3).Visible = False
    Me!txtCaseNum.Visible = True
    Me!txtStaffNum.Visible = False
    [frmCase].[Form]![txtAssignStaff].Visible = False
    [frmCase].[Form]![AssignStaffHighlight].Visible = False
    'Anyone with username and password attempting to access this form without a proper permission level set forces the application to shutdown without warning if they made it past login form with the following.
    ElseIf Nz(Permission, "") = "" Or Nz(Permission, "") = 0 Then
    Beep
    'module that handles the logging
    modSomeName.Tracker "Unauthorized Access Attempt - Permissions Null or 0"
    DoCmd.Quit
    ElseIf Permission <> "Admin" Or Permission <> "Manager" Or Permission <> "User" Then
    Beep
    'module that handles the logging
    modSomeName.Tracker "Unauthorized Access Attempt - Permissions Not Recognized"
    DoCmd.Quit
    End If
    'otherwise open the form/subforms and set all records to new/blank
    DoCmd.GoToRecord , , acNewRec
    End Sub
    Last edited by SierraJuliet; 01-04-2018 at 01:08 PM.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Get rid of the password, do not require the user to have a password or to log in. It is not necessary as you know who the user is based on their Windows login, and also requires a robust "forgot your password" routine (I recently forgot my Yahoo password and they do not have this service available, so I have lost 20 years of information and contacts - let this be a warning to all). Have the main form bound to the users table and when it opens (the OnLoad event), get the login from Windows and filter to that user. Now you have all the information on that form and you can use it throughout the database.
    Disagree with this.
    Users may login to the program on a different workstation with another users login
    The password allows an extra level of security to prevent that (or make it MUCH less likely)
    Also users can be assigned to different levels of security allowing/preventing access to sections of your application e.g. Admin menu

    The password can be taken from Active Directory data so doesn't need to be stored in the app
    However, if you want to store the password, it should be encrypted in a table that is hidden from users.
    In fact ALL tables should be inaccessible to standard users - use an ACCDE file where the navigation pane is HIDDEN using code

    As for Yahoo not having a list of passwords...I don't believe them
    Unless of course they scrapped passwords after user accounts were hacked a few years ago.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Users may login to the program on a different workstation with another users login
    You're saying in cases where person A can go on person B's pc and use B's login credentials and system password (which I dare say would be a violation of IT policy in most companies) an Access password provides a higher level of protection in that situation??

    I can't imagine B being OK with A going to any pc in the company and logging in as B, which exposes all email, files and other data to A but that's OK because whatever B does in Access is protected by a different password. I must not be understanding what you meant by your statement. I suspect you might have misinterpreted what you've quoted because:

    If my db reads the Windows login name of the person trying to open the db it is safe to presume that person is a registered user of the db if their Windows user/account name is in a db table. If it's not, I just close the db and they don't see anything except a message explaining the situation. I don't see the need for a db login password and the management overhead that comes with it. To each their own, I guess. My apologies if I've misinterpreted your meaning.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Micron View Post
    You're saying in cases where person A can go on person B's pc and use B's login credentials and system password (which I dare say would be a violation of IT policy in most companies) an Access password provides a higher level of protection in that situation??

    I can't imagine B being OK with A going to any pc in the company and logging in as B, which exposes all email, files and other data to A but that's OK because whatever B does in Access is protected by a different password. I must not be understanding what you meant by your statement. I suspect you might have misinterpreted what you've quoted because:

    If my db reads the Windows login name of the person trying to open the db it is safe to presume that person is a registered user of the db if their Windows user/account name is in a db table. If it's not, I just close the db and they don't see anything except a message explaining the situation. I don't see the need for a db login password and the management overhead that comes with it. To each their own, I guess. My apologies if I've misinterpreted your meaning.
    What you're describing may represent the situation in the environment your clients work with but it doesn't apply for my clients.

    Many of my databases are written for staff use in schools.

    In this environment, many users will access computers that are shared with other users e.g. staff rooms & classrooms

    Whilst it should be the case that users log out at the end of a session / lesson, that often does not happen.
    There are many reasons for that - pressures on staff / laziness / carelessness / emergencies etc
    There have also been cases where students have used staff computers when the member of staff is out of the room.
    With the best will in the world, no school will be able to prevent staff using computers others have logged into

    So I do indeed maintain that another level of security is essential to prevent misuse of my apps
    These apps include student, staff & whole school data - much of it is sensitve
    Certain features are restricted to specific users e.g program admins

    Other features contain highly confidential material e.g. child protection (CP) data.
    These require a further password known only by staff who need to know ...

    Schools have a choice in how to manage login passwords:
    In many cases, the staff network password is used so no need to store it in the app - therefore no management overhead
    The app checks the entered password matches that in AD and allow / prevents access accordingly

    In other schools, a separate password is used. An encrypted version is stored in the program & is inaccessible to users.
    Even if a program admin viewed that table the password would be unreadable
    Again minimal overhead - once the password is set, it needs no action unless there is a need to change it at a later date

    Similarly the CP password is encrypted (using a different algorithm) and changed at specified intervals

    For the end user, the process is smooth & efficient

    Of course, I'm not claiming this makes it OK that email etc could be viewed by others.
    But the email software (not mine) used by many schools prevents that also - you can I'm sure guess how

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks for the insight. What you describe would never fly in most industry/commercial environments, I think. Seems these users aren't enforced to log off when done, or lock the computer when leaving the room. Nor does it seem they are faced with a sleep login for any reasonable amount of time of non-use (like 5 minutes). Yes, I'm used to an environment where you had to log back in if there was no activity for a short period of time. I would never have thought to access anyone else's pc if they were logged on. You could find yourself in trouble for it.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When I was still teaching, we tried cracking down on staff leaving computers unlocked, staying logged in overnight etc.
    Various group policies were attempted to try & enforce this with some effect but the system could never be locked down sufficiently to be foolproof.

    I remember one incident where a student set off a fire alarm on purpose so he could hack his & other students records whilst everyone else was out of the building.
    He was trying to delete / alter various disciplinary records in my database app
    Of course, the teacher didn't lock down their computer before leaving the building!
    The student was hiding nearby & got into several admin programs but I'm pleased to say couldn't get into mine.
    The student was identified by various means & punished severely.
    The member of staff rightly pointed out that in a fire, saving life is the priority.

    In all the many years of staff using my apps and having to login with user name / passwords there were never any problems or complaints...
    And as I said before additional passwords were required for child protection and other extra confidential material

    And if you think that's not enough, when staff logged into my app from home using Terminal Server, I added an extra layer requiring DOB & home postcode before reaching the normal login screen!
    I thought that would get complaints but not so ...

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry to jump so late in this, if still interested please have a look at my custom access levels package on forestbyte.com (under the MS Access Utilities page). I created that a while ago to do just what you need, but with a much finer "granularity". You guys mention denying access to entire forms; mine works at the control level, so you can hide/disable/lock individual controls; if you have tab controls on forms you can easily hide/show entire tabs for each user group (such as financial info or child protection). For forms (and subforms) you can allow specific actions (can add but not delete, cannot add or delete but edit, etc.,, you get the idea. If you use that with an accde file and a custom ribbon you can get quite a secure application.

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Viewing Continuous forms
    By scoe in forum Forms
    Replies: 9
    Last Post: 10-04-2013, 01:04 AM
  2. Replies: 1
    Last Post: 05-12-2012, 11:38 AM
  3. users only need to see forms.
    By BF15 in forum Security
    Replies: 1
    Last Post: 08-25-2011, 05:25 PM
  4. Replies: 1
    Last Post: 08-10-2011, 06:28 AM
  5. access restricting users
    By jmskms in forum Access
    Replies: 2
    Last Post: 03-05-2010, 08:39 AM

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
  •  
Other Forums: Microsoft Office Forums