-----------------------------------------------------------
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
