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

You’ll probably want to provide the customer with a list of users

SELECT DISTINCT("owner") FROM annotations.features;

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:

Example CSV: