[
https://issues.apache.org/jira/browse/SYNCOPE-667?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14540011#comment-14540011
]
Guido Wimmel commented on SYNCOPE-667:
--------------------------------------
Created a branch:
https://git-wip-us.apache.org/repos/asf?p=syncope.git;a=shortlog;h=refs/heads/SYNCOPE-667
I could test with H2, MySQL, and PostgreSQL, the tests pass and I didn't find a
difference between the original and simplified query.
The execution plan for the modified query in MySQL seems to be less complex
(one less DEPENDENT SUBQUERY), but I am not sure about a possible performance
impact.
About the tests: I modified AuthenticationTestITCase.testUserSearch() a bit to
improve coverage: in the first part, it verifies that user #2 and user #5 are
not returned, as they have memberships to roles outside the role entitlements
of the user that executes the search. For this purpose, I had to change the
search query to isNotNull("id") - otherwise user #2 and user #5 are always
excluded regardless of their memberships.
I think the second part of testUserSearch() is not very meaningful as
userService3.search() simply returns nothing (because test user "verdi" does
not have any role entitlements, so no search is performed).
I didn't want to make too many changes to the test initially as am not familiar
enough with its intention.
Still should be tested on the other supported databases.
> simplification of admin roles filter query used in search
> ---------------------------------------------------------
>
> Key: SYNCOPE-667
> URL: https://issues.apache.org/jira/browse/SYNCOPE-667
> Project: Syncope
> Issue Type: Improvement
> Affects Versions: 1.2.4
> Reporter: Guido Wimmel
> Assignee: Guido Wimmel
> Priority: Minor
>
> As discussed on the dev mailing list [1], I think the query generated in
> SubjectSearchDAOImpl.getAdminRolesFilter() to determine the users which have
> roles not in adminRoles can be simplified for type==SubjectType.USER.
> Currently generated query: (for type==USER and adminRoles={1,2}):
> {code}
> SELECT syncopeUser_id AS subject_id FROM Membership M1 WHERE syncopeRole_id IN
> (SELECT syncopeRole_id FROM Membership M2 WHERE
> M2.syncopeUser_id=M1.syncopeUser_id AND syncopeRole_id NOT IN
> (SELECT id AS syncopeRole_id FROM SyncopeRole WHERE id=1 OR id=2)
> )
> {code}
> Suggested simplification:
> {code}
> SELECT syncopeUser_id AS subject_id FROM Membership M2 WHERE syncopeRole_id
> NOT IN (
> SELECT id AS syncopeRole_id
> FROM SyncopeRole WHERE id=1 OR id=2
> )
> {code}
> In addition, the test coverage of the admin roles filter mechanism used in
> search should be improved.
> [1]
> http://syncope-dev.1063484.n5.nabble.com/getAdminRolesFilter-query-td5716932.html
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
