SQL Queries

Products

• CA Single Sign On Secure Proxy Server (SiteMinder)
• CA Single Sign On SOA Security Manager (SiteMinder)
• CA Single Sign-On

Issue/Introduction

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 ?

Environment

Release: MSPSSO99000-12.8-Single Sign-On-for Business Users-MSP
Component:

Resolution

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.