Handle Annotations with SSO

When the authentication method changes in iShare GIS be mindful that the user will be known by a different identifier. For example under ADFS a user might be known as user@mycouncil. Under Azure they might appear as user@mycouncil.gov.uk. Any annotations that they have created in iShare GIS will need to be adjusted to the new format.

iShare GIS v5.8 and below

If the format can be adjusted:

UPDATE annotations.features SET username = replace(username, '@mycouncil', '@mycouncil.gov.uk');
UPDATE annotations.layers SET "owner" = replace("owner", '@mycouncil', '@mycouncil.gov.uk');
UPDATE annotations.layers SET "lasteditedby" = replace("lasteditedby", '@mycouncil', '@mycouncil.gov.uk');
UPDATE annotations.layers_access SET username = replace(username, '@mycouncil', '@mycouncil.gov.uk');

If a lookup is required here’s an example:

Ask for a csv file containing the domain user account & the email address - which should be the name of the user after Azure AD integration.

-- create a table
CREATE TABLE annotations_lookup
(
  account character varying,
  email character varying
)

-- import the data
COPY annotations_lookup from E'E:\\Temp\\annotations_users.csv' WITH DELIMITER ',' CSV HEADER QUOTE '"';

-- update the annotations
UPDATE annotations.features SET username = annotations_lookup.email FROM annotations_lookup 
WHERE features.username = annotations_lookup.account;
UPDATE annotations.layers SET "owner" = annotations_lookup.email FROM annotations_lookup
WHERE layers."owner" = annotations_lookup.account;
UPDATE annotations.layers SET "lasteditedby" = annotations_lookup.email FROM annotations_lookup
WHERE layers."lasteditedby" = annotations_lookup.account;
UPDATE annotations.layers_access SET username = annotations_lookup.email FROM annotations_lookup
WHERE layers_access.username = annotations_lookup.account;

iShare GIS v6.0 and above

The name of the annotation tables have changed in v6.0, the following assumes a lookup table with account and email columns has been loaded into a table called ishare._gis_anno_user_lookup_temp within the iShareData database via a CSV Spatial Data Workflow Task:

UPDATE ishare._gis_anno_features features SET username = annotations_lookup.email FROM ishare._gis_anno_user_lookup_temp annotations_lookup WHERE features.username = annotations_lookup.account;

UPDATE ishare._gis_anno_layers layers SET "layerowner" = annotations_lookup.email FROM ishare._gis_anno_user_lookup_temp annotations_lookup WHERE layers."layerowner" = annotations_lookup.account;

UPDATE ishare._gis_anno_layers layers SET "lasteditedby" = annotations_lookup.email FROM ishare._gis_anno_user_lookup_temp annotations_lookup WHERE layers."lasteditedby" = annotations_lookup.account;

UPDATE ishare._gis_anno_layers_access layers_access SET username = annotations_lookup.email FROM ishare._gis_anno_user_lookup_temp annotations_lookup WHERE layers_access.username = annotations_lookup.account;

Example CSV: