ranger-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Velmurugan Periasamy <vperias...@hortonworks.com>
Subject Re: Review Request 68133: RANGER-2169: Create unique index on service and name column of x_policy table
Date Tue, 31 Jul 2018 22:37:27 GMT

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


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message