HCL Connections, external user to internal

We all got the request before, a user has been created as an external user and needs to become internal, or an external consultant has been hired and needs to be changed.

WARNING! I strongly advise not to change an internal user to an external one. This is a security risk as internal users have access to much content by default and the db2 commands I will provide do not guarantee all will be secured if you perform them in the opposite way. The recommended process here is to delete the user, remove all email address references in your database and recreate a user with a new identifier.

How to change form external to internal. I will only discuss the steps needed in the database systems, LDAP etc are not covered. It comes down to changing the state of the user in all relevant tables. The sql statements below are for db2 and will need some changes to work on SQL Server and Oracle. I struggle running the long peopledb commands in a script, have not figured out why. I run them in my favorite database tool dbeaver. Obviously the parameter $UID should be replaced if you run these manually. I will also post the script if you want to run it command line.

Peopledb

connect to peopledb

UPDATE EMPINST.EMPLOYEE SET PROF_MODE = '0' WHERE PROF_UID = $UID

UPDATE EMPINST.EMP_ROLE_MAP SET ROLE_ID = 'employee' WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)
UPDATE EMPINST.SURNAME SET PROF_MODE = '0' WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)

UPDATE EMPINST.PHOTO SET PROF_IMAGE = (SELECT p.PROF_IMAGE FROM EMPINSt.PHOTOBKUP p WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)) WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)

UPDATE EMPINST.PHOTO SET PROF_THUMBNAIL = (SELECT p.PROF_THUMBNAIL FROM EMPINSt.PHOTOBKUP p WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)) WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)

connect reset

Blogs

connect to blogs

UPDATE BLOGS.ROLLERUSER SET ISEXTERNAL = '0' WHERE EXTID = $UID

connect reset

Sncomm

connect to sncomm

UPDATE SNCOMM.MEMBERPROFILE SET ISEXTERNAL = '0' WHERE DIRECTORY_UUID = $UID

connect reset

Files

connect to files

UPDATE FILES.USER SET TYPE = '1' WHERE DIRECTORY_ID = $UID

connect reset

Forum

connect to forum

UPDATE FORUM.DF_MEMBERPROFILE SET ISEXTERNAL = '0' WHERE EXID = $UID

connect reset

Homepage

connect to homepage

UPDATE HOMEPAGE.PERSON SET IS_EXTERNAL = '0' WHERE EXID = $UID

connect reset

Opnact

connect to opnact

UPDATE ACTIVITIES.OA_MEMBERPROFILE SET ISEXTERNAL = '0' WHERE EXID = $UID

connect reset

Wikis

connect to wikis

UPDATE WIKIS.USER SET TYPE = '1' WHERE DIRECTORY_ID = $UID

connect reset

Script

#!/bin/bash
# title                  : Transform External User into Internal
# description            : This script will change the external flag to internal in all member tables
#                          It will fail on several if the user has never logged in. Do not forget to change
#                          the needed things in LDAP and SDI.
#                          the operation.
# author                 : Wannes Rams
# date                   : 11/02/2021
# version                : 1.0
# usage                  : bash external_to_internal.sh "UID"
# notes                  :

#
#Check if correct user to execute script
#
if [ "`whoami`" == "db2inst1" ] ; then

#
### Variables definition
#

. $HOME/sqllib/db2profile
UID = $1

if [ -z $1 ] then

db2 connect to peopledb
db2 UPDATE EMPINST.EMPLOYEE SET PROF_MODE = '0' WHERE PROF_UID = $UID
db2 UPDATE EMPINST.EMP_ROLE_MAP SET ROLE_ID = 'employee' WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)
db2 UPDATE EMPINST.SURNAME SET PROF_MODE = '0' WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)
UPDATE EMPINST.PHOTO SET PROF_IMAGE = (SELECT p.PROF_IMAGE FROM EMPINSt.PHOTOBKUP p WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)) WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)
UPDATE EMPINST.PHOTO SET PROF_THUMBNAIL = (SELECT p.PROF_THUMBNAIL FROM EMPINSt.PHOTOBKUP p WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)) WHERE PROF_KEY IN (SELECT e.PROF_KEY FROM EMPINST.EMPLOYEE e WHERE PROF_UID = $UID)
db2 connect reset

db2 connect to blogs
db2 UPDATE BLOGS.ROLLERUSER SET ISEXTERNAL = '0' WHERE EXTID = $UID
db2 connect reset

db2 connect to sncomm
db2 UPDATE SNCOMM.MEMBERPROFILE SET ISEXTERNAL = '0' WHERE DIRECTORY_UUID = $UID
db2 connect reset

db2 connect to files
db2 UPDATE FILES.USER SET TYPE = '1' WHERE DIRECTORY_ID = $UID
db2 connect reset

db2 connect to forum
db2 UPDATE FORUM.DF_MEMBERPROFILE SET ISEXTERNAL = '0' WHERE EXID = $UID
db2 connect reset

db2 connect to homepage
db2 UPDATE HOMEPAGE.PERSON SET IS_EXTERNAL = '0' WHERE EXID = $UID
db2 connect reset

db2 connect to opnact
db2 UPDATE ACTIVITIES.OA_MEMBERPROFILE SET ISEXTERNAL = '0' WHERE EXID = $UID
db2 connect reset

db2 connect to wikis
db2 UPDATE WIKIS.USER SET TYPE = '1' WHERE DIRECTORY_ID = $UID
db2 connect reset




fi
else
        echo "need to be db2inst1"
fi

Leave a Comment

Your email address will not be published. Required fields are marked *