Results 1 to 8 of 8
  1. #1
    jlgray0127 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Oct 2011
    Location
    Central Illinois
    Posts
    185

    MS Access and Security in Multiple User Environment

    Good morning!


    So, I've done what normally happens. I've used Access to build a tool to help me do my job, and make life easier. Worked out fantastic, and eliminated over 100 spreadsheets and Excel from our process, which was the state of this function when I took over.
    I built a database we use for project management and a warehousing tool for inventory, orders, build of materials, and so much much more...
    My delima, is now I've been asked to make this a multiple user (currently have 3 users within my department) tool for over 40 people.

    No big deal. I've got a front end and back end and built it with this in mind. Where I am finding myself in trouble, is I have not really dug into security since I built this, and am not sure how to implement it, as I've not created anything on any of the 70+ forms the team would be using.

    I did set up security levels for the users when I built this, but that was the furthest I got into security, even though I knew this would happen.

    Basically, what I've done, is done is given permission levels from 1 (admin) through 5 (read only).

    What I'd like to do, is create a module (if possible) to restrict what the users can see (hide controls and fields) and do (manually run updates, macros, or check boxes, make selections in comboboxes) within the forms.

    Does anyone know a good place for me to start looking on where to get a starting point for doing something like this, or have something similar in a database, where they'd be willing to share the code, so I can build off of that? I'm not the best at actually writing the code, but am normally amazing at googling and understand enough to manipulate codes, most of the time, to make them work for me.

    Any help or guidance is greatly appreciated!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what I have done in the past is have four tables - users, groups and actions plus a join table between groups and actions

    Groups in this sense relate more to the job description (sales manager, sales exec, stock controller, storeman, etc)

    Users can belong to many groups and each group can have many actions and each action can belong to many groups. An action will typically be read/read-write/hidden and relate to a specific table - but you may have others such as the ability or not to print a report. So 20 tables means say 60 possible actions.

    when a form is opened, you can determine from its recordsource the table or tables it uses and from that see what actions the user is permitted to do. This could be done in a global function - pass the form to it, it inspects the recordsource and can then set the various controls as required based on the control controlsource. Including something in the tag property for a button can also be used to disable or hide 'open form' type buttons to prevent users going into areas they are not permitted to do so.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My approach was to create a UDF (User Defined Object) with properties: FName, LName, Level, EmplNo, IsActive, LoginID (from their Windows network login) and whatever else you can think of as appropriate. Even a custom method can be associated with such an object (.RefreshUserData). Any of these attributes can be called upon from anywhere at any time. For example, if you want to know the empl no of who modified a record, you write dbUser.EmplNo to that table record. You'd be amazed at how much you can get from this object without having to do DLookups or execute sql statements. As for which forms to allow a user to access, a switchboard form can hide access to anything by setting the visible property of controls that access them based on dbUser.Level upon opening the switchboard.

    There is a limit of what you can do to secure an Access db, subject to the level of determination and skill possessed by anyone who seeks to thwart it. IMHO, the minimum (not necessarily in this order) is to:
    - split the db into BE and FE
    - make the fe an accde or mde as the case warrants (what some call an 'executable' but it's not really) with the knowledge of what protection that offers
    - disable the shift bypass key but have an Easter egg (the software type!) on an About Form for enabling or re-disabling it.
    - use a password when linking the be tables - good idea to share this with somebody (like IT person or management) although it is discoverable if they are ODBC; not sure about when they're not.
    -validate that the fe is accessing only the authorized tables in case someone grabs a copy of both and puts them in their personal folder. While this might seem like a data integrity thing, what happens if they think they have their own copy of the tables and from the fe, start deleting data from those tables? Guess where they're linked to. Of course, this would mean they defeated the shift bypass or you forgot to disable it after distributing a revised production version. For this reason, invoking the Easter egg colored a label background red when the bypass was enabled.
    - have user data tables and use that information to limit access to table members
    - hide sensitive macros and tables (can be seen in design view in accde) and queries, which can be seen in sql view.
    That's it for now.

    Epilogue
    If you're reading this and are a moderator, if forum doesn't have a treatise on what can be done to secure an Access db, there ought to be one. One or more forum mods should have sole rights to amend suggestions in order to preserve its content and limit it to one post. If there is one, wish I had thought of it before now...
    Last edited by Micron; 10-26-2016 at 03:51 PM. Reason: grammar
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'd mention a couple of other things - do not allow users to see the navigation window or ribbon

    - use a password when linking the be tables - good idea to share this with somebody (like IT person or management) although it is discoverable if they are ODBC; not sure about when they're not
    very easy to do if someone has a small amount of access knowledge. Just look at the connection field in mysysobjects.
    hide sensitive macros and tables (can be seen in design view in accde) and queries
    only works if user does not have access to ribbon and file>options>current database where they can make them visible again. Other option is to prefix forms/tables/macros etc with Usys. Access the treats them like system objects (as in show/hide system objects) but still relatively easy to override by knowledgeable user. Macros are very insecure - better to only use vba code in mde/accde and better to ensure users cannot write their own macros (which they can in mde/accde if they have access to the ribbon)

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am not aware of any standard security practices or code for Access. There was a post by David Crake and NTC in answer to a poster and the idea was--it's a roll your own scenario. I have mentioned RBAC role based access control, and audit logs in response to posts. Other have made comments and suggestions that are in the same area in my view.

    If Ajax and/or Micron or others have a working set up that seems to be applicable generally, I'm sure any code/sample database etc could be added to the code repository. It may be a start.

    If anyone is aware of other schemes and/or examples, please identify same with related info.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The original question was about restricting users to what they can see/do rather than securing the database against malicious or accidental actions. There are general principles to follow to secure a database which have been documented numerous times but these will be shallow or deeper depending on the strength of the perceived threat and the value of the data. Any system can be broken in time so all you are buying (or requiring the hacker to spend) is time.

    I would summarise:

    To make it difficult for someone to copy the backend, or if copied, difficult to open
    1. put the backend somewhere obscure
    2. rename the backend (including the extension) to something obscure - access will still successfully link to myBackEnd.txt or myBackEnd.jpg
    3. password protect the backend (.accdb encryption is 100+ x more secure than .mdb) and keep the password secure and complex (8+ chars, mix of lower, upper, number and wildcards)
    4. Do regular backups and store in secure location (as much against malicious/accidental deletion as corruption)
    5. Instead have a backend in sql server/MySQL which is easier to defend but more costly to maintain.

    But then that info is easily discoverable in the front end, mde/accde or not - so what's the solution?
    1. don't have linked tables (so nothing in mysysobjects). instead create a backend db object i.e. set beDB=dbengine(opendatabase,path to backend) then populate all forms/reports by recordset rather than recordsource
    2. don't have query objects - there aren't any tables they can use anyway - you can in fact put the queries in the back end
    3. ensure the BE password is not hardcoded, instead it needs to be calculated in some way (reason - opening a .accde in notepad, hardcoded values are easily discoverable)

    For protection of code and form/report design
    1. use mde/accde
    2. hide navigation window and ribbon and disable all means of enabling them.

    For protection from sql injection
    1. ensure user input is filtered before including in a sql statement

    For protecting commercial interests (e.g. preventing a departing salesperson from taking a list of all customers to a competitor)
    1. restrict users to what they need to see to do their job
    2. disable copy facility (shortcut/rightclick menus)
    3. limit use of datasheet views - use continuous forms without record selectors
    4. limit data returns to a screen full of data at a time (so user would have to do multiple copy/pastes)
    5. log all user activity - so 'suspicious' behaviour can be identified
    6. rigorous user access management

    I'm sure there is more, so please feel free to add to the list. As you will note, much of it is not code but design principles which typically have to be taken into account at the start of development, not as an afterthought.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Ajax View Post
    The original question was about restricting users to what they can see/do rather than securing the database against malicious or accidental actions.
    If it wasn't expressly asked for, I believe it was implied
    Where I am finding myself in trouble, is I have not really dug into security since I built this, and am not sure how to implement it,
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ajax,
    Great list. Good ideas. Hopefully it can be reviewed and expanded as applicable.

    Micron,
    I went by the title of the thread -perhaps I read more into it than intended, but the subject comes up from time to time, and it would be good to have something to point posters to. I think Ajax's list is what the poster was seeking generally. If you or others can expand, show some alternatives etc, I think it would make a great reference (as per your epilogue above).

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

Similar Threads

  1. Import Access Database into Excel in Multi-User Environment
    By Chim20 in forum Import/Export Data
    Replies: 7
    Last Post: 10-06-2016, 02:23 PM
  2. Replies: 1
    Last Post: 03-24-2015, 01:49 PM
  3. Replies: 13
    Last Post: 11-18-2013, 02:20 PM
  4. Replies: 3
    Last Post: 03-17-2012, 10:49 PM
  5. Access User Security
    By KkcBroad50s in forum Security
    Replies: 3
    Last Post: 04-29-2011, 09:31 AM

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