De-duplicate Mixed Case Usernames

Applies to version 2022.3

From version 2022.3, Alation ends the support for case sensitive usernames. All usernames are treated as case insensitive. For example, usernames like Alvin.Lucas@alationcatalog.com and alvin.lucas@alationcatalog.com are considered identical usernames.

Note

In releases prior to 2022.3, case sensitive usernames were supported. Usernames differing in the use of lowercase or uppercase letters were considered different usernames. For example, usernames like Alvin.Lucas@alationcatalog.com and alvin.lucas@alationcatalog.com were considered two different usernames.

After updating the Alation application to 2022.3, make sure that your Alation instance does not have any duplicate mixed case usernames created in previous releases. Note that the owner of the duplicate accounts will not be able to log in until their accounts are de-duplicated.

Alation provides a one-off script with release 2022.3 that can be used to find and suspend duplicate user accounts. The script performs the following operations:

  • Prepares a list of all usernames that are duplicates from the standpoint of case sensitivity.

  • Allows you to specify which usernames must be retained and which must be suspended.

  • Suspends the duplicate user accounts based on your input.

    Important

    The script will require an action. If it finds duplicate usernames, you will be prompted to provide information which of them are valid and should be retained and which are duplicate and can be suspended.

    If you are not sure about what accounts to retain or suspend, you will have an option to exit from the script and re-initiate it later when you have the required information.

Step 1: Generate a List of Duplicate Usernames

Initially, run the script to generate a list of duplicate usernames. To run the script:

  1. Use SSH to connect to the Alation server.

  2. Enter the Alation shell.

    sudo /etc/init.d/alation shell
    
  3. Change the user to alation.

    sudo su alation
    
  4. Go to /opt/alation/django.

    cd /opt/alation/django
    
  5. Run the script with the following command:

    python ./rosemeta/one_off_scripts/generate_and_remove_dup_user_accounts_with_mixed_case.pyc
    
  6. The output will contain a prompt to specify one of the three options:

    • G or g – Generate a CSV file listing the duplicate user accounts with mixed case usernames.

    • U or u – Specify the CSV file name which will be used to update the user accounts.

    • T or t – Terminate and exit.

    Type G or g and press Enter to generate the list of duplicate usernames.

  7. The script will analyze the usernames on your instance. If any mixed case duplicates are found, it will generate a CSV file in the directory /data1/tmp/ inside the Alation chroot. If no duplicates are found, you’ll see the message No duplicate user accounts with mixed case usernames. In the latter case, no further action is required.

  8. If the script identifies some duplicate accounts and generates a CSV file, you’ll see a message similar to the following: Unique file generated with name: /data1/tmp/duplicate_users_926b6052-f2e7-4976-ac21-e1ac15993aa3.csv.

  9. The script will display the editing instructions for the generated file and the prompt Please provide confirmation that the file is updated appropriately? [Y/n]. You can leave the script waiting for the input and edit the file right now in a separate Terminal window (or using other tools) and then return to the window with the script and continue.

Alternatively, you can type n to exit the script, take as much time as needed to edit the CSV file, and then rerun the script with the U (update) option.

  1. If you chose to exit from the script, exit from the user alation:

    exit
    
  2. Exit from the Alation shell:

    exit
    

Step 2: Edit the CSV File

The script generates a CSV file with a name similar to duplicate_users_926b6052-f2e7-4976-ac21-e1ac15993aa3.csv in the directory /data1/tmp.

To edit the file:

  1. Open the file in an editor of your choice. For example, if you prefer to edit it directly in the Terminal, open a new Terminal window, enter the Alation shell, go to /data1/tmp and use the vi editor to open the file.

    The screenshot below shows an example of a file generated by the script.

    ../../_images/Deduplicate_Mixed_Case_User_List.png

    The file contains mixed case duplicate usernames organized by group. Each group has a numeric ID and includes usernames that only differ in the use of uppercase and lowercase letters.

    The file includes the following columns:

    • SN – Serial number of a record in the file.

    • Username – Username of the Alation user.

    • Email – Email of the Alation users.

    • Action – Specifies the action to take on this row. The initial value is RETAIN/SUSPEND.

    • Group – ID of a group (set of users who have duplicate case insensitive usernames).

  2. For each group of the duplicates, you need to specify which username to retain and which usernames to suspend to eliminate the duplicates. You only need to edit the Action values.

    • Use RETAIN for the username to retain. In each group of duplicating usernames, you can only retain one username.

    • Use SUSPEND for the duplicate usernames. The duplicate usernames will be suspended.

    The screenshot below shows an example of an edited file.

    ../../_images/Deduplicate_Mixed_Case_Edited_List.png
  3. Save the changes.

  4. If you were editing the file outside of the Alation server, copy the updated file to /data1/tmp inside the Alation chroot to make it available to the script.

Step 3: Update the User Accounts

Use the appropriate set of steps depending on whether or not you previously exited from the script.

Script is Waiting for Input

If you still have the Terminal window with the script waiting for your input:

  1. Go back to the Terminal window that has the script in progress. The last prompt says: Please provide confirmation that the file is updated appropriately? [Y/n].

  2. Type Y in response to the last prompt.

  3. The script will suspend the duplicate accounts according to the edits you made in the CSV file. Note that the suspended accounts will be changed to the following format: <username>_uis_suspend_<uuid4>, for example: Alvin.Lucas@alationcatalog.com_uis_suspend_26b6052-f2e7-4976-ac21-e1ac15993.

  4. Wait for the success message that will be similar to the following:

    Total number of users got updated with temp username and suspended: 2
    Successfully completed.
    
  5. If you have no other tasks in the Alation shell, exit the shell.

    exit
    

    The screenshot below illustrates this scenario:

    ../../_images/Deduplicate_Mixed_Case_Script_Waiting.png

Script Has Been Exited

Run the script again, choosing the U option when prompted:

  1. Use SSH to connect to the Alation server.

  2. Enter the Alation shell.

    sudo /etc/init.d/alation shell
    
  3. Change the user to alation.

    sudo su alation
    
  4. Go to /opt/alation/django.

    cd /opt/alation/django
    
  5. Run the script with the following command:

    python ./rosemeta/one_off_scripts/generate_and_remove_dup_user_accounts_with_mixed_case.pyc
    
  6. When prompted, provide the U option. This option is for updating the usernames after editing the file.

  7. The script will suspend the duplicate accounts according to the edits you made in the CSV file. Note that the suspended usernames will be changed to the following format: <username>_uis_suspend_<uuid4>, for example: Alvin.Lucas@alationcatalog.com_uis_suspend_26b6052-f2e7-4976-ac21-e1ac15993.

  8. Wait for the success message.

  9. If you have no other tasks in the Alation shell, exit the shell.

    exit