Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-03-02 Thread Pradeep Agrawal 


> On March 2, 2023, 2:49 a.m., Kirby Zhou wrote:
> > security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql
> > Lines 19 (patched)
> > 
> >
> > Can we avoid using stored procedures? TiDB does not support stored 
> > procedures.
> 
> Pradeep Agrawal wrote:
> How the other old sql patches with stored procedure are working in TiDB. 
> Also if TiDB defers from mysql then TiDB support can be added seperately.
> 
> Kirby Zhou wrote:
> The old sql patches have the same problem……
> 
> 
> TiDB is a 95% mysql-compatible NewSQL database. For legal reason, we have 
> to deploy ranger based on tidb. But TiDB is missing some features, which 
> makes ranger unable to install properly.
> 
> https://docs.pingcap.com/tidb/stable/mysql-compatibility#unsupported-features
> 
> The biggest problem affecting ranger is missing "Stored procedures and 
> functions", "Select into".
> ranger use Stored procedures in setup scripts to simplify SQL.
> Some work is needed to remove the stored procedure.
> 
> Because few old versions of Ranger are using TiDb, we mainly focus on sql 
> patch after ranger-2.3.
> 
> 
> https://issues.apache.org/jira/browse/RANGER-3984

Would not like to mix with MySQL. TiDB support can be added separately(as a 
separate db flavor). 
Earlier there was issue with MySQL functions so i moved them to MySQL 
procedures. Now again won't prefer to put effort for moving out of procedure 
for TiDB. 
Also dont have environment of TiDB and not familier to it.


- Pradeep


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


On Feb. 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
> 
> ---
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> ---
> 
> (Updated Feb. 23, 2023, 10:35 p.m.)
> 
> 
> Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
> Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
> 
> 
> Repository: ranger
> 
> 
> Description
> ---
> 
> Currently, Service Definitions are persisted in several database tables in a 
> normalized form. However, as operations on a Service Definition object is not 
> granular, but act on the object as a whole, there is not much advantage in 
> normalizing it for storage, and a distinct disadvantage of having to recreate 
> it by accessing multiple database tables during the initialization of Ranger 
> admin process.
> 
> This Jira denormalizes service-definition object's persistent state and also 
> removes several tables used only for normalizing service-definition object.
> 
> 
> Diffs
> -
> 
>   
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
>  8632dd6bc 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 
> 9a79fe8ad 
>   security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
> fd6cec9a7 
>   security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
> 4d5a8cedf 
>   security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
>  3ed2a5b9c 
>   
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
> ca8f7da1f 
>   security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
> 0e5388646 
>   security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
> 562467e80 
>   security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
>   security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
> 418557bcb 
>

Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-03-01 Thread Kirby Zhou 


> On 三月 2, 2023, 2:49 a.m., Kirby Zhou wrote:
> > security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql
> > Lines 19 (patched)
> > 
> >
> > Can we avoid using stored procedures? TiDB does not support stored 
> > procedures.
> 
> Pradeep Agrawal wrote:
> How the other old sql patches with stored procedure are working in TiDB. 
> Also if TiDB defers from mysql then TiDB support can be added seperately.

The old sql patches have the same problem……


TiDB is a 95% mysql-compatible NewSQL database. For legal reason, we have to 
deploy ranger based on tidb. But TiDB is missing some features, which makes 
ranger unable to install properly.
https://docs.pingcap.com/tidb/stable/mysql-compatibility#unsupported-features

The biggest problem affecting ranger is missing "Stored procedures and 
functions", "Select into".
ranger use Stored procedures in setup scripts to simplify SQL.
Some work is needed to remove the stored procedure.

Because few old versions of Ranger are using TiDb, we mainly focus on sql patch 
after ranger-2.3.


https://issues.apache.org/jira/browse/RANGER-3984


- Kirby


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


On 二月 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
> 
> ---
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> ---
> 
> (Updated 二月 23, 2023, 10:35 p.m.)
> 
> 
> Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
> Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
> 
> 
> Repository: ranger
> 
> 
> Description
> ---
> 
> Currently, Service Definitions are persisted in several database tables in a 
> normalized form. However, as operations on a Service Definition object is not 
> granular, but act on the object as a whole, there is not much advantage in 
> normalizing it for storage, and a distinct disadvantage of having to recreate 
> it by accessing multiple database tables during the initialization of Ranger 
> admin process.
> 
> This Jira denormalizes service-definition object's persistent state and also 
> removes several tables used only for normalizing service-definition object.
> 
> 
> Diffs
> -
> 
>   
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
>  8632dd6bc 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 
> 9a79fe8ad 
>   security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
> fd6cec9a7 
>   security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
> 4d5a8cedf 
>   security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
>  3ed2a5b9c 
>   
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
> ca8f7da1f 
>   security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
> 0e5388646 
>   security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
> 562467e80 
>   security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
>   security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
> 418557bcb 
>   security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java 
> db1878a35 
>   security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 
> 7f8d6193f 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java
>  f5f9f8956 
>   
>

Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-03-01 Thread Pradeep Agrawal 


