SQL Query Scheme Dialog for Get User Properties.

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>