-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/74616/
-----------------------------------------------------------
Review request for ranger, Abhishek Kumar, bhavik patel, Dhaval Shah,
Dineshkumar Yadav, Kishor Gollapalliwar, Abhay Kulkarni, Madhan Neethiraj,
Mehul Parikh, Ramesh Mani, Sailaja Polavarapu, and Velmurugan Periasamy.
Bugs: RANGER-4422
https://issues.apache.org/jira/browse/RANGER-4422
Repository: ranger
Description
-------
**Problem Statement:** While searching for users / groups using the "Sync
Source" filter on the Users/Groups page, it leads to an error page. This issue
is present only on Oracle DB
``Error Message:``
2023-09-19 17:10:19,701 ERROR org.apache.ranger.biz.XUserMgr:
[https-jsse-nio-6182-exec-9]: Error getting the exact match of group
=>javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse
Persistence Services - 2.7.12.v20230209-e5c4074ef3):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent
datatypes: expected - got CLOB
Error Code: 932
Call: SELECT COUNT(ID) FROM x_group WHERE (((? = ?) AND LOWER(GROUP_NAME) LIKE
?) AND (LOWER(SYNC_SOURCE) = ?))
bind => [4 parameters bound]
Query: ReportQuery(referenceClass=XXGroup sql="SELECT COUNT(ID) FROM x_group
WHERE (((? = ?) AND LOWER(GROUP_NAME) LIKE ?) AND (LOWER(SYNC_SOURCE) = ?))")
2023-09-19 17:10:21,654 WARN org.apache.ranger.biz.XUserMgr:
[https-jsse-nio-6182-exec-5]: XUserMgr.searchXGroups: unexpected
searchCriteriaParam:name
**Proposed solution:** The above error is due to SYNC_SOURCE column being used
in where clause which is of clob type in oracle and can not be used directly.
According to oracle documents type casting need to be done but since type
casting functions are different in different db flavor using them and
customizing the code for each flavor seems messy. The other possible approach
is we can use 'partial search approach' (rather 'full') in the code which shall
be converted to like query as per underlying db flavor. like clause shall take
care of type casting as well. since 'sync_source' column values are restricted
to 4 distinct possible values, choosing one values in the filter will not bring
the result of another 'sync_source'.
Diffs
-----
security-admin/src/main/java/org/apache/ranger/service/XGroupService.java
650760304
security-admin/src/main/java/org/apache/ranger/service/XUserService.java
d7c011ca3
Diff: https://reviews.apache.org/r/74616/diff/1/
Testing
-------
Tried Ranger installation with the proposed patch and the behaviour is as per
the expectation. Search request with sync_source filter completed successfully
and page was loaded with records.
Thanks,
Pradeep Agrawal
