Hue Oracle Database- Delete non standard user entries from Parent and Child Tables.

 

We recently implemented authentication for Hue (https://gethue.com/) before that folks were allowed to authenticate with any kind of username and use Hue WebUI. 

After implementing authentication it was found that Hue Database consisted entries for old unwanted garbage users which were still able to use the service and by pass authentication.

Thus, it was required to delete such user entries from table HUE.AUTH_USER. But there were many associated child constraints, with the table which made deleting an entry violate constraints. 

Thus, it was required to find out all child tables associated with HUE.AUTH_USER. First delete entries from Child tables followed by deleting entries from Parent HUE.AUTH_USER.

We used following query to find all child tables, constraints and associated columns - 

select a.table_name parent_table_name, b.r_constraint_name parent_constraint, c.column_name parent_column, b.table_name child_table, b.constraint_name as child_constraint, d.column_name child_column from all_constraints a, 
all_constraints b, all_cons_columns c, all_cons_columns d
where a.owner='HUE' and a.table_name like 'AUTH_USER' and a.CONSTRAINT_TYPE in ('P', 'U') and a.constraint_name=b.r_constraint_name and a.owner=c.owner and a.table_name=c.table_name
and b.r_constraint_name=c.constraint_name and 
b.owner=d.owner and b.table_name=d.table_name
and b.constraint_name=d.constraint_name;

Further, we wrote a PLSQL block that deletes entries from Child tables, followed by deleting entries from parent table, followed by commit statement. Please refer following for complete PLSQL - 

https://github.com/dinesh028/engineering/blob/master/Oracle/deleteParentChildEntriesHue/hue_cleanup_parent_child.sql