Patrick-Dussault

Tech Tip : CA Single Sign-On : SQL Queries

Discussion created by Patrick-Dussault Employee on Aug 16, 2018

Question:


I'd like to find documentation about 2 things :

- describes which SQL Query is executed when policy server set the response;
- when Policy Server does the search of a user from User Directory
definition;

Where can I find it ?

 

Answer:

 

At first glance, our documentation gives guide line on the SQL
statement sent for different use case :

SQL Query Scheme Dialog

"SQL Query Scheme Settings

The SQL Query Scheme dialog contains fields that describe SQL
queries that that Policy Server uses to access user and group
information. The default values for each query field correspond to
the CA Single Sign-on sample relational database schema called
SmSampleUsers. You must change the table and column names if you
are using a different database schema.

[...]"

https://docops.ca.com/ca-single-sign-on/12-8/en/using/administrative-ui/user-directory-dialog/sql-query-scheme-dialog

Even if that documentation is related to 12.8, this should apply to
Policy Server 12.52SP1.

Following that documentation, when Policy Server searches a User in an ODBC Store for
authentication, then it should send something equivalent to :

select Name from SmUser where Name = '%s' and Password = '%s'

When it looks for a user property (which might be the case when
triggering a response), then it should send something like :

select %s from SmUser where Name = '%s'

More, to get a more precise view on SQL statement sent depending the
different operation, you might configure the Policy Server profiler to
get the full SQL and ODBC traces with all the data field set in the
Profiler. This should give you the SQL statement.

 

KB : KB000111227

Outcomes