Sharing user tables

If your sites are in different databases and you have a master site, you can use the Intranet Shared User Tables form to share user tables. User maintenance tasks for all sites (maintaining user and group names and permissions, user-group mappings, and application-specific user information) can then be handled from the master site.

When all sites are in the same database (for example, in a multi-tenant cloud environment), the sites are already sharing user tables.

Prerequisites:

You must set up the sites on an intranet and assign a master site to the intranet. For more information about master sites, see Setting Up a Master Site and Shared Tables.
If you have sites in different databases, we strongly recommend that you set up intranet licensing at the master site and at all participating sites before you set up intranet shared user tables.
See Intranet Licensing.
We highly recommended that an administrator back up all of the application databases (from the master and participating non-master sites) before you run this process. These backups can be used to restore the databases in all of the sites to their original state if necessary.
While you are setting up or reverting tables on this form, ensure that no other users are logged into any of the sites where changes will occur.

Note:

If FDA traceability is a requirement, and your environment includes sites that are not in the same database as the master site, do not use the Shared User Tables feature.
User sessions are still managed at individual sites.
For each shared user table, the RowPointer in the master site is no longer unique in other sites, since it is shared with the view. Event processing needs to take this into account, including the InWorkflow column.
Specialized note processing, including copy utilities, might be affected by the creation of these views. The NoteExistsFlag column is set when any note is added in any site. However, with the shared table views, the actual note might not exist in the site that reads the NoteExistsFlag column. In this case, the NoteExistsFlag indicates that one or more notes exist in one or more sites.

To set up the shared user tables:

Log in to the master site for the intranet where you want to share user tables.
On the Intranet Shared User Tables form, select this site's intranet.
In order to make changes using this form, you must be logged into the selected intranet's master site. You can view, but not change, the shared tables information on this form from other sites on the intranet if the master site is replicating the Site Admin category to the other sites.

If a master site has been declared for this site's intranet, the system displays the master site name and a list of the user maintenance tables that can be shared. Not every user maintenance table is listed here, because some tables are not available for sharing.

If no master site has been declared for the selected intranet, the rest of the form is blank and you cannot continue.

Two lists of tables are shown:
- The top grid lists user tables that can be shared. You cannot edit this list. When you select Set up shared user tables, all of the user tables in the top grid are selected for sharing. You can clear the Shared option for some tables, but not all of them, as described below.
  The user tables let you maintain user information, which can include application-specific information such as initials, employee, or multi-site group, user groups, and user/group assignments.
  
  For more information about how sharing of these tables is handled in multiple sites per database vs. one site per database environments, see Setting Up a Master Site and Shared Tables.
- The bottom grid lists tables that contain a column whose base domain is UserNames.UserId or GroupNames.GroupId. The bottom grid is used during the setup of shared user tables to identify the tables and columns that might need to be updated if records that were formerly defined in the Usernames or Groupnames tables in the non-master site are moved to the master site, but with different UserId or GroupId values.
  When you select Set up Shared User Tables, the Update Referenced ID check box for each of these associated tables is selected. This grid is display-only for standard application tables. You can add custom tables that include user or group references at the end of this grid. When the user tables are shared or unshared, the references in the associated tables are updated appropriately.
If you have custom tables that contain a column whose value comes from base domain UserNames.UserId or GroupNames.GroupId, add your custom table and its associated ID column to the Non-Shareable Tables grid. We recommend that your custom tables refer to the Username or Groupname columns, rather than the ID columns, because the distinct list of Usernames and Groupnames across intranet sites is always the same, whether they are stored in shared tables or per site, and therefore no changes are required for data referencing this base domain.
Select Set up shared user tables, which automatically selects Shared for all tables in the top grid and Update Referenced ID for all tables in the bottom grid.

In the top grid, clear the Shared check box for any tables that you do not want to share between sites.

Note: Be aware of how this process works. For the Usernames, GroupNames, UserEmail, UserModules, UserPasswordHistory, UserCalendar, and UserTask tables:

Table names	Select Shared on Intranet Shared User Tables?	Result for one site per database	Result for multiple sites per database
Usernames, GroupNames, UserEmail, UserModules, UserPasswordHistory, UserCalendar, and UserTask	No	Tables are not shared across multiple sites.	Tables are shared for multiple sites within a database, but not across different databases.
Usernames, GroupNames, UserEmail, UserModules, UserPasswordHistory, UserCalendar, and UserTask	Yes	Tables are shared across multiple sites.	Tables are shared across multiple sites and databases.
AccountAuthorizations_mst, user_local_mst, and UserGroupMap_mst	No	Tables are not shared across multiple sites.	Tables are not shared across multiple sites.
AccountAuthorizations_mst, user_local_mst, and UserGroupMap_mst	Yes. You can select each of these tables individually for sharing.	Tables are shared across multiple sites and databases.	Tables are shared across multiple sites and databases.

The sharing process copies any records in a non-master site’s UserNames table to the master site’s table if UserName does not already exist in the master site. If the UserId for a given record does not exist in the master site, the same UserId is kept. If the UserId for a given record does exist in the master site, a new UserId is generated via identity column defaulting. If a user exists in the master site and non-master site with different UserIds, the UserId in the master site is used and the UserId in the non-master site is flagged to be updated. The UserIds in the two sites are mapped via the shared Username.

We have identified the standard tables where UserId and GroupId are used as keys or foreign keys. These tables are included in the Non-shareable Tables Containing References to UserID or GroupID grid. Tables in this grid are updated if there are changes to the UserId, to guarantee referential integrity.

Click Process to copy information from the tables at the other sites to the master site's table, delete the table from the other sites (creating views into the master site tables instead), and regenerate the replication triggers for the other sites. (Removing tables and setting up views does not happen if the non-master site is in the same application database as the master site.)

If you have many sites on this intranet or many users and groups, processing can take a while. The Processing Step area shows the system's progress.

During processing, the system validates link setup between the master site and the using sites of an intranet. If it finds a problem, an error message displays and nothing is processed; fix the link and then click Process again.

The Processed field indicates which tables have been processed. If tables are shared, they are now resident only in the master site's database. After a row on the form is marked as Processed, subsequent "Process" runs will not reprocess that row.

The Status field indicates whether the user tables are shared or not shared.

After processing all the tables and sites, the system regenerates the replication triggers at the master site.
After processing is complete, you must reapply a valid license document on the master site.

Note: This step is not required in a usage-tracking license environment.
In the master site, open the View Management form and regenerate views at the master site, so that the views reflect the changes to table sharing.
In the master site, open the Trigger Management form and regenerate table triggers at the master site.

Note: When you are already sharing user tables, and you add a new site to the current master site's intranet, you might also want to set up the new site to share user tables. To do this:

In the Intranet Shared User Tables form, select Set up per site user tables and click Change Setup Option to change the field label to Set up shared user tables.
Click Process. The status of the shared tables is checked as each site is processed. If sharing has already been set up for a site, no processing occurs for that site, and the process continues with the next site. When it encounters a site that is not already set up, the site is processed.