Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to Remove all External Users from the Ranger / Ranger usersync database

Solved Go to solution
Highlighted

How to Remove all External Users from the Ranger / Ranger usersync database

In the course of testing the usersync tool, we had some settings wrong and the users are mis-synced. We'd like to clear them all out and restart the sync. Is there an easy way/tool to remove all the external users from the Ranger / Ranger usersync database so we can resync with our new settings?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to Remove all External Users from the Ranger / Ranger usersync database

Contributor

scripts.zip

Check attached scripts and see if it helps..

14 REPLIES 14

Re: How to Remove all External Users from the Ranger / Ranger usersync database

Contributor

scripts.zip

Check attached scripts and see if it helps..

Re: How to Remove all External Users from the Ranger / Ranger usersync database

Will this work on Postgress also?

Re: How to Remove all External Users from the Ranger / Ranger usersync database

Guru

@bganesan Now that https://issues.apache.org/jira/browse/RANGER-205 is fixed, can we use the rest API instead of DB script?

Re: How to Remove all External Users from the Ranger / Ranger usersync database

New Contributor

Really useful scripts. Helped me with my MySQL environment.

To prepare the input.txt, I had to run this and pass this as input to the script:

$ mysql -u userName -p dbName -e “select user_name from x_user” > /tmp/input.txt

Re: How to Remove all External Users from the Ranger / Ranger usersync database

Contributor

Re: How to Remove all External Users from the Ranger / Ranger usersync database

Re: How to Remove all External Users from the Ranger / Ranger usersync database

Awesome! These API urls were really helpful!

You have to make sure the user or group is not assigned to any policy before you delete it. If the user/group is assigned to a policy the API returns 404 not found as result.

Re: How to Remove all External Users from the Ranger / Ranger usersync database

New Contributor

Until the API gets fixed to clean things up correctly, here's a PostgreSQL anonymous code block which cleans up the stuff it leaves behind which we used:

DO $
DECLARE 
	u record;
	r record;
	p record;
BEGIN
	FOR u IN select id, login_id from x_portal_user where login_id not in (select user_name from x_user)
	LOOP
		RAISE NOTICE 'User roles in x_portal_user_role:';
		FOR r IN select id, user_id, user_role from x_portal_user_role where user_id = u.id
		LOOP
			RAISE NOTICE USING MESSAGE = '  ' || r.user_role;
			RAISE NOTICE USING MESSAGE = 'DELETE from x_portal_user_role WHERE id = ' || r.id;
			-- Uncomment next line to perform action
			--EXECUTE 'DELETE from x_portal_user_role WHERE id = ' || r.id;
		END LOOP;
		RAISE NOTICE 'User permissions in x_user_module_perm:';
		FOR p IN select id, user_id, module_id from x_user_module_perm where user_id = u.id
		LOOP
			RAISE NOTICE USING MESSAGE = '  ' || (select module from x_modules_master where id = p.module_id);
			RAISE NOTICE USING MESSAGE = 'DELETE from x_user_module_perm where id = ' || p.id;
			-- Uncomment next line to perform action
			--EXECUTE 'DELETE from x_user_module_perm where id = ' || p.id;
		END LOOP;
		RAISE NOTICE USING MESSAGE = 'DELETE FROM x_portal_user WHERE id = ' || u.id;
		-- Uncomment next line to perform action
		--EXECUTE 'DELETE FROM x_portal_user WHERE id = ' || u.id;
		RAISE NOTICE '  ';
	END LOOP;
END$;

Re: How to Remove all External Users from the Ranger / Ranger usersync database

For complete cleaning of users from DB, please use the scripts provided by @bganesan@hortonworks.com above.

REST API DELETE calls perform only soft-delete.

Hard DELETE feature is still in the works.https://issues.apache.org/jira/browse/RANGER-205

Don't have an account?
Coming from Hortonworks? Activate your account here