SQL Query Scheme Dialog for Get User Properties.
search cancel

SQL Query Scheme Dialog for Get User Properties.

book

Article ID: 53782

calendar_today

Updated On:

Products

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

Issue/Introduction

Description:

Customer Environment:
Policy Server: R12 SP1 CR02
OS: Win2003 SP2
Policy Store: SQL DB 2005
User directory: SQL DB 2005

Problem:
Customer is using SQL Database as user directory, and would like to store a user attribute in a different table other than username and provide this in the SM policy for authorization and header responses.
Customer wants to add extra values to default DB policy store using SQL Query Scheme. Need to verify if user attributes must exist in the same table as user name.

 

Environment

Release:
Component: SMPLC

Resolution

Solution:

  • This is the default scheme

  • Get User Properties field

    Name, UserId, FirstName, LastName, TelephoneNumber, EmailAddress, PIN, Mileage, Disabled

  • Get User Property field

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

  • Siteminder will run the following query

    select Name, UserId, FirstName, LastName, TelephoneNumber, EmailAddress, PIN, Mileage, Disabled from SmUser
    where Name = 'Mikel'

  • Let's say you want to add the table more attributes

    CREATE TABLE MoreAtt (UserID NUMBER NOT NULL,
    Attr1 VARCHAR(200),
    Attr2 VARCHAR(200));

  • Insert more attributes

    insert INTO MoreAtt (userid, attr1, attr2) VALUES (2,'Attribute number 1', 'Attribute number 2');
    COMMIT;

  • Now MODIFY your SQL query scheme

  • Get User Properties field

    smuser.Name, smuser.UserId, smuser.FirstName, smuser.LastName, smuser.TelephoneNumber, smuser.EmailAddress, smuser.PIN,
    smuser.Mileage, smuser.Disabled, moreatt.attr1, moreatt.attr1

  • Get User Property field

    SELECT %s FROM smuser, moreatt WHERE smuser.userid = moreatt.userid AND NAME = '%s'

  • SiteMinder will run the following query:

    SELECT smuser.Name, smuser.UserId, smuser.FirstName, smuser.LastName, smuser.TelephoneNumber, smuser.EmailAddress, smuser.PIN,
    smuser.Mileage, smuser.Disabled, moreatt.attr1, moreatt.attr2
    FROM smuser, moreatt
    WHERE smuser.userid = moreatt.userid
    AND NAME = 'Mikel'

<example result>

 

Attachments