SQL Query Scheme Dialog for Get User Properties.

Document ID : KB000053782
Last Modified Date : 14/02/2018
Show Technical Document Details

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.

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'

Figure 1