Re: Review Request 73663: RANGER-3493: Add unique index on service and resource_signature column of x_policy table

[Abhay Kulkarni <ak...@hortonworks.com>]

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/73663/#review223769
-----------------------------------------------------------


Ship it!




Ship It!

- Abhay Kulkarni


On Oct. 21, 2021, 12:39 p.m., Pradeep Agrawal wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/73663/
> -----------------------------------------------------------
> 
> (Updated Oct. 21, 2021, 12:39 p.m.)
> 
> 
> Review request for ranger, bhavik patel, Dineshkumar Yadav, Kishor Gollapalliwar, Abhay Kulkarni, Madhan Neethiraj, Mehul Parikh, Mehul Parikh, Ramesh Mani, Sailaja Polavarapu, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-3493
>     https://issues.apache.org/jira/browse/RANGER-3493
> 
> 
> Repository: ranger
> 
> 
> Description
> -------
> 
> **Problem Statement:** Currently resource_signature column of x_policy table does not have unique constraint. Also if there are more than one ranger service in the same service-def and policies exported from one service is imported in the other service then the policy resource_signature is same and do not change, however since service id's are different for both the resource_signature. we need to restrict 1 entry only for the same resource_signature under a specific service. in case of disabled policy resource_signature could be same as enabled policy which also should be taken care.
> 
> 
> **Proposed Solution:**
> => it will be better to include service id also with resource_signature column for the unique key creation so that the same restriction can be enforced from db end. 
> 
> 
> Note: This is required after the analysys of RANGER-3490, to maintain uniqueness in disabled policies, resource_signature can have guid as well. resource_signature field length need to be extended so that it can contain guid as well. to handle the upgrade case, resource_signature field should be updated with guid also for disabled policies.
> 
> 
> Diffs
> -----
> 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 57af321a3 
>   security-admin/db/mysql/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 25ee44cc5 
>   security-admin/db/oracle/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 78cee1a93 
>   security-admin/db/postgres/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql PRE-CREATION 
>   security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql 632aefe1d 
>   security-admin/db/sqlanywhere/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql fa29da008 
>   security-admin/db/sqlserver/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql PRE-CREATION 
> 
> 
> Diff: https://reviews.apache.org/r/73663/diff/4/
> 
> 
> Testing
> -------
> 
> Tested the latest patch on apache master branch for different DB Flavors(mysql/oracle/postgres/mssql)
> 
> 
> Thanks,
> 
> Pradeep Agrawal
> 
>