Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Permitted users only, restrict database

  1. #1
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    280

    Permitted users only, restrict database


    So, I have a database which contains sensitive data, so I only want certain people to have access to it. I also want to restrict it so the database is restricted so the users can't accidentally break things. A user on another forum suggested the following, but didn't go into detail as that's not what the thread was actually about. I've included any comments I have regarding these suggestions below the comment itself.

    operating system security, setting user access to folders
    -This would be managed by a different department who would charge us, I'd rather not go down that route.

    having a table of user names, allowing only those users access to the database (the function : Environ("username") provides the user name, no need for the user to log in to the database or to provide a password
    -I like this idea but not sure how to do it, assistance would be good. would it use the username used to log onto the machine?

    the production version of the database will be an accde
    -I'm not sure what was meant by this

    create a customized ribbon with only the features you decide on
    -again, this could work but I'm not sure how to do it.

    set options, such as don't allow access to the navigation pane, don't allow shortcut menus or bypass keys
    -again, could work, but I don't know how to do it.

    you MUST split the database - have the tables in a separate database (back-end) and link to them from the front-end (I have not done this myself, but I have seen it around that you would put a password on this file and when you link to the tables you will include that password. Breaks the rules of forgetting but it would be a good idea to put the password in the front-end, maybe in linking code, so that no one has to remember what it is)
    -I don't know what this means or how to go about doing it.

    any local tables will be hidden
    -I do know how to do this

    All assistance would be greatly appreciated.
    Any queries, just fire away

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    4,536
    put the database in a folder restricted to your users.
    also you can have a table in the db for authorized users and their level
    userID, name, level

    when the db opens, open the main menu, this will grab the userID put in read only textboxes.
    Code:
    form_load()
       'get userID
    txtUser =  Environ("Username")
    
      'now get level
    txtLevel = Dlookup("[Level]","tUsers","[userID]='" & txtUser & "'")
     
    'now you have level, restrict buttons and form based on this.
    btnDetail.enabled = txtLevel <>"U"
    
    end sub
    I use: A= admin, M=manager, U=general user

  3. #3
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    280
    Quote Originally Posted by ranman256 View Post
    put the database in a folder restricted to your users.
    Like I said, I can't create a folder which is accessible to only specific users as that setting is locked down to me and can only be set up if I speak to another department, who will charge us each time we need to change the users.

    Quote Originally Posted by ranman256 View Post
    also you can have a table in the db for authorized users and their level
    userID, name, level

    when the db opens, open the main menu, this will grab the userID put in read only textboxes.
    Code:
    form_load()
        'get userID
    txtUser =  Environ("Username")
    
       'now get level
    txtLevel = Dlookup("[Level]","tUsers","[userID]='" & txtUser & "'")
      
     'now you have level, restrict buttons and form based on this.
    btnDetail.enabled = txtLevel <>"U"
    
     end sub
    I use: A= admin, M=manager, U=general user
    I think everyone who can open the database would have full access to everything on the forms (in form view), I just want to restrict it so they can't get to the tables, queries and other views of the forms - would your code be able to do that? where would I put it? you're probably going to have to go very basic with me, I'm a bit of an amateur compared to you guys.

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    3,994
    You really need to do a 'threat assessment' to decide how far you want to take security. Who are you protecting from? and from doing what? On data security, are you concerned about someone deleting files (accidentally or not?), from copying files or taking data off site? from making uncontrolled changes? System security is more about protection from users making changes to forms/reports/calculations.

    On data security, one you don't appear to have considered is protection from sql injection from user input. Another is protection from users copying data to the clipboard, then pasting into excel etc. and then taking the excel file to their new employer!

    the production version of the database will be an accde
    -I'm not sure what was meant by this
    a .accde is compiled code so users cannot get into the design view for forms and reports or see/change code in modules. Personally I believe this to be a must except perhaps for small one off db's. On its own, users will still be able to see tables (and modify local ones) and see and modify queries. The .accde is created by going to file>Save and Publish and choosing the make .accde option. Note all code should compile successfully for a file to be created. (in VBA window, choose Debug>Compile). All modules should have Option Explicit at the top, just below Option Compare Database and it is advisable to ensure you have adequate error control in place. If users are using a .accdb then an uncontrolled error will dump them in the vba window

    create a customized ribbon with only the features you decide on
    -again, this could work but I'm not sure how to do it.
    investigate commandbars - it is a bit daunting until you get your head round it


    set options, such as don't allow access to the navigation pane, don't allow shortcut menus or bypass keys
    -again, could work, but I don't know how to do it.
    plenty of examples on this and other forums - google 'allowbypasskey' and 'allowspecialkeys' - however both are easily overridden by someone with a small knowledge of access.

    any local tables will be hidden
    -I do know how to do this
    Again easily discoverable by someone who know what they are doing. Another little trick is to prefix the tables you want hidden with 'usys'. It will then be treated as a system object - but users only need to tick the show system objects option to see them

    putting a password on a back end is a good idea anyway, but again if you link tables, it is easily discoverable by someone with a small knowledge of access. Hiding the password in compiled code helps, but again, moderate users of access will know how review the .accde file and discover the password. Investigate hashing the password. If data security is your main issue consider using sql server or similar (but I guess you will need to pay for that one way or another).

    One other option for the front end is to hide the access window - all forms will need to be popup/modal and there wouldn't be a ribbon to use - google 'ShowWindowAsync Application.hWndAccessApp()' to find out more.

    Other point about front ends is each user must have their own copy of the front end. Under no circumstances should multiple users use the same front end file, if they do, your run the risk of corruption

  5. #5
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    280
    Ok, I definitely want to protect against someone deleting/changing tables/queries/forms - that should be restricted to just me - so it looks like making it an accde database could work, if combined with other stuff. what did you mean by adequate error control? How can I make sure I have done this?

    I may not need to restrict the database so that only certain people can access it - I've been thinking, and since it would likely be in a folder restricted to my team anyway (so I wouldn't need to speak to the IT department about setting one up) and everyone in my team should be fine accessing it, maybe I don't need to restrict it on that front. I'm awaiting confirmation from my manager.

    I hadn't thought about sql injection or copying data onto the clipboard - because those that will have access to the database have already had their criminal record etc checked, depending on the conversation with my manager, this may all be extra - unnecessary - security because of the systems the company already has in place, again, awaiting to hear from my manager.

    I'll give those suggestions a search to see what I can find, I may soon be back however - like I said, I'm not all that knowledgeable with this.

    There would only be one user in the database at a time - only the user (one person, with another potentially covering if the original is on leave/sick) and myself would have any need to go into the database, and I would not be entering data myself. If necessary, I could ensure the other person is not in the database at the time I go in to make any changes to ensure there are no corruption issues.

  6. #6
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    3,994
    what did you mean by adequate error control? How can I make sure I have done this?
    error control is about protecting against 'unexpected' events - such as code that assumes there is are records returned from a query but there aren't any, or referencing a form that isn't open. You need to test yourself!, and then a period of user testing before the official 'go live'

  7. #7
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    280
    So making sure that if that does happen, it doesn't display the message that will take the user to the VBA if they select debug?

  8. #8
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,890
    Continue on the route of splitting the database. With data in one database and everything else in another, the minimum advantage is that when you make changes you can make them in your own/development copy, test them and get them working - and then copy just the front-end over. In the meantime the user has continued to enter data and if your database contains data it will overwrite, otherwise you have headaches trying to match the old tables with the new forms, etc.

    Accde is a good idea, altho make sure to keep development copies separate so that the users can't access them.

  9. #9
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    280
    How do I go about creating a 'front end' version?

    Also, if I needed assistance on here - and needed to upload the database again - how easy would it to be to get the data out? Would simply selecting the whole table and deleting the records (and repeat for each table) erase all the data?

  10. #10
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,890
    Front- and back-ends are just buzzwords, they are both just ordinary Access databases. Create a new one, import the data, delete the tables from the original db, go to External data and link to the tables.

    You could zip both db's together into one zip file.

  11. #11
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    280
    Ok, I think I've done that - see attached. But it appears to me that you can still open the tables from the front end?

    I actually meant from a data protection point of view - if I copied the databases, would deleting the data from the tables of the copy remove all trace of what was there before, so that I can upload the databases for assistance?
    Attached Files Attached Files

  12. #12
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,890
    You've done it correctly. This is not a security thing. Not sure what you are asking about deleting, there is no change in that, deleting all data deletes all data in that table in the database in which it is housed.

  13. #13
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    280
    Ok, I'm unsure how I would then "just copy the front end over" since the back end (which I would be doing the development on) does not have any forms or queries on - or can I do it so that the back end is exactly the same as the front end, but with the tables in the database, then whenever I make changes I just replace the front end with a copy of my back end, remove the tables and link it to the original back end?

    Hopefully that makes sense...

  14. #14
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,890
    Hopefully that makes sense...
    Not at all!

    - in development, 2 db's - a front-end and a back-end (all tables)
    - to production - back-end and front-end (now an accde)
    - changes required - work on development front-end
    - to production - front-end only

    Do you see how the back-end that the user is working on isn't touched? They continue working while you can take your time in development.

  15. #15
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    280
    Quote Originally Posted by aytee111 View Post
    In the meantime the user has continued to enter data and if your database contains data it will overwrite, otherwise you have headaches trying to match the old tables with the new forms, etc.
    oh, so there is a back end and a front end in both development and production? Your comment above threw me.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple Users in the same Database
    By Vandacardoso in forum Access
    Replies: 20
    Last Post: 07-09-2015, 06:24 AM
  2. Replies: 5
    Last Post: 06-26-2013, 10:40 AM
  3. How to restrict users?
    By cindytan in forum Access
    Replies: 5
    Last Post: 02-08-2012, 08:40 AM
  4. Replies: 1
    Last Post: 09-26-2011, 02:12 PM
  5. Replies: 3
    Last Post: 12-15-2009, 12:47 PM

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