Is it possible to make oracle queries case insensitive for User Directory Mapping in SiteMinder Policy Server?
Article ID: 54607
Updated On:
Products
Issue/Introduction
Description:
The problem is, I think, pretty straightforward. I am authenticating and authorizing user 'mikel' (typed in all lower case) at a web site.
The authentication directory is an LDAP Server and I am authenticated with no problem.
The authorization directory is a user directory (Oracle) via ODBC. The table is 'smuser' and the column containing the name is 'name'. The configured user attribute for Universal ID is 'smuser.name'.
The query executed seems to be the one called Lookup Users, default syntax: Select Name, "User" as Class from SmUser where %s. However, it only finds the row when the name is typed in all Upper Case. When typed in all Lower Case, no row is returned. User is not authorized because is not found in the directory.
I modified the query to try to accommodate the lowercase typing and the uppercase data: Select Name, "User" as Class from SmUser where upper(%s). This failed.
I tried several permutations such as upper('%s'), and (upper(%s)), and others. Every on where upper was applied resulted in a database error as below. In all cases it looks like SiteMinder is treating expanding %s for 'mikel' as 'smuser.name = 'mikel'. Thus when the Upper ( ) function is applied to that string, Oracle returns a syntax error:
[17/Nov/2008:14:15:34 -0500][6556-E] Database Error executing query 'select name, 'User' as Class from smuser where name = upper('name = 'mikel' ')'. Internal Error: Database error. Error code -4007.
I was hoping you would recognize this situation and would have a suggestion as to how to work around this issue. I've got to believe this has been encountered before.
Solution:
This issue has been documented in the Technical Note "Directory Mapping Case Sensitivity Tech Note" available at:
https://support.ca.com/irj/portal/anonymous/phpdocs?filePath=7/5262/auth2_Case_Sensitivity_Tech_Note.html.
The above technical note provide several solutions, but in this KB article we are going to discuss a solution from the Oracle Side.
The problem is because the following query is not returning data:
SQL> select name, 'User' as Class 2 from smuser 3 where name = 'mikel'; no rows selected
However, it does return data when the user is in uppercase:
SQL> select name, 'User' as Class 2 from smuser 3 where name = 'MIKEL'; NAME ---------------------------------- CLAS ---- MIKEL User
This is because Oracle Database is case-sensitive. But, how can we make oracle queries case insensitive?
There is more than one way to achieve this and this is only one example.
We can create a function-based index using nlssort() function on the table smuser and column name, For instance:
CREATE INDEX smuser_name ON smuser(NLSSORT(name,'NLS_SORT=BINARY_CI'));
The above index will support case-insensitive queries, however two oracle parameters must be set in order to support this index: NLS_COMP and NLS_SORT. These oracle parameters can be implemented at session level, by a trigger at logon time, for the user who queries the smuser table:
CREATE OR REPLACE TRIGGER ENABLE_INSENSITIVE_TGR
AFTER logon
-- Replace smuser with the user who queries the smuser table
ON smuser.schema
BEGIN
execute immediate 'alter session set NLS_COMP=ANSI';
execute immediate 'alter session set NLS_SORT=BINARY_CI';
END ENABLE_INSENSITIVE_TGR;
As result of the above, all the queries executed by the user "smuser" will be case insensitive and it will return information:
SQL> select name, 'User' as Class 2 from smuser 3 where name = 'mikel'; NAME ---------------------------------- CLAS ---- MIKEL User
Notes:
- This is an example; Your Oracle DBA should evaluate the best solution for your environment based on your business requirements and needs.
- Oracle Database must be oracle 10g or higher.
- Oracle Database must support function-based indexes.
Environment
Component: SMPLC
Feedback