> On March 2, 2023, 2:49 a.m., Kirby Zhou wrote:
> > security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql
> > Lines 19 (patched)
> > 
> >
> > Can we avoid using stored procedures? TiDB does not support stored 
> > procedures.

How the other old sql patches with stored procedure are working in TiDB. 
Also if TiDB defers from mysql then TiDB support can be added seperately.


- Pradeep


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


On Feb. 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
> 
> ---
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> ---
> 
> (Updated Feb. 23, 2023, 10:35 p.m.)
> 
> 
> Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
> Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
> 
> 
> Repository: ranger
> 
> 
> Description
> ---
> 
> Currently, Service Definitions are persisted in several database tables in a 
> normalized form. However, as operations on a Service Definition object is not 
> granular, but act on the object as a whole, there is not much advantage in 
> normalizing it for storage, and a distinct disadvantage of having to recreate 
> it by accessing multiple database tables during the initialization of Ranger 
> admin process.
> 
> This Jira denormalizes service-definition object's persistent state and also 
> removes several tables used only for normalizing service-definition object.
> 
> 
> Diffs
> -
> 
>   
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
>  8632dd6bc 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 
> 9a79fe8ad 
>   security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
> fd6cec9a7 
>   security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
> 4d5a8cedf 
>   security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
>  3ed2a5b9c 
>   
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
> ca8f7da1f 
>   security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
> 0e5388646 
>   security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
> 562467e80 
>   security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
>   security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
> 418557bcb 
>   security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java 
> db1878a35 
>   security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 
> 7f8d6193f 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java
>  f5f9f8956 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
>  fb7e5fd40 
>   
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java
>  656bc0184 
>   
> security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java
>  85e457efa 
>   security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 
> 691ab52b3 
>   security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 
> 528f4e511 
> 
> 
> Diff: https://reviews.apache.org/r/74319/diff/1/
> 
> 
> Testing
> ---
> 
> Ran all unit tests successfully.

Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-03-01 Thread Kirby Zhou 

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




security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql
Lines 19 (patched)


Can we avoid using stored procedures? TiDB does not support stored 
procedures.


- Kirby Zhou


On 二月 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
> 
> ---
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> ---
> 
> (Updated 二月 23, 2023, 10:35 p.m.)
> 
> 
> Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
> Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
> 
> 
> Repository: ranger
> 
> 
> Description
> ---
> 
> Currently, Service Definitions are persisted in several database tables in a 
> normalized form. However, as operations on a Service Definition object is not 
> granular, but act on the object as a whole, there is not much advantage in 
> normalizing it for storage, and a distinct disadvantage of having to recreate 
> it by accessing multiple database tables during the initialization of Ranger 
> admin process.
> 
> This Jira denormalizes service-definition object's persistent state and also 
> removes several tables used only for normalizing service-definition object.
> 
> 
> Diffs
> -
> 
>   
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
>  8632dd6bc 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 
> 9a79fe8ad 
>   security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
> fd6cec9a7 
>   security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
> 4d5a8cedf 
>   security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
>  3ed2a5b9c 
>   
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
> ca8f7da1f 
>   security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
> 0e5388646 
>   security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
> 562467e80 
>   security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
>   security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
> 418557bcb 
>   security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java 
> db1878a35 
>   security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 
> 7f8d6193f 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java
>  f5f9f8956 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
>  fb7e5fd40 
>   
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java
>  656bc0184 
>   
> security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java
>  85e457efa 
>   security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 
> 691ab52b3 
>   security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 
> 528f4e511 
> 
> 
> Diff: https://reviews.apache.org/r/74319/diff/1/
> 
> 
> Testing
> ---
> 
> Ran all unit tests successfully. Ran ranger-admin and component services in a 
> cluster and verified they work correctly.
> 
> 
> Thanks,
> 
> Abhay Kulkarni
> 
>

Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-02-27 Thread Pradeep Agrawal 


> On Feb. 24, 2023, 10:30 a.m., Kirby Zhou wrote:
> > Have you tested it on mysql/mariadb with master-slave? Some sql statements 
> > have restrictions.

I am able to upgrade to this patch build from ranger-2.3
GTID replication Server version: 8.0.32 MySQL Community Server - GPL


- Pradeep


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


