From dev-return-18746-archive-asf-public=cust-asf.ponee.io@ranger.apache.org Tue Jul 31 18:40:57 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 2A451180662 for ; Tue, 31 Jul 2018 18:40:56 +0200 (CEST) Received: (qmail 2753 invoked by uid 500); 31 Jul 2018 16:40:56 -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 2735 invoked by uid 99); 31 Jul 2018 16:40:55 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 31 Jul 2018 16:40:55 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 383A3C035E; Tue, 31 Jul 2018 16:40:55 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.601 X-Spam-Level: ** X-Spam-Status: No, score=2.601 tagged_above=-999 required=6.31 tests=[DKIM_ADSP_CUSTOM_MED=0.001, FREEMAIL_REPLYTO_END_DIGIT=0.25, 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 (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id O9yjHlVV-aHC; Tue, 31 Jul 2018 16:40:53 +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 AF0575F27D; Tue, 31 Jul 2018 16:40:52 +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 5A9FBE0253; Tue, 31 Jul 2018 16:40:52 +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 45753C40151; Tue, 31 Jul 2018 16:40:52 +0000 (UTC) Content-Type: multipart/alternative; boundary="===============8991308996734078772==" MIME-Version: 1.0 Subject: Review Request 68133: RANGER-2169: Create unique index on service and name column of x_policy table From: Pradeep Agrawal To: Mehul Parikh , Gautam Borad , Abhay Kulkarni , Ramesh Mani , Velmurugan Periasamy , Ankita Sinha , Madhan Neethiraj Cc: Pradeep Agrawal , ranger Date: Tue, 31 Jul 2018 16:40:52 -0000 Message-ID: <20180731164052.42424.37944@reviews-vm2.apache.org> X-ReviewBoard-URL: https://reviews.apache.org/ Auto-Submitted: auto-generated Sender: Pradeep Agrawal X-ReviewGroup: ranger X-Auto-Response-Suppress: DR, RN, OOF, AutoReply X-ReviewRequest-URL: https://reviews.apache.org/r/68133/ X-Sender: Pradeep Agrawal 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 Reply-To: Pradeep Agrawal X-ReviewRequest-Repository: ranger --===============8991308996734078772== 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/ ----------------------------------------------------------- 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 --===============8991308996734078772==--