I have created several Access databases on a shared drive.
Here is the way I currently have it set up. Folder 1 - Shared folder on the network Contains four back-end databases DB1 – data for Program1 DB2 – data for Program2 DB3 – data for Program3 DB4 – holds deleted data for reports Subfolder1 holds the complete front-end programs for the users Program1 Program2 Program3
Each user has a copy of each program on their C:\Drive. Each has: Front-end database Scripts (vbs) to Close one database and open another Replace a front-end database from Subfolder1 if theirs gets corrupted or deleted
I have been tasked to assign privileges to certain individuals to have rights. Admin group with full rights to edit and update all files and folders Front-end users that use the databases
I have given passwords before but have never given rights, so here is my question. Aren’t all these rights given by the Administrator of the network folder, or do I have the ability to assign these rights for each database?
I have each user with their personal FE app in their personal network folder. (no C drive needed)
Then I can issue updates from my version to copy FE to all the users.
If a user's app gets corrupt, I can again copy 1 single FE to their folder.
The tUser table has each individual's rights code.
when the user opens the db, the main menu grabs their 'code' to allow : edit/view/etc.
no need to set all the folder crap.
The preferred way to do this if you are in a high-security environment will depend on whether you want all four DBs to have the same exact setup or whether you have to keep the rights separate. This is what I did when I was working for the U.S. Navy, which is certainly a high-security set-up.
If you want separate security profiles because the users of the 4 DBs do not completely overlap, then you need to do this once for each DB. If you want it to be one grand free-for-all among the users, you can do this just once for the lot. IF you are not sure, then I would recommend once for each DB because it is harder to split them apart after-the-fact once they have been together.
1. Set aside a folder or folders on your share. One for each or one for all. Try to place the folder(s) as near to the drive's root folder as permitted by corporate policy. While I would not recommend using drive-letter mapping, I will use that as an illustration to what I mean.
Each layer of folder adds to the time/complexity of rights traversal. When you actually implement the paths, use UNC mapping: \\Server\Sharename\folder\ for mapping back ends and for launcher icons.
2. Work with the domain administrator to create a domain-level group identifier (or identifiers) for the users of the database. You might want a second identifier or set thereof for the admins to keep them simple. You will want whatever rights are needed for you to directly assign members to groups.
3. Assign the MODIFY (coarse-level) permissions to the files(s) and folder(s) based on the GROUP identifiers for the users, not to the individual users. There is an Advanced option where you can assign nearly two dozen individual permissions but the high-level MODIFY has everything you need for users.
Note also that if your app involves other folders, that if you use a child folder of the place where you put your app file, assigning permissions has the option to propagate those permissions to child files and folders, which you want. AND you want those permissions to be the default for files, which is another permissions option.
4. Assign the FULL CONTROL (coarse-level) rights to the files(s) and folder(s) based on the GROUP identifier for the admins, not to the individual admins. Again, don't bother with detailed individual permissions. Again, don't forget to make the permissions the default for the files and folder and to let them propagate.
5. Add your users to the user group(s) as appropriate. Add your admins to the admin group(s) as appropriate. A person will then be in one or the other of the two groups. Using this scheme it would indeed be possible to have user XYZ a user in one group and an admin in another group, but user ZYX might have the opposite roles. Note also that adding a user to a project's user group is redundant if the user is also an admin, since the MODIFY permissions are a formal subset of the FULL CONTROL permissions.
Note that having FULL CONTROL via a group identifier usually allows you to assign other people to the group. So you can thus add a user once (per group if appropriate) and that is ALL you need to do for new user permissions.
6. Recognize that this requirement:
I have been tasked to assign privileges to certain individuals to have rights.
Admin group with full rights to edit and update all files and folders
Front-end users that use the databases
is actually contrary to the way Windows security works. If you are a user of the database, you need rights to edit and update files in the folder (as well as create and delete) because of the things done to the LOCK files, and they don't always exist so you can't statically assign rights. Has to be default for the folder(s).
7. ALSO recognize that if you have security requirements once a user is in the database, you need to implement something like a switchboard or dispatcher form that identifies the user and determines what functions the user can activate from there. Which means a user should be identified by detailed role in a table, because once you are inside the database, there are no roles that you didn't create for your app. That also means that the actual database features like the navigation pane and other similar things must always be hidden from the users. That is because Access doesn't inherently enforce permissions once you are in the DB. YOU have to enforce them.
The good news is that if there IS a domain, you usually can trust the Environ("Username") function to tell you who that user really is. You don't need a password because the domain's security asked for one when that user logged in. What you are doing in essence is implementing a single-sign-on environment, which is a good thing for places that care about security. (Fewer passwords AND the domain password mechanism is fairly robust compared to anything you might implement in Access on your own.) This is called a "TRUST" relationship - that your Access app "trusts" the domain. If you discuss this with your security wonks in those terms, they will understand and will not be too upset with it.
Like I said earlier, the U.S. Navy did this and allowed my DB app to do exactly as I said.
EVERY form was built from the same template that had buttons for CREATE, REMOVE, SAVE, UNDO, and common functions CLOSE, HELP,and FILE TR (trouble report). But every button was also dynamically enabled at form load based on the user's privileges within the DB. Every recordset was conditioned on a special list of the servers over which any given person had authority. (Total of about a dozen non-overlapping groups for a total of about four dozen users covering 80 projects and maybe 1200 servers.)
So basically, I had the forms with all the required infrastructure and all that mattered was that at form LOAD time, I determined user roles BEFORE I allowed the various controls to show up. They were situational, too. If you clicked CREATE then SAVE and UNDO lit up but CLOSE and CREATE vanished. HELP and FILE TR were the only actual constant controls.
Each template was populated along the top with the command buttons and along the bottom with alert boxes, status boxes, and the user's ID and role information. The controls on the form would change from black on white to Dark Maroon on Pink when there was a change pending for that field. If the user was NOT allowed to touch the field, the motif was dark brown on a kind of peachy/orangey color. The box in focus was dark blue on a nice light pastel blue if it was clean or bright red on pink if dirty. I had other color codes as well.
Since I had common "control support" routines in a general module library and since the template forms had about 50% to 60% of my security infrastructure (plus full-blown editing, error trapping, and event logging), I just copied the template and filled in the blanks. Cost me a bit of time to build the template, but then SAVED me 50% of the work for EACH NEW FORM. Since I had something like two dozen related forms that did various things for each of the major entities of my object model, I used that concept to hell and gone. Made it trivial for updates, though. It was always just one FE file and the user info was available in a security module's declaration area as Public variables, loaded there by the Form_Load routine on the switchboard form.
Doing it that way, I was essentially GUARANTEED a uniform "look and feel" for every form in every part of the application. And I only had four roles: NoAccess (which died when you launched the switchboard), Guest (everything was read-only), SysAdmin (could update the info we were tracking), and DBAAdmin (could tweak things and could close the switchboard without closing the app).