On Feb. 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
> 
> ---
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> ---
> 
> (Updated Feb. 23, 2023, 10:35 p.m.)
> 
> 
> Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
> Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
> 
> 
> Repository: ranger
> 
> 
> Description
> ---
> 
> Currently, Service Definitions are persisted in several database tables in a 
> normalized form. However, as operations on a Service Definition object is not 
> granular, but act on the object as a whole, there is not much advantage in 
> normalizing it for storage, and a distinct disadvantage of having to recreate 
> it by accessing multiple database tables during the initialization of Ranger 
> admin process.
> 
> This Jira denormalizes service-definition object's persistent state and also 
> removes several tables used only for normalizing service-definition object.
> 
> 
> Diffs
> -
> 
>   
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
>  8632dd6bc 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 
> 9a79fe8ad 
>   security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
> fd6cec9a7 
>   security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
> 4d5a8cedf 
>   security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
>  3ed2a5b9c 
>   
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
> ca8f7da1f 
>   security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
> 0e5388646 
>   security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
> 562467e80 
>   security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
>   security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
> 418557bcb 
>   security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java 
> db1878a35 
>   security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 
> 7f8d6193f 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java
>  f5f9f8956 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
>  fb7e5fd40 
>   
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java
>  656bc0184 
>   
> security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java
>  85e457efa 
>   security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 
> 691ab52b3 
>   security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 
> 528f4e511 
> 
> 
> Diff: https://reviews.apache.org/r/74319/diff/1/
> 
> 
> Testing
> ---
> 
> Ran all unit tests successfully. Ran ranger-admin and component services in a 
> cluster and verified they work correctly.
> 
> 
> Thanks,
> 
> Abhay Kulkarni
> 
>

Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-02-24 Thread Kirby Zhou 


> On 二月 24, 2023, 2:47 a.m., Madhan Neethiraj wrote:
> > security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
> > Line 856 (original)
> > 
> >
> > This patch deletes around 20 tables, but corresponding entity/dao 
> > classes have not been deleted. Deleting these classes might be involved; 
> > until this is done, I suggest to not to delete the tables.
> > 
> > This can help to enable rolling upgrades as well i.e. current version 
> > of Ranger will continue to work with newer version of Ranger. This will 
> > however require:
> > - the newer Ranger to continue populating tables used current version 
> > as well, in addition to new column x_service_def.service_def_text
> > - in addition, the newer Ranger should handle service_def updates from 
> > earlier Ranger version - perhaps by having another column like 
> > update_time_v2 and comparing it to update_time column

It is very compliated to rollback.
I suggest renaming these obsolete tables instead of deleting them to avoid 
possible conflicts and keep backups.


- Kirby


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


On 二月 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
> 
> ---
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> ---
> 
> (Updated 二月 23, 2023, 10:35 p.m.)
> 
> 
> Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
> Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
> 
> 
> Repository: ranger
> 
> 
> Description
> ---
> 
> Currently, Service Definitions are persisted in several database tables in a 
> normalized form. However, as operations on a Service Definition object is not 
> granular, but act on the object as a whole, there is not much advantage in 
> normalizing it for storage, and a distinct disadvantage of having to recreate 
> it by accessing multiple database tables during the initialization of Ranger 
> admin process.
> 
> This Jira denormalizes service-definition object's persistent state and also 
> removes several tables used only for normalizing service-definition object.
> 
> 
> Diffs
> -
> 
>   
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
>  8632dd6bc 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 
> 9a79fe8ad 
>   security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
> fd6cec9a7 
>   security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
> 4d5a8cedf 
>   security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
>  3ed2a5b9c 
>   
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
> ca8f7da1f 
>   security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
> 0e5388646 
>   security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
> 562467e80 
>   security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
>   security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
> 418557bcb 
>   security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java 
> db1878a35 
>   security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 
> 7f8d6193f 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java
>  f5f9f8956 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java
>

Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-02-24 Thread Kirby Zhou 

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



Have you tested it on mysql/mariadb with master-slave? Some sql statements have 
restrictions.

- Kirby Zhou


On 二月 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
> 
> ---
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> ---
> 
> (Updated 二月 23, 2023, 10:35 p.m.)
> 
> 
> Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
> Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
> 
> 
> Repository: ranger
> 
> 
> Description
> ---
> 
> Currently, Service Definitions are persisted in several database tables in a 
> normalized form. However, as operations on a Service Definition object is not 
> granular, but act on the object as a whole, there is not much advantage in 
> normalizing it for storage, and a distinct disadvantage of having to recreate 
> it by accessing multiple database tables during the initialization of Ranger 
> admin process.
> 
> This Jira denormalizes service-definition object's persistent state and also 
> removes several tables used only for normalizing service-definition object.
> 
> 
> Diffs
> -
> 
>   
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
>  8632dd6bc 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 
> 9a79fe8ad 
>   security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
> fd6cec9a7 
>   security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
> 4d5a8cedf 
>   security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
>  3ed2a5b9c 
>   
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
> ca8f7da1f 
>   security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
> 0e5388646 
>   security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
> 562467e80 
>   security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
>   security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
> 418557bcb 
>   security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java 
> db1878a35 
>   security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 
> 7f8d6193f 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java
>  f5f9f8956 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
>  fb7e5fd40 
>   
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java
>  656bc0184 
>   
> security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java
>  85e457efa 
>   security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 
> 691ab52b3 
>   security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 
> 528f4e511 
> 
> 
> Diff: https://reviews.apache.org/r/74319/diff/1/
> 
> 
> Testing
> ---
> 
> Ran all unit tests successfully. Ran ranger-admin and component services in a 
> cluster and verified they work correctly.
> 
> 
> Thanks,
> 
> Abhay Kulkarni
> 
>

Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-02-23 Thread Madhan Neethiraj 

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




