Layer 7 Access Management

Tech Tip : CA Single Sign-On : SQL Queries

  • 1.  Tech Tip : CA Single Sign-On : SQL Queries

    Posted 08-16-2018 10:37 AM

    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