----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/68133/#review206697 -----------------------------------------------------------
Ship it! Ship It! - Velmurugan Periasamy On July 31, 2018, 4:40 p.m., Pradeep Agrawal wrote: > > ----------------------------------------------------------- > This is an automatically generated e-mail. To reply, visit: > https://reviews.apache.org/r/68133/ > ----------------------------------------------------------- > > (Updated July 31, 2018, 4:40 p.m.) > > > 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 > >
