Created 10-06-2015 11:20 PM
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?
Created 10-06-2015 11:26 PM
Created 10-06-2015 11:26 PM
Check attached scripts and see if it helps..
Created 10-06-2015 11:49 PM
Will this work on Postgress also?
Created 07-05-2016 04:07 PM
@bganesan Now that https://issues.apache.org/jira/browse/RANGER-205 is fixed, can we use the rest API instead of DB script?
Created 09-07-2017 01:08 PM
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
Created 10-06-2015 11:26 PM
Created 10-07-2015 12:08 AM
Rest apis used for delete are:
Created 10-07-2015 10:30 AM
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.
Created 03-16-2016 11:59 PM
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$;
Created 10-27-2015 04:04 PM
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
Created 11-11-2015 08:14 PM
log into the ranger database, and delete the following rows in order.
delete from x_group_users where added_by_id in (1,2)
delete from x_user where added_by_id in (1,2)
delete from x_group where added_by_id in (1,2)
Created 12-07-2016 12:27 PM
Using version 0.5.x. Had to modify as following.
delete from x_group_users where added_by_id in (1,2); delete from x_policy_item_user_perm where user_id in (select id from x_user where added_by_id in (1,2)); delete from x_user where added_by_id in (1,2); delete from x_policy_item_group_perm where group_id in (select id from x_group where added_by_id in (1,2)); delete from x_group where added_by_id in (1,2); delete from x_user_module_perm where added_by_id in (1,2); delete from x_portal_user_role where added_by_id in (1,2); delete from x_portal_user where added_by_id in (1,2);
Created 02-10-2016 10:17 PM
I have a follow up question on this.
Lets say I removed all the users from Ranger which were synced from a local unix server and then re-configured to sync users from an AD domain/group. In this case, do II need to create "hive" user on that particular AD group before I can create a policy to let hive queries run as hive user instead of end users on the cluster? what about other service accounts like mapred, yarn etc .. do I need to create all those accounts on AD? please advise.
Created 02-11-2016 10:37 PM
Yes, if the users are removed from Ranger DB, service users also need to be re-sync'ed.
Created 10-12-2016 08:20 AM
You can also go for a full reset of the ranger_admin databases by following these steps:
https://cwiki.apache.org/confluence/display/RANGER/Manual+Reinstallation+of+Ranger-admin
I had to go for this option after my manual interventions with the database led to problems on the Ranger WebUI. Downside of this is that the Ranger plugin services also have to be reconfigured, so beware