From dev-return-18760-archive-asf-public=cust-asf.ponee.io@ranger.apache.org Wed Aug 1 06:18:41 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id BAB57180662 for ; Wed, 1 Aug 2018 06:18:40 +0200 (CEST) Received: (qmail 12563 invoked by uid 500); 1 Aug 2018 04:18:38 -0000 Mailing-List: contact dev-help@ranger.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@ranger.apache.org Delivered-To: mailing list dev@ranger.apache.org Received: (qmail 12552 invoked by uid 99); 1 Aug 2018 04:18:38 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 01 Aug 2018 04:18:38 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 91A4F180659; Wed, 1 Aug 2018 04:18:37 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.351 X-Spam-Level: ** X-Spam-Status: No, score=2.351 tagged_above=-999 required=6.31 tests=[DKIM_ADSP_CUSTOM_MED=0.001, HEADER_FROM_DIFFERENT_DOMAINS=0.25, HTML_MESSAGE=2, KAM_LAZY_DOMAIN_SECURITY=1, KAM_MANYTO=0.2, NML_ADSP_CUSTOM_MED=1.2, RCVD_IN_DNSWL_MED=-2.3] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id tRc-WTCLsToB; Wed, 1 Aug 2018 04:18:33 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id 8EA3C5F21E; Wed, 1 Aug 2018 04:18:33 +0000 (UTC) Received: from reviews.apache.org (unknown [10.41.0.12]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 2ECA1E0253; Wed, 1 Aug 2018 04:18:33 +0000 (UTC) Received: from reviews-vm2.apache.org (localhost [IPv6:::1]) by reviews.apache.org (ASF Mail Server at reviews-vm2.apache.org) with ESMTP id CF8EDC40151; Wed, 1 Aug 2018 04:18:32 +0000 (UTC) Content-Type: multipart/alternative; boundary="===============6727937952997169265==" MIME-Version: 1.0 Subject: Re: Review Request 68133: RANGER-2169: Create unique index on service and name column of x_policy table From: Gautam Borad To: Mehul Parikh , Gautam Borad , Abhay Kulkarni , Ramesh Mani , Velmurugan Periasamy , Ankita Sinha , Madhan Neethiraj Cc: Pradeep Agrawal , ranger Date: Wed, 01 Aug 2018 04:18:32 -0000 Message-ID: <20180801041832.42423.64804@reviews-vm2.apache.org> X-ReviewBoard-URL: https://reviews.apache.org/ Auto-Submitted: auto-generated In-Reply-To: <20180731164052.42424.37944@reviews-vm2.apache.org> X-ReviewGroup: ranger X-Auto-Response-Suppress: DR, RN, OOF, AutoReply X-ReviewRequest-URL: https://reviews.apache.org/r/68133/ X-Sender: Gautam Borad X-ReviewBoard-ShipIt: 1 References: <20180731164052.42424.37944@reviews-vm2.apache.org> X-ReviewBoard-Diff-For: security-admin/db/sqlanywhere/patches/033-add-unique-constraint-on-table-x_policy.sql X-ReviewBoard-Diff-For: security-admin/db/postgres/patches/033-add-unique-constraint-on-table-x_policy.sql X-ReviewBoard-Diff-For: security-admin/db/oracle/patches/033-add-unique-constraint-on-table-x_policy.sql X-ReviewBoard-Diff-For: security-admin/db/mysql/patches/033-add-unique-constraint-on-table-x_policy.sql X-ReviewBoard-Diff-For: security-admin/db/sqlserver/patches/033-add-unique-constraint-on-table-x_policy.sql X-ReviewBoard-ShipIt-Only: 1 Reply-To: Gautam Borad X-ReviewRequest-Repository: ranger Sender: Gautam Borad --===============6727937952997169265== MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/68133/#review206720 ----------------------------------------------------------- Ship it! Ship It! - Gautam Borad 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 '-duplicate-'. > 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 > > --===============6727937952997169265==--