Deleting users from Ranger database (mysql)
Once you sync users in Apache Ranger they will stay in the database even if we sync ranger users from a different source.
All those users will clutter up the Ranger user interface.
Following two scripts will help in deleting those users & groups from the ranger database.
Delete Users
File: updatedDeleteUser.sh
#!/bin/bash
## Script to delete Ranger Users from database
## Usage: deleteUser.sh -f input.txt -u ranger_user -p password -db ranger [-r <replaceUser>]
## -f contains newline separated list of users to be deleted
## -u db user name
## -p db user password
## -db db name
## -r (optional) User to be used to replace references of deleted user. If not provided, `admin` will be used.
superuser="admin";
usage() {
[ "$*" ] && echo "$0: $*"
sed -n '/^##/,/^$/s/^## \{0,1\}//p' "$0"
exit 2
} 2>/dev/null
while [ "$1" != "" ]; do
case $1 in
-f | --file ) shift
filename=$1
;;
-u | --username ) shift
user=$1
;;
-p | --password ) shift
passwd=$1
;;
-r | --replaceUser ) shift
superuser=$1
;;
-db | --db ) shift
dbname=$1
;;
-h | --help ) usage
exit
;;
* ) usage
exit 1
esac
shift
done
if [ -z "$filename" ]; then usage; exit 1; fi
if [ -z "$user" ]; then usage; exit 1; fi
if [ -z "$passwd" ]; then usage; exit 1; fi
if [ -z "$dbname" ]; then usage; exit 1; fi
mysqlex="mysql -u${user} -p${passwd} $dbname"
$mysqlex <<EOF
DELIMITER $$
CREATE PROCEDURE deleteUserByUsername(username varchar(1024), superuser varchar(1024))
BEGIN
declare xuser_id bigint;
declare x_portal_user_id bigint;
declare superuser_id bigint;
set xuser_id = (select id from x_user where user_name=username);
if (xuser_id is not null) then
delete from x_audit_map where user_id = xuser_id;
delete from x_group_users where user_id = xuser_id;
delete from x_perm_map where user_id = xuser_id;
delete from x_user where id = xuser_id;
end if;
set x_portal_user_id = (select id from x_portal_user where login_id=username);
if(x_portal_user_id is not null) then
set superuser_id = (select id from x_portal_user where login_id = superuser);
if(superuser_id is null) then
set superuser_id = (select user_id from x_portal_user_role where user_role = "ROLE_SYS_ADMIN" and status = 1 LIMIT 1);
end if;
update x_asset set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_asset set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_audit_map set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_audit_map set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_auth_sess set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_auth_sess set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_cred_store set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_cred_store set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_group set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_group set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_group_groups set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_group_groups set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_group_users set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_group_users set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_perm_map set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_perm_map set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_policy_export_audit set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_policy_export_audit set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_portal_user set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_portal_user set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_portal_user_role set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_portal_user_role set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_resource set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_resource set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_trx_log set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_trx_log set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
update x_user set added_by_id=superuser_id where added_by_id=x_portal_user_id;
update x_user set upd_by_id=superuser_id where upd_by_id=x_portal_user_id;
delete from x_auth_sess where user_id = x_portal_user_id;
delete from x_portal_user_role where user_id = x_portal_user_id;
delete from x_portal_user where id = x_portal_user_id;
end if;
END
EOF
while read line
do
name=$(echo $line)
if [ -z "$name" ]; then continue; fi
name=$(echo "$name" | sed "s|\\\0|\\\\\\0|g")
name=$(echo "$name" | sed "s|'|\\\'|g")
name=$(echo "$name" | sed "s|%|\\\%|g")
name=$(echo "$name" | sed "s|\\\_|\\\\\\\_|g")
name=$(echo "$name" | sed "s|Z|\\\Z|g")
echo " Deleting user : $name"
$mysqlex -e "CALL deleteUserByUsername(\"$name\", \"$superuser\")"
done < $filename
$mysqlex -e "DROP PROCEDURE IF EXISTS deleteUserByUsername"
echo "Deleted all Users successfully"
Delete Groups
File: updatedDeleteGroup.sh
#!/bin/bash
## Script to delete Ranger Groups from database
## Usage: deleteUser.sh -f input.txt -u ranger_user -p password -db ranger
## -f contains newline separated list of groups to be deleted
## -u db user name
## -p db user password
## -db db name
usage() {
[ "$*" ] && echo "$0: $*"
sed -n '/^##/,/^$/s/^## \{0,1\}//p' "$0"
exit 2
} 2>/dev/null
while [ "$1" != "" ]; do
case $1 in
-f | --file ) shift
filename=$1
;;
-u | --username ) shift
user=$1
;;
-p | --password ) shift
passwd=$1
;;
-db | --db ) shift
dbname=$1
;;
-h | --help ) usage
exit
;;
* ) usage
exit 1
esac
shift
done
if [ -z "$filename" ]; then usage; exit 1; fi
if [ -z "$user" ]; then usage; exit 1; fi
if [ -z "$passwd" ]; then usage; exit 1; fi
if [ -z "$dbname" ]; then usage; exit 1; fi
mysqlex="mysql -u${user} -p${passwd} $dbname"
$mysqlex <<EOF
DELIMITER $$
CREATE PROCEDURE deleteGroupByGroupName(grpName varchar(1024))
BEGIN
declare grpId bigint;
set grpId = (select id from x_group where group_name=grpName);
if(grpId is not null) then
delete from x_audit_map where group_id = grpId;
delete from x_group_users where p_group_id = grpId;
delete from x_perm_map where group_id = grpId;
delete from x_group_groups where group_id = grpId or p_group_id = grpId;
delete from x_group where id = grpId;
end if;
END
EOF
while read line
do
name=$(echo $line)
if [ -z "$name" ]; then continue; fi
name=$(echo "$name" | sed "s|\\\0|\\\\\\0|g")
name=$(echo "$name" | sed "s|'|\\\'|g")
name=$(echo "$name" | sed "s|%|\\\%|g")
name=$(echo "$name" | sed "s|\\\_|\\\\\\\_|g")
name=$(echo "$name" | sed "s|Z|\\\Z|g")
echo " Deleting group : $name"
$mysqlex -e "CALL deleteGroupByGroupName(\"$name\")"
done < $filename
$mysqlex -e "DROP PROCEDURE IF EXISTS deleteGroupByGroupName"
echo "Deleted all Groups successfully"
References
https://community.hortonworks.com/questions/1021/how-to-remove-all-external-users-from-the-ranger-r.html
