How to setup Security for multiple departments and databases
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?