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'
<Please see attached file for image>