security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
Line 856 (original)


This patch deletes around 20 tables, but corresponding entity/dao classes 
have not been deleted. Deleting these classes might be involved; until this is 
done, I suggest to not to delete the tables.

This can help to enable rolling upgrades as well i.e. current version of 
Ranger will continue to work with newer version of Ranger. This will however 
require:
- the newer Ranger to continue populating tables used current version as 
well, in addition to new column x_service_def.service_def_text
- in addition, the newer Ranger should handle service_def updates from 
earlier Ranger version - perhaps by having another column like update_time_v2 
and comparing it to update_time column


- Madhan Neethiraj


On Feb. 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
> 
> ---
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> ---
> 
> (Updated Feb. 23, 2023, 10:35 p.m.)
> 
> 
> Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
> Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
> 
> 
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
> 
> 
> Repository: ranger
> 
> 
> Description
> ---
> 
> Currently, Service Definitions are persisted in several database tables in a 
> normalized form. However, as operations on a Service Definition object is not 
> granular, but act on the object as a whole, there is not much advantage in 
> normalizing it for storage, and a distinct disadvantage of having to recreate 
> it by accessing multiple database tables during the initialization of Ranger 
> admin process.
> 
> This Jira denormalizes service-definition object's persistent state and also 
> removes several tables used only for normalizing service-definition object.
> 
> 
> Diffs
> -
> 
>   
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
>  8632dd6bc 
>   security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 
> 9a79fe8ad 
>   security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
> fd6cec9a7 
>   security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
> 4d5a8cedf 
>   security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
>  3ed2a5b9c 
>   
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
> ca8f7da1f 
>   security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
> 0e5388646 
>   security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
> PRE-CREATION 
>   security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
> PRE-CREATION 
>   security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
> 562467e80 
>   security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
>   security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
> 418557bcb 
>   security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java 
> db1878a35 
>   security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 
> 7f8d6193f 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java
>  f5f9f8956 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java
>  PRE-CREATION 
>   
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
>  fb7e5fd40 
>   
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java
>  656bc0184 
>   
>

Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup

2023-02-23 Thread Abhay Kulkarni 

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

Review request for ranger, Abhishek  Kumar, Dineshkumar Yadav, madhan, Madhan 
Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.


Bugs: RANGER-4108
https://issues.apache.org/jira/browse/RANGER-4108


Repository: ranger


Description
---

Currently, Service Definitions are persisted in several database tables in a 
normalized form. However, as operations on a Service Definition object is not 
granular, but act on the object as a whole, there is not much advantage in 
normalizing it for storage, and a distinct disadvantage of having to recreate 
it by accessing multiple database tables during the initialization of Ranger 
admin process.

This Jira denormalizes service-definition object's persistent state and also 
removes several tables used only for normalizing service-definition object.


Diffs
-

  
agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
 8632dd6bc 
  security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 9a79fe8ad 
  security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql 
PRE-CREATION 
  security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql 
PRE-CREATION 
  security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql 
fd6cec9a7 
  security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql 
PRE-CREATION 
  security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql 
PRE-CREATION 
  security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 
4d5a8cedf 
  security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql 
PRE-CREATION 
  security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql 
PRE-CREATION 
  
security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql 
3ed2a5b9c 
  security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql 
PRE-CREATION 
  security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql 
PRE-CREATION 
  security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql 
ca8f7da1f 
  security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 
0e5388646 
  security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql 
PRE-CREATION 
  security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql 
PRE-CREATION 
  security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 
562467e80 
  security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52 
  security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 
418557bcb 
  security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java 
db1878a35 
  security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 
7f8d6193f 
  
security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java
 PRE-CREATION 
  
security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java
 f5f9f8956 
  
security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java
 PRE-CREATION 
  
security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
 fb7e5fd40 
  
security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java
 656bc0184 
  
security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java 
85e457efa 
  security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 
691ab52b3 
  security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 
528f4e511 


Diff: https://reviews.apache.org/r/74319/diff/1/


Testing
---

Ran all unit tests successfully. Ran ranger-admin and component services in a 
cluster and verified they work correctly.


Thanks,

Abhay Kulkarni