ranger-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pradeep Agrawal <pradeepagrawal8...@gmail.com>
Subject Review Request 68133: RANGER-2169: Create unique index on service and name column of x_policy table
Date Tue, 31 Jul 2018 16:40:52 GMT

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


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