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