Results 1 to 11 of 11

Thread: Access Security

  1. #1
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hello again, Colleagues All,
    I have a client who has a database system where there is a single front-end used by customer-facing operators and back-of-house managers. There are many reports and queries, some of which are OK and some of which he wants to retain a degree of confidentiality about. When I looked at the users and groups, with a few exceptions every user had total privilege (i.e. the default). What I have suggested is making each user a member of one or more groups and apply access privileges at the group level. Thus you would have the owners and administrators (the client and me) with total access, managers with more restricted access, supervisors with more restricted access again and finally the users who only access what they need to know to service the client. Every person is a member of a group corresponding to their seniority plus all lower-level groups. Reports, queries plus a few table-level operations (e.g. deleting records) would be assigned to only one group.
    This sounds nicely coherent, but when we came to apply it, the operators could not log in. The questions I have are these:
    1) Does the group privilege work by itself, or do we still have to assign privileges to each user ? (Oh, please, no!).
    2) If we have a front-end which is the only access to the back end (at least for the regular users), do we need to assign any restrictive privileges to the back end, assuming the front-end is the only mode of contact ?
    3) Am I in fact, thinking along the right lines.
    All advice gratefully received

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    1) Does the group privilege work by itself, or do we still have to assign privileges to each user ?

    Yes, users inherit the permissions of the group they belong to; there should no need to assign permissions to individual users.

    2) If we have a front-end which is the only access to the back end (at least for the regular users), do we need to assign any restrictive privileges to the back end, assuming the front-end is the only mode of contact ?

    Users can open the backend directly. If you're not worried about that, you can leave the backend unprotected, otherwise that is a seucrity risk.

    3) Am I in fact, thinking along the right lines.

    Personally, I'd give each user an individual copy of the frontend.

    You mentioned that the operators couldn't log in. Are you sure that everyone is opening the database with the secured .mdw file?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787277' date='03-Aug-2009 06:01']1) Does the group privilege work by itself, or do we still have to assign privileges to each user ?

    Yes, users inherit the permissions of the group they belong to; there should no need to assign permissions to individual users.

    2) If we have a front-end which is the only access to the back end (at least for the regular users), do we need to assign any restrictive privileges to the back end, assuming the front-end is the only mode of contact ?

    Users can open the back end directly. If you're not worried about that, you can leave the back end unprotected, otherwise that is a security risk.

    3) Am I in fact, thinking along the right lines.

    Personally, I'd give each user an individual copy of the front end.

    You mentioned that the operators couldn't log in. Are you sure that everyone is opening the database with the secured .mdw file?[/quote]

    Point 1. Thank goodness for that !

    Point 2. The back end is located elsewhere and not generally accessible to users, but I was wary about creating conflicts if I had permissions in each database which did not match exactly and this could have been the case given the different levels of privilege available.

    Point 3. Users each have their own copy of the front end. They invoke it through a desk-top icon which identifies the user and nominates the location of both the front end and the (shared) workgroups file. I made all of the changes to one of the front-ends, then copied it over to the others. I assumed this was OK because of the use of group permissions instead of individual users ones. Any problems with this ?

    I have downloaded all of the materials and will be having a closer look to try and identify conflicts or missing privileges.

    Thanks for your input once more

    Jim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Opening the database from a shortcut that points to the frontend and the secured .mdw is the correct way to do it. Make sure that the users have sufficient permissions on the folder with the frontend, the folder with the .mdw file and the folder with the backend..

    You could also give each user an individual copy of the .mdw file and adjust the target of the shortcut accordingly.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    As Hans notes, you can deploy the .mdw file to the local workstation as well - it really depends on how many users you have. If only a handful, then a shared .mdw file is OK, but if you have many users (>15 or 20), then the .mdw file can become a source of problems. The security .mdw file can go corrupt just as regular Access databases, and when that happens, if you have a shared file, all the users must exit before you can repair the situation. We tried that with a client with some 80 potential users, and discovered it was a real issue.

    Also, if your security situation gets too complex, you may want to look at some third party products that build on Access security. The basic problem is that you typically have a menu that is shared across the user population, and when users who are restricted from a given form or report attempt to use that object, they get a not-to-friendly message that they aren't allowed to do that. The third party tools let you build custom menus so the user doesn't even know that option exists if they don't have permission.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Thanks for your input, Hans and Wendell,
    The database I am having problems with is an old one going back to 1991 and was recently converted from 1997 to 2003. Since my last post I have had a look at it and have pinpointed where the problem occurs. The software used to be shared by a community of companies with a single call centre and the front screen is a selector for a database. It is only when you select the database that you get the message that you do not have access privileges to a certain object, which turns out to be the first table in the backend. The backend has default privileges which more or less allow unlimited access. The selected database is relinked to the frontend every time you start up with quite a lot of Window OS level code (ex 1997 with lots of Types and Declares, but which I suspect would be easier in straight VBA, these days). Actually, as all of the community members now have their own servers, I have suggested to the client that he ditches the database selector altogether, which may fix his problem.
    On a more general note, your insights are most valuable, because I am designing a new database for one of the community members, to replace the old one and I am already moving towards the sort of structures you suggest. I do not give the users their own .mdw file, because all permissions are assigned to groups, rather than individuals. Instead, I keep a couple of copies, one offsite, in case of corruption. The idea of users not seeing the reports, etc. that they are not allowed to use I have adopted by having all of the reports listed in a table, cross-referenced with a list of users. This supports the rowsource in a listbox so that the user can only select what (s)he is allowed to. Metadata tables such as these are edited in a separate front-end used only by managers.
    A propos the libraries I mentioned, can anybody suggest a source for a list of system functions and variables in Office and Windows. The last time I got such information was in a lovely set of paperback manuals, with Access, Excel, Word having two or three volumes each in its own box and all nested in another box , published in 1992 ! I still have them on my shelves and refer to them occasionally.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jim_from_oz' post='787601' date='05-Aug-2009 04:03']can anybody suggest a source for a list of system functions and variables in Office and Windows.[/quote]
    I think such a list will be hard to find.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I have been identifying functions from sources such as the Object window in VBA, but they do not give you any information. You can get some assistance from the Help system, but trawling around a lot of irrelevant stuff is terribly time-consuming. Googling is a little bit better. At least, you have some control over the search process !

    As to the subject at hand, I am slowly going mad. Here is the situation as at today.

    I attach a PDF with the security groups schema. The backend and frontend each have their own schema, but they are identical. The separation is because other frontends may have access to the same backend in future.

    The various database objects have access permissions assigned to the groups. Thus most reports are assigned to users (i.e. everybody), some others are assigned to Managers(RoastSupervisor is the on-site manager) and some are assigned to Admins (RoastManager is the business owner and my client). I have a test login (RoastUserTest) which should allow me to behave like any other user; i.e printing reports, being refused permission to do anything I am not supposed to, and so forth. The backend is set to have Read Design, Read, Insert, Update and Delete permissions set for Users. I did have it set for all groups, but removed the othere because I thought it might cause a conflict, in view of what was happening (see below).

    Now, whether I log in as RoastManager, RoastSupervisor or RoastUserTest, the frontend will open OK. However, when I attempt any operation which accesses the backend, I get the message that I do not have the permissions to access the backend database. Here is the really interesting thing. If I take the Owner off all of the other groups and give it no access to the backend objects, I can log in as owner to the frontend and access the backend as normal.

    In other words, it looks as if the backend is totally ignoring the security arrangements. Any more ideas, you clever people out there ?
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I can't believe that i have wasted all you clever guys' time on something so simple. To be fair, the way Microsoft thinks can be a bit of a wonderland. My backend consists only of a set of tables and one would think that giving universal permission on the tables would be sufficient. But no. There is another object, and that is the database itself. When you are setting permissions, the drop-down list contains the word Database, though it is the first on the list and the default item is the second on the list, i.e. tables. So, unless you are looking for it, you can easily overlook it. It is necessary to give access permission to the database itself, even though all it is going to find there is universal permission to all of the objects. Doh !

    However, there is one question. I have never ever had to assign database level permission to anyone and hence universal access would appear to be the default. So, WHO TURNED IT OFF ???

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='jim_from_oz' post='787723' date='06-Aug-2009 02:52']However, there is one question. I have never ever had to assign database level permission to anyone and hence universal access would appear to be the default. So, WHO TURNED IT OFF ???[/quote]
    Since the database design goes back at least 10 years, it will probably never be known exactly how that happened. The real question is how the original .mdw file was created, and whether it was properly secured or not. If the security wizard was used to secure the database initially, it may well have turned off the database permission. Or you indicated that there was a lot of legacy code being used to link to the back-end tables - testing that may have done the dirty deed. Which raises the question of why the code is necessary to link to tables? We work with linked tables most of the time, and the only time we typically need to relink is if we are moving from a test environment to a live environment or vice versa. In those cases we find the linked table manager works very nicely as long as you are dealing with Access backend data. Just some food for thought....
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hi, Wendell,

    I have been looking after this system for about three years now off-and-on and while it is very good as a representation of the workflow required, the lack of normalisation and the HUGE reliance on hard coding in VBA is enough to turn anyone into a candidate for the asylum ("They're taking me away, ha, ha !"). Yes, I think there may be something in what you say about the securing of the .mdw file, especially as I strongly suspect that the authors (no longer in business) had set up the system to maintain an ongoing reliance by the client on their services.
    As to why the relinking process is necessary, the program was originally a front-end supporting several businesses through a single call-centre. The program would change dynamically from one backend to another when a different factory was selected. Since the various associated companies now each have their own server (though they still share a common website) I am currently getting rid of the on-demand relinking. I do have a simplified one to upload my stuff to their system because my directory structure isn't the same as the clients - but at least it is only run once after an update. Thanks for your response, anyway - it contains some useful food for thought.

    Regards, Jim

Posting Permissions

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