----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/68133/ -----------------------------------------------------------
Review request for ranger, Ankita Sinha, Gautam Borad, Abhay Kulkarni, Madhan Neethiraj, Mehul Parikh, Ramesh Mani, and Velmurugan Periasamy. Bugs: RANGER-2169 https://issues.apache.org/jira/browse/RANGER-2169 Repository: ranger Description ------- **Problem Statement:** Currently `name` column of x_policy table does not have unique constraint and validation is done only at Ranger admin code. Concurrent create policy request might create same name policies within the same service. **Proposed Solution:** We can't create unique index in a table columns if there are duplicate entries in it so first we need to rename/remove the duplicate entries. 1. SQL Patch 033 shall Update the policy name if there are duplicate policies in a service. New policy name shall be '<old policyname>-duplicate-<id>'. Example : if there are two policy having same name say 'hivepolicy' with id 10 and 11 then the new name of the policies shall be 'hivepolicy-duplicate-10' and 'hivepolicy-duplicate-11' 2. Add Unique key/constraint on 'name' and 'service' columns of x_policy table. Diffs ----- security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 174204eb3 security-admin/db/mysql/patches/033-add-unique-constraint-on-table-x_policy.sql PRE-CREATION security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 2d18b5082 security-admin/db/oracle/patches/033-add-unique-constraint-on-table-x_policy.sql PRE-CREATION security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 0e0344d9b security-admin/db/postgres/patches/033-add-unique-constraint-on-table-x_policy.sql PRE-CREATION security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql 4cf295c81 security-admin/db/sqlanywhere/patches/033-add-unique-constraint-on-table-x_policy.sql PRE-CREATION security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 09701e2b8 security-admin/db/sqlserver/patches/033-add-unique-constraint-on-table-x_policy.sql PRE-CREATION Diff: https://reviews.apache.org/r/68133/diff/1/ Testing ------- **Steps Performed (without patch) :** Steps (without patch) is to reproduce the case so that when we with patch ranger installation is done then it can update the duplicate policies name. 1. After Build untar the Ranger module and updated install.properties for MySQL DB flavor. 2. Called setup.sh to install Ranger. 3. Started Ranger admin and created hive service and hive policies 'hivepolicy1', 'hivepolicy2' and 'hivepolicy3' 4. Logged into ranger db and updated all three policy name to 'hivepolicy' by using SQL statement: UPDATE x_policy set name='hivepolicy' where id in(4,5,6); 5. Restarted Ranger and Ranger UI was showing 3 hive policies with same name 'hivepolicy'. **Steps Performed (with patch) :** 1. After Build untar the Ranger module and updated install.properties for MySQL DB flavor with the same configuration used earlier. 2. Called setup.sh to install Ranger. 3. Restarted Ranger and Logged into Ranger admin to check names of 3 duplicate policies. **Expected Behavior :** 1. Ranger UI should show different policy names and there should not be any duplicate policies. 2. Unique constraint should get created in x_policy(name,service). 3. Attempt to create same name policies with in the same service should fail. For example below given SQL statement execution should fail: "UPDATE x_policy set name='hivepolicy' where id in(4,5,6)" **Actual Behavior :** 1. Ranger UI was showing three default policies and 3 hive policies which was having name 'hivepolicy' was appearing with name 'hivepolicy-duplicate-4' 'hivepolicy-duplicate-5' 'hivepolicy-duplicate-6' where 4,5 and 6 are the ID's of hivepolicy. 2. Unique key got created on name and service column of x_policy table. 3. Execution of SQL statement "UPDATE x_policy set name='hivepolicy' where id in(4,5,6)" failed due to unique key constraint violation attempt. Note: I have tested above steps for all other DB Flavors. Thanks, Pradeep Agrawal