The Lightweight Directory Access Protocol LDAP Sync Job in Clarity and in CA Business Intelligence CABI LDAP Sync function differently when username fields have mixed case.
Clarity LDAP Sync job ignores the case of Active Directory (AD) usernames and brings users into Clarity with all lowercase usernames, while the CABI LDAP Sync respects the case of the user names in Active Directory and brings them in as they are in Active Directory.
This causes problems when creating WEBI reports using stock Clarity Universes, and causes the WEBI reports to come back with no data.
Steps to Reproduce:
Example 1: For the given scenario:
- Create a user in Active Directory and ensure their username/account name is in uppercase (e.g. "USER1")
- Create a user in Active Directory and ensure their username/account name is in lowercase (e.g. "user2")
- Perform an LDAP sync with Clarity
- Perform an LDAP sync with CABI (DO NOT use the Create BO Users job from Clarity)
- Note that both users in Clarity have their usernames in lower case (e.g. "user1" and "user2" for our example)
- Note that in CABI the users have retained their case-sensitivity (e.g. "USER1" and "user2" for our example)
Example 2: Regarding the running of reports given the above discrepancy
- Now using the WEBI component of CABI attempt to create and run a report utilizing any of the PPM Universes
- Log into InfoView with USER1
- Create a basic WEBI report utilizing the CA PPM Investments - Oracle Universe
- Add the Investments > Projects > Project ID to the WEBI Report
- Run the Query and note that there is no data
- Return to the Report Design and click the "SQL" button and note the portions of the query that evaluate "user_name = @variable('BOUSER')"
- Firstly, this statement is what causes the lack of data.
Referring to Example 1 we know that in Clarity user_name = 'user1'; however, the @variable('BOUSER') = 'USER1' which at a SQL level will not evaluate properly and will thus return no data
- Secondly, the intent of injecting the security evaluation is completely pointless as the SQL can be easily modified from InfoView/WEBI to remove the offending conditions and allow anyone access to anything.
- The SQL that is generated in Step 6 originates from the Universe and is found in the [*_security_<object>] tables
- For instance, Open the Universe Designer
- Import the CA PPM Investments - Oracle Universe
- Locate the RPT_PRJ_SECURITY_PROJECT table
- Right-click on the above table and select "Edit Derived Table"
- You are now presented with the underlying SQL that generates the conditions in step 6
Expected Result: The application should be able to identify the user's account for generating reports.
Actual Result:WEBI reports produce no data due to the code not recognizing the user's account.
Update the PPM Universes and the derived tables such that the conditions are no longer "user_name = @variable('BOUSER')" but instead manipulate the string to be the same case on both sides. For instance, "UPPER(user_name) = UPPER(@variable('BOUSER'))" . This can be tested in a DEV environment, by modifying all the security tables. Your expected results should be that a user with inconsistent case across Clarity and CABI will be able to pull reports via WEBI
This issue is documented as CLRT-68484 and is in review with development for a future resolution.
Keywords: CLARITYKB, CLRT-68484, clarity13open.