
Until now, it has not been possible to obtain an object reference for users and groups in SFOS (v20.0.x). In other words: Where is the user or group used in the configuration. This CLI script provides a remedy.
Update 21.02.2024: Script updated to version 1.1
Introduction
With SFOS 20.0.0 Sophos has finally introduced the possibility to display the references of host, FQDN and service objects in the configuration. Unfortunately, this option was not provided comprehensively. It is not possible to display the object references of user names and groups. If you cannot delete an object, this is still the case: Search and find. Too bad...
So I sat down for a few hours and analyzed the database. Et voilà: My self-developed script for the advanced shell is ready. With its help, you can quickly find out where the user or group is still configured.
For host, FQDN and service objects you must have version >=20.0.0 installed. Here the function is integrated in the GUI.
The script does not change any configuration. It accesses the database read-only and identifies the locations where the user or group being searched for has been configured.
Update 10/2024 / SFOS 21
Mit SFOS 21 hat Sophos noch mal nachgelegt. Allerdings fehlt weiterhin die Möglichkeit der Objektreferenz bei den Benutzern. Too bad...
Give it a try!
Compatibility
My script is 100% compatible with SFOS 19.5.x to 21.0.0. The versions before and after will certainly also work. Nevertheless, the script refuses to execute. If you still want to run it, you must set the "-i" parameter as described below. Correct output is then without guarantee.
Versions
- 1.0 (2023) Initial release
- 1.1 (02/2024) Output now shows which configurations must be removed before deletion with a (*)
Using the script
The script requires few parameters. If it is on the firewall, you can specify the following parameters:
sh /tmp/userlookup.sh -h
sh /tmp/userlookup.sh -q Benutzername
sh /tmp/userlookup.sh -q Gruppenname
sh /tmp/userlookup.sh -q "Name mit Leerzeichen"
sh /tmp/userlookup.sh -i -q Abfrage
Download
curl -s -L -o /tmp/userlookup.sh https://it-tech.wiki/sdc_download/2690/?key=tn9tt0ol2eqccogdhc6elc9czebl2j && echo -en "\n\nUse \"sh /tmp/userlookup.sh -h\" to get help for first use\n\n"
Source code
#!/bin/sh
####################################
#
# Sophos Firewall (XG/XGS) - SFOS 19.x-20.x and hopefully above
# Script to get information where a user or group is used in configuration
#
# Credits:
# Sebastian Mies ([email protected])
# I am not a professional programer. But if it works - its enough for me :)
# Feedback and wishes are welcome at my mail address!
# Feel free to share the script, but please do not reshare it on any public webserver!
# To share public please use this link: https://it-tech.wiki/vt3e
#
#
# License:
# Free for any use (with no warrenty)
#
# Version:
# 1.0 20231027 Initial Release
# 1.1 20240221 Added (*) marker for the dependency when deleting
#
#####################################
# Variables
version="1.1" # version of this script
compatibelfirmware_pattern="(19\.5\.|20\.0\.)" # versions this script is compatibel to (regular expression)
compatibelfirmware_text="19.5.x, 20.0.x" # versions this script is compatibel to (text)
ignoreversion="false" # set to "true" to ignore compatibel version or use -i option
# Output modifications for "echo"
# more colors: https://ansi.gabebanks.net/?ref=linuxhandbook.com
NOCOLOR='\033[0m'
buCyan='\033[36;4;1m' # Cyan underline bold
bCyan='\033[1;36m' # Cyan bold
bGreen='\033[1;32m' # Green bold
bRed='\033[1;31m' # Red bold
bYellow='\033[1;33m' # Yellow bold
buYellow='\033[33;4;1m' # Yellow bold underline
uYellow='\033[1;33;4m' # Yellow underline
POS40='\033[40G' # cursor to position 40
Red='\033[1;31m' # Red bold
Green='\033[1;32m' # Green bold
Yellow='\033[1;33m' # Yellow bold
# Print HELP
Help() {
echo -en "${Yellow}Script to get a information where a user or group is used in configuration on a Sophos Firewall (XG/XGS)\n\n"
echo -en "Version ($version) - works for SFOS 19.5 to 20.0 (older versions should work too)\n\n"
echo -en "License and usage: Free for any use (with no warrenty)\n"
echo -en "Feedback welcome at: [email protected]\n\n"
echo -en "${uYellow}Parameter for using the Script:${NOCOLOR}\n\n"
echo -en "${Yellow}Syntax: ${0##*/} [-h|q|i]\n"
echo -en "options:\n"
echo -en "-h ${POS34}Print this help.\n"
echo -en "-q ${POS34}\"username|groupname\" to search for in configuration (case insensitive).\n"
echo -en "-i ${POS34}Ignore SFOS version.\n\n"
echo -en "${uYellow}Examples:${NOCOLOR}${Yellow}\n"
echo -en "sh ${0##*/} -q username\n"
echo -en "sh ${0##*/} -q groupname\n"
echo -en "sh ${0##*/} -q \"name with space\"\n"
echo -en "sh ${0##*/} -i -q username\n"
echo -en "\n${NOCOLOR}"
exit 0
}
# reading parameters
while getopts :q:hi options; do
case $options in
h) Help;;
i) ignoreversion="true";;
q) query=$OPTARG;;
\?) echo -en "${Red}ERROR: Unknown option provided: $0 ${NOCOLOR}\n"
Help;;
esac
done
# Checking current firmware
currentfirmware=$(cat /boot/sfloader.version)
if ! (echo "$currentfirmware" | grep -E "$compatibelfirmware_pattern" > /dev/null) && (! $ignoreversion); then
echo -en "${bRed}ERROR: Firmware version not supported. Supported versions: \"$compatibelfirmware_text\". To run script and ignore version use -i option.${NOCOLOR}\n\n"
exit 0
fi
# argument check
echo -en "\n"
uname_length=${#query}
if [ "$query" == "" ]; then
echo -en "${bRed}ERROR: -q (username|groupname) argument not provided. For more information use -h option to get help ${NOCOLOR}\n"
exit 0
#else
# if [ $uname_length -gt 50 ]; then
# echo -en "${bRed}ERROR: Username/Groupname too long. Maximum allowed length on SFOS is 50 characters. You provided: $uname_length ${NOCOLOR}\n"
# exit 0
# else
# echo -en "${bGreen}Will search now for username \"$query\" in configuration ...${NOCOLOR}\n\n"
# fi
fi
echo -en "${bYellow}${buYellow}INFORMATION:${NOCOLOR}${bYellow}\nThe configurations marked with an ${bCyan}(*)${NOCOLOR}${bYellow} usually prevent the deletion of an object if the specified user name or user group is in use.\n\n${NOCOLOR}"
# Looking for user or group id
userid=$(psql -U nobody -d corporate -t -A -c "select userid from tbluser where username=LOWER('$query');")
usertype=$(psql -U nobody -d corporate -t -A -c "select usertype from tbluser where username=LOWER('$query');")
if [ -n "$userid" ]; then
echo -en "${bYellow}UserID/GroupID:${NOCOLOR}${POS40}${bGreen}$userid "
if [ $usertype = 1 -o $usertype = 5 -o $usertype = 7 ]; then
echo -en "(type: user)${NOCOLOR}\n\n"
groupid=$(psql -U nobody -d corporate -t -A -c "select groupid from tbluser where userid='$userid';")
if [ -n "$groupid" ]; then
usergroup=$(psql -U nobody -d corporate -t -A -c "select username from tbluser where userid='$groupid';")
echo -en "${bYellow}Groupname ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}$usergroup${NOCOLOR}\n\n"
fi
else
if [ $usertype = 3 -o $usertype = 4 ]; then
echo -en "(type: group)${NOCOLOR}\n\n"
else
echo -en "(type: unknown)${NOCOLOR}\n\n"
fi
fi
else
echo -en "${bRed}ERROR: No user or group with name \"$query\" found.${NOCOLOR}\n\n"
exit 0
fi
# Looking for user in firewall rules
count_fwruleid=$(psql -U nobody -d corporate -t -c "select count(*) from tblfwuser where userid='$userid';")
if [ $count_fwruleid -gt 0 ]; then
fwruleid=$(psql -U nobody -d corporate -t -A -R "," -c "select fwruleid from tblfwuser where userid='$userid';")
counter=0
for i in ${fwruleid//,/ }
do
fwrulename=$(psql -U nobody -d corporate -t -A -c "select name from tblfirewallrule where ruleid='$i';")
counter=$((counter+1))
if [ $counter -gt 1 ]; then
echo -en "${POS40}${bGreen}$fwrulename (Rule ID: $i)${NOCOLOR}\n"
else
echo -en "${bYellow}FW rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}$fwrulename (Rule ID: $i)${NOCOLOR}\n"
fi
done
echo -en "\n"
else
echo -en "${bYellow}FW rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bRed}${POS40}none${NOCOLOR}\n\n"
fi
# Looking for user in tls rules
count_tlsruleid=$(psql -U nobody -d corporate -t -c "select count(*) from tbltlsrule_src_user_rel where user_id='$userid';")
if [ $count_tlsruleid -gt 0 ]; then
tlsruleid=$(psql -U nobody -d corporate -t -A -R "," -c "select tls_rule_id from tbltlsrule_src_user_rel where user_id='$userid';")
counter=0
for i in ${tlsruleid//,/ }
do
tlsrulename=$(psql -U nobody -d corporate -t -A -c "select name from tbltlsrule where id='$i';")
counter=$((counter+1))
if [ $counter -gt 1 ]; then
echo -en "${POS40}${bGreen}$tlsrulename (Rule ID: $i)${NOCOLOR}\n"
else
echo -en "${bYellow}TLS rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}$tlsrulename (Rule ID: $i)${NOCOLOR}\n"
fi
done
echo -en "\n"
else
echo -en "${bYellow}TLS rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bRed}${POS40}none${NOCOLOR}\n\n"
fi
# Looking for user in web rules
count_policyrulegroupid=$(psql -U nobody -d corporate -t -c "select count(*) from tblsecpolicyruleuserrel where userid='$userid';")
#echo $count_policyrulegroupid
if [ $count_policyrulegroupid -gt 0 ]; then
policyrulegroupid=$(psql -U nobody -d corporate -t -A -R "," -c "select policyrulegroupid from tblsecpolicyruleuserrel where userid='$userid';")
counter=0
rulenames=""
for i in ${policyrulegroupid//,/ }
do
counter=$((counter+1))
securitypolicyid=$(psql -U nobody -d corporate -t -A -c "select securitypolicyid from tblsecpolicywebcategoryrel where policyrulegroupid='$i';")
securitypolicyname=$(psql -U nobody -d corporate -t -A -c "select securitypolicyname from tblsecuritypolicy where securitypolicyid='$securitypolicyid';")
if [ $counter -gt 1 ]; then
rulenames=$rulenames","$securitypolicyname
else
rulenames=$rulenames$securitypolicyname
fi
done
echo -en "${bYellow}Web rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bGreen}"
echo "$rulenames" | awk -F',' '{
for (i = 1; i <= NF; i++) {
gsub(/^ */, "", $i); # Entfernen von führenden Leerzeichen
gsub(/ *$/, "", $i); # Entfernen von abschließenden Leerzeichen
word_count[$i]++;
}
}
END {
for (word in word_count) {
printf "\033[40G%s\"%s\" (%d times)\n", "", word, word_count[word]
}
}'
echo -en "${NOCOLOR}\n"
else
echo -en "${bYellow}Web rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bRed}${POS40}none${NOCOLOR}\n\n"
fi
# Looking for user in web rules override
policyoverride=$(psql -U nobody -d corporate -t -A -c "select value from tblconfiguration where key='override_policy_users';")
if echo "$policyoverride" | awk -v num="$userid" -F',' '{for (i=1; i<=NF; i++) if ($i == num) exit 0; exit 1}'; then
echo -en "${bYellow}Web rule policy override:${NOCOLOR}${bGreen}${POS40}Set${NOCOLOR}\n\n"
else
echo -en "${bYellow}Web rule policy override:${NOCOLOR}${bRed}${POS40}Not set${NOCOLOR}\n\n"
fi
# Looking for user in Hotspots
count_hotspotid=$(psql -U nobody -d corporate -t -A -c "select count(*) from tbluserrel where userid='$userid';")
if [ $count_hotspotid -gt 0 ]; then
hotspotid=$(psql -U nobody -d corporate -t -A -R "," -c "select hotspotid from tbluserrel where userid='$userid';")
counter=0
hotspotnames=""
for i in ${hotspotid//,/ }
do
counter=$((counter+1))
tblhotspotname=$(psql -U nobody -d corporate -t -A -c "select name from tblhotspot where id='$i';")
if [ $counter -gt 1 ]; then
hotspotnames=$hotspotnames","$tblhotspotname
else
hotspotnames=$hotspotnames$tblhotspotname
fi
done
echo -en "${bYellow}Hotspots ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bGreen}"
echo "$hotspotnames" | awk -F',' '{
for (i = 1; i <= NF; i++) {
printf "\033[40G%s\n", $i
}
}'
echo -en "${NOCOLOR}\n"
else
echo -en "${bYellow}Hotspots ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bRed}${POS40}none${NOCOLOR}\n\n"
fi
# Looking for SMTP relay
count_authenticatedrelay=$(psql -U nobody -d corporate -t -A -c "select count(*) from tblauthenticatedrelay where authuserid='$userid';")
if [ $count_authenticatedrelay -gt 0 ]; then
echo -en "${bYellow}SMTP relay ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}Set${NOCOLOR}\n\n"
else
echo -en "${bYellow}SMTP relay ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi
# Looking for user in WAF authentication policy
count_wafauth=$(psql -U nobody -d corporate -t -A -c "select count(*) from tblwafauth_profile_user_rel where userid='$userid';")
if [ $count_wafauth -gt 0 ]; then
wafauthid=$(psql -U nobody -d corporate -t -A -R "," -c "select profileid from tblwafauth_profile_user_rel where userid='$userid';")
counter=0
wafauthnames=""
for i in ${wafauthid//,/ }
do
counter=$((counter+1))
tblauthnames=$(psql -U nobody -d corporate -t -A -c "select name from tblwafauth_profile where id='$i';")
if [ $counter -gt 1 ]; then
wafauthnames=$wafauthnames","$tblauthnames
else
wafauthnames=$wafauthnames$tblauthnames
fi
done
echo -en "${bYellow}WAF auth. policys ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bGreen}"
echo "$wafauthnames" | awk -F',' '{
for (i = 1; i <= NF; i++) {
printf "\033[40G%s\n", $i
}
}'
echo -en "${NOCOLOR}\n"
else
echo -en "${bYellow}WAF auth. policys ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}None${NOCOLOR}\n\n"
fi
# Looking for user in remote access IPsec
count_raipsec=$(psql -U nobody -d corporate -t -A -c "select count(*) from tblxauthusers where userid='$userid' and usertype='u';")
if [ $count_raipsec -gt 0 ]; then
echo -en "${bYellow}Remote access IPSec ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}set${NOCOLOR}\n\n"
else
echo -en "${bYellow}Remote access IPSec ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi
# Looking for user in remote access SSLVPN
sslvpnid=$(psql -U nobody -d corporate -t -A -R "," -c "select sslvpnpolicy from tbluser where userid='$userid';")
if [ -n "$sslvpnid" ]; then
sslvpnname=$(psql -U nobody -d corporate -t -A -R "," -c "select name from tblsslvpnpolicy where id='$sslvpnid';")
echo -en "${bYellow}Remote access SSLVPN:${NOCOLOR}${POS40}${bGreen}$sslvpnname${NOCOLOR}\n\n"
else
echo -en "${bYellow}Remote access SSLVPN:${NOCOLOR}${POS40}${bRed}None${NOCOLOR}\n\n"
fi
# Looking for user in remote access Clientless SSLVPN
clsslvpnid=$(psql -U nobody -d corporate -t -A -R "," -c "select clientlesspolicy from tbluser where userid='$userid';")
if [ -n "$clsslvpnid" ]; then
clsslvpnname=$(psql -U nobody -d corporate -t -A -R "," -c "select name from tblsslvpnpolicy where id='$clsslvpnid';")
echo -en "${bYellow}Remote access Clientless SSLVPN:${NOCOLOR}${POS40}${bGreen}$clsslvpnname${NOCOLOR}\n\n"
else
echo -en "${bYellow}Remote access Clientless SSLVPN:${NOCOLOR}${POS40}${bRed}None${NOCOLOR}\n\n"
fi
# Looking for user in SD-WAN policy
sdwanid=$(psql -U nobody -d corporate -t -A -R "," -c "select policyrouteid from tbluser_pr_rel where userid='$userid';")
if [ -n "$sdwanid" ]; then
counter=0
sdwanames=""
for i in ${sdwanid//,/ }
do
counter=$((counter+1))
tblsdwanames=$(psql -U nobody -d corporate -t -A -R "," -c "select name from tblpolicyroute where id='$i';")
if [ $counter -gt 1 ]; then
sdwanames=$sdwanames","$tblsdwanames
else
sdwanames=$sdwanames$tblsdwanames
fi
done
echo -en "${bYellow}SD-WAN policys ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bGreen}"
echo "$sdwanames" | awk -F',' '{
for (i = 1; i <= NF; i++) {
printf "\033[40G%s\n", $i
}
}'
echo -en "${NOCOLOR}\n"
else
echo -en "${bYellow}SD-WAN policy ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}None${NOCOLOR}\n\n"
fi
# Looking for user in OTP
count_otp=$(psql -U nobody -d corporate -t -A -c "select count(*) from tblotp where userid='$userid';")
if [ $count_otp -gt 0 ]; then
echo -en "${bYellow}OTP:${NOCOLOR}${POS40}${bGreen}set${NOCOLOR}\n\n"
else
echo -en "${bYellow}OTP:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi
# Looking for user in anonymization
count_anonymization=$(psql -U nobody -d corporate -t -A -c "select count(*) from tbluser where userid='$userid' and authrole='2';")
if [ $count_anonymization -gt 0 ]; then
echo -en "${bYellow}Anonymization ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}set${NOCOLOR}\n\n"
else
echo -en "${bYellow}Anonymization ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi
# Looking for user in anonymization exception
count_anonymizationex=$(psql -U nobody -t -A -d corporate -c "select count(*) from tbldeanonymizationinfo where LOWER(value)=LOWER('$query');")
if [ $count_anonymizationex -gt 0 ]; then
echo -en "${bYellow}Anonymization exception ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}set${NOCOLOR}\n\n"
else
echo -en "${bYellow}Anonymization exception ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi
Screenshots

Danke für das Script!
Eine schöne Ergänzung zum neuen Objekt-Lookup von SFOS 20.x wo leider nur Hosts und Dienste gehen…
Hallo Werner,
danke für Dein Feedback! 🙂
Gruß,
Sebastian
Hi,
erst einmal danke für das tolle Script. Funktioniert soweit gut bei mir.
Aber hier fehlen doch Stellen an denen der Benutzer hinterlegt, oder seh ich da was falsch? Ich sehe zum Beispiel nicht L2TP bei der Prüfung.
Gruß,
Dennis
Hallo Dennis,
schön, dass Dir das Script gefällt. 😀
Ja, es ist richtig was du festgestellt hast. Das Script zeigt nicht alle Stellen auf an denen ein Benutzer oder eine Gruppe hinterlegt ist. Der Fokus lag hier erst einmal an den Stellen, die bei einer Löschung relevant sind. Sprich: wo muss der Benutzer oder die Gruppe aus den Einstellungen gelöscht werden, sodass man den Benutzer oder die Gruppe auch löschen kann.
Die anderen Einstellungen wie zum Beispiel L2TP werden nicht berücksichtigt beim Löschen. Ist hier noch ein Eintrag drin, wird dieser einfach entfernt ohne eine Fehlermeldung in der GUI zu bekommen.
Ein zukünftigen Release werde ich wohl entsprechend ergänzen – da lag aber zunächst keine Prio drauf.
Ich hoffe dir mit der Antwort helfen zu können?!
Gruß,
Sebastian
OK, danke für die Aufklärung. Soweit verstanden.
Für einen Audit wäre das trotzdem super, wenn die Auflistung vollständig wäre. Sonst muss man alle Einstellungen manuell durchklicken, um zu wissen, ob ein Benutzer irgendwo hinterlegt ist und mehr Rechte hat als er haben sollte.
Anpassung wäre als top!
Hi Dennis,
da hast Du Recht! Das macht Sinn. Ich werde das im Laufe des Januars ergänzen.
Gruß,
Sebastian
Danke dir
DANKE DANKE DANKE für das userlookup.sh script ……
Erstaunlich das Sophos nicht entweder das Problem beim Löschen von Usern in der UI mit anzeigt oder direkt (wie bei der UTM damals) fragt ob das Problem direkt gelöst werden soll.
Auch ein blick in das CSC.log zeigt nur das eine referenz auf die Tabelle tblfwusers existiert …. ohne die interna zu kennen .. keine Chance.
Dein Script hat mir echt sehr geholfen.
dankbare grüße
Holger
Hallo Holger,
Vielen Dank für Dein Feedback!
Mit etwas Glück kommt die Funktion in SFOS 21 dazu. Erste Teaser sprechen dafür. Schauen wir mal 😊
Viele Grüße,
Sebastian