Re: Review Request 69453: RANGER-2291: Make optimized db schema script idempotent for all DB Flavors

[Velmurugan Periasamy <vp...@hortonworks.com>]

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


Ship it!




Ship It!

- Velmurugan Periasamy


On Nov. 28, 2018, 9:43 a.m., Pradeep Agrawal wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/69453/
> -----------------------------------------------------------
> 
> (Updated Nov. 28, 2018, 9:43 a.m.)
> 
> 
> Review request for ranger, Abhay Kulkarni, Madhan Neethiraj, Mehul Parikh, Nikhil P, Ramesh Mani, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-2291
>     https://issues.apache.org/jira/browse/RANGER-2291
> 
> 
> Repository: ranger
> 
> 
> Description
> -------
> 
> **Problem Statement:** Currently Ranger core db schema is not idempotent for all db flavors. Ranger core DB schema for Oracle and SQL anywhere flavor may fail to execute if we execute them again for the same DB flavor.
> 
> **Proposed Solution:** I have added drop statements before the create statements for the various objects(table/constraints etc)
> 
> 
> Diffs
> -----
> 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql a4fa1305e 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 0949cbd1d 
>   security-admin/db/oracle/patches/009-updated_schema.sql 7e21f69e1 
>   security-admin/db/oracle/patches/013-permissionmodel.sql 4ac7901ba 
>   security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql 12627f589 
>   security-admin/db/oracle/patches/020-datamask-policy.sql 8448a8568 
>   security-admin/db/oracle/patches/022-split-service-table.sql 9b4f69c4c 
>   security-admin/db/oracle/patches/025-create-schema-for-plugin-info.sql bedd0a2ef 
>   security-admin/db/oracle/patches/030-policy-labels-schema.sql 894b9346f 
>   security-admin/db/oracle/patches/031-create-schema-for-usersync-audit-info.sql cb52065c6 
>   security-admin/db/oracle/patches/035-update-schema-for-x-policy.sql c75e62089 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql a0e02e0e0 
>   security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql db8ebc343 
>   security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql f3b64d003 
>   security-admin/db/sqlanywhere/patches/020-datamask-policy.sql fe6fa9f61 
>   security-admin/db/sqlanywhere/patches/022-split-service-table.sql d32966d8c 
>   security-admin/db/sqlanywhere/patches/025-create-schema-for-plugin-info.sql 6e9477984 
>   security-admin/db/sqlanywhere/patches/030-policy-labels-schema.sql b2ed2386d 
>   security-admin/db/sqlanywhere/patches/031-create-schema-for-usersync-audit-info.sql 8ed84e302 
>   security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql c079014df 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 522b57b03 
> 
> 
> Diff: https://reviews.apache.org/r/69453/diff/1/
> 
> 
> Testing
> -------
> 
> **Steps Performed (with patch) :**
> 1. After Build untar the Ranger module and updated install.properties for Oracle DB flavor.
> 2. Called setup.sh to install Ranger.
> 3. Started Ranger admin and logged in to check the installation is working or not.
> 4. create a user 'testuser1'.
> 5. Stopped Ranger admin.
> 6. Executed given JISQL command again to import core db schema with the same config (for the same ranger db and user):
> 
> /usr/jdk64/jdk1.8.0_112/bin/java -Djava.security.egd=file:///dev/urandom  -cp /usr/share/java/ojdbc6.jar:/tmp/ranger-2.0.0-SNAPSHOT-admin/jisql/lib/* org.apache.util.sql.Jisql -driver oraclethin -cstring jdbc:oracle:thin:@localhost -u 'ranger112701' -p '********' -noheader -trim -input /tmp/ranger-2.0.0-SNAPSHOT-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql -c \;
> 
> **Expected behavior:** Command should able to execute core db schema file again and should not fail. user testuser1 should not appear in user/groups page of ranger admin
> 
> **Actual behavior:** Command executed successfully and recreated all the tables again, was able to see new entries and able to login to ranger admin. 'testuser1' was not seen in the ranger admin.
> 
> 
> Thanks,
> 
> Pradeep Agrawal
> 
>