10 Replies Latest reply on Sep 12, 2014 1:38 PM by andrew.nield

    How to setup Security for multiple departments and databases

    andrew.nield

      Title

      How to setup Security for multiple departments and databases

      Post

      Say you have 3 departments/groups (G1, G2, G3) and each has write access to table(s) (T1, T2, T3)

      Users can be in any combination of Groups but as far as I know they can only be authorised in one group for FM security so you would need security groups and access as follows:

      G1 (data entry access to T1, read access to T2, T3)

      G2 (data entry access to T2, read access to T1, T3)

      G3 (data entry access to T3, read access to T1, T2)

      G1G2 (data entry access to T1, T2 read access to T3)

      G1G3 (data entry access to T1, T3 read access to T2)

      G2G3 (data entry access to T2, T3 read access to T1)

      G1G2G3 (data entry access to T1, T2, T3)

      And if that isn't complicated enough, suppose you have 8 departments (D1-D8) and 8 sets of table(s) (T1-T8) and the tables from different departments are related to each other and combined on layouts...

      How would you set up the security and the database?

      8 separate databases would mean duplicate layouts/scripts and setting up multiple external data sources links between the 8 databases

      Would you have one database with all the tables? (if so how would the security work?)

      Would you have 8 separate databases plus another database that contains all the layouts and scripts and connects the other 8 as external data sources? (links easier as all in one database, security easier as you just order the Data entry group above the read groups in each database) sounds less complicated and easier to maintain is this the way to set it up?

      Something else?

      Thanks

        • 1. Re: How to setup Security for multiple departments and databases
          FentonJones

          Have you looked at FileMaker -> Manage -> Security ?
          Then Privilege Sets. You can create Privilege Set (PS) for each one of your G1, G2, etc.. 

          Each PS can be set differently for each Table (in the Records:), whether they have View, Edit, Create, Delete. You can (if you want), get into each Field Access (modifiable, view only, no access).

          Then there's Layout access, and even "Records via this layout" (where you can stop then from modifying, even if they have access to do so elsewhere {newer used it myself, but used practically everything else, sometime} ).

          Then Value Lists.

          Then Scripts. This is a good one to all no access to those complex geeky ones that are not used escape by higher levels). You can say which scripts they see in the Scripts menu (if you show that at all).

          Then there is Custom Menus, a completely different tool. Not my favorite, but sometimes very useful, to keep many things from even showing; or to cause command lines to run YOUR script, instead of the default. Best to learn on a test file. NEVER "play" with the live file!

          Regarding files. Many "modern" people have very few files. They put most tables in one file. That is a good, in most cases. However, many of us "old" folks are still making fixes to multi-file databases. What we learned, long ago, was that scripts can create a new "staff" persons access in all the files, specifying the correctly Privilege Set.

          A Privilege Set must be set up for each Table. This is true whether all tables are in one file or multiple files. Yes, it's slightly faster to do it in file, I suppose. But it's still a step by step on each table process.

          This does NOT mean however that you should use dupe type files. I've been true that (on a short term basis). The "do everything twice" will become very annoying ofter a short while.

          This my script to add a new person to every file. It calls scripts in the other files, each of which is much like the "If" steps part. Notice: I used a field ("Privileges") to specify the "name" of the desired Privilege Set, then test for it. Hence you want to get most of those figured out well before doing this (they can be changed, but must be fixed in all files).

          There is also a script to Delete a person from Security. It is simpler, as it does not care what PS they are. The "Password" can either be "set" (though a script can change it later); or you can use the option to use a generic one, but ask them to specify it on first login (which is more secure). 

           

          • 2. Re: How to setup Security for multiple departments and databases
            andrew.nield

            Yes I have looked at privilege sets, and I can assign a privilege set to a single group, but the users need to be in multiple groups/projects.

            Can a user be in multiple groups or in multiple privilege sets?

            • 3. Re: How to setup Security for multiple departments and databases
              philmodjunk

              It will be a bit tedious to set them up. You'll need one privilege set for each possible group combination.

              If you have multiple database files, you may want to research "External server authentication" as a way to better work with setting permissions for multiple files.

              If your different users need access to only certain groups of records within a table, see: "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a description of how to set this up.

              • 4. Re: How to setup Security for multiple departments and databases
                andrew.nield

                Thanks Phil, yes I am using external authentication, each user is in all the AD groups that they need to be in and my thinking is that each database should give write access for it's own fm group and read access to another fm_user group that all the users are in.

                so every Project_X database gives write access to group fm_project_X and read access to group fm_read.

                So for example user bob is in groups fm_project_1, fm_project_2, fm_project_6 fm_project_10 and fm_user.  He therefore gets write access to projects 1,2,6,10 and read access to everything else.

                The application database that contains all user interface would then have all the project databases as external data sources.  This is the only database that the users will open and log in to.

                Is this a recommended setup or if there is a better way?

                 

                 

                • 5. Re: How to setup Security for multiple departments and databases
                  philmodjunk

                  External authentication is not something I've had to set up so I am at a disadvantage offering any additional assistance. But as I understand it, External authorization still results in your user opening the file with an account name, password and privilege set, it's just that they didn't have to use an extra log in just for Filemaker to get it open.

                  If I am correct in that, it still comes down to what privilege set is assigned to the account that opened the file and what options it permits. That then returns us full circle to my original post where I indicated that each combination of possible access permissions on your file needs a different privilege set, you can't assign multiple privilege sets to the same account.

                  • 6. Re: How to setup Security for multiple departments and databases
                    andrew.nield

                    With AD or OD external authentication the authentication is also done on group membership.

                    so you can set up a FM login for an AD user e.g. bob or for AD group membership e.g. finance.

                    • 7. Re: How to setup Security for multiple departments and databases
                      philmodjunk

                      Yes, but then that user is presented with an opened database file. And it can only be opened under a specific account name with a single privilege set. That account name might identify the group rather than the individual, but you still have a single set of privilege set options controlling their access permissions within your file.

                      • 8. Re: How to setup Security for multiple departments and databases
                        andrew.nield

                        Yes but each external databases is opened with write permission if the user is in the correct group for that database, i.e. the finance database will be opened with write permissions if the user is in the finance group, the project3 database will be opened with write permission if the user is in the project3 group.

                        Each database only has two user accounts defined: it's own group (write) plus the global group that every user is in (read).

                        I've tested this and the security works correctly, once logged into the main database you have the correct permissions (write or read) on each of the external datasource databases depending on the AD groups that you are in.

                        • 9. Re: How to setup Security for multiple departments and databases
                          philmodjunk

                          Which is essentially what I said, each file is opened under one account name with one privilege set for that user. Yes, the privilege set will be different for each file.

                          Apologies, but your initial posts left me with the idea that you were trying to assign two privilege sets to the same user for the same file and that's not possible.

                          • 10. Re: How to setup Security for multiple departments and databases
                            andrew.nield

                            > Apologies, but your initial posts left me with the idea that you were trying to assign two privilege sets to the same user for the same file and that's not possible.

                            That was part of my question, to find out if I could do that... smiley

                            Thanks for your help.