It is possible and sometimes reasonable to modify the schema for the Core Access Assurance Suite’s Profile table in order to enhance its capacity to store user data coming from authoritative sources such as Lawson or PeopleSoft HR Systems.
As you plan to make alterations to the schema, it is important to keep in mind that there are other database objects that depend on this table and such dependencies could be removed causing other parts to break.
The following is a list of changes you should not make to the Profile table without consulting with Core Security Engineer or Consultant:
- Don’t Rename The Columns
- Don’t Reduce The Size Of The Columns
- Don’t Change The Datatype Of Columns
- Don’t Add More Columns
It is advisable to leave the schema as is unless there is a serious need for modifications.
Reason To Modify
The common reason to alter the schema for the Profile table is when the column sizes are insufficient for storing the data from authoritative sources. An easy work-around is to truncate the data but some times this is not good enough, hence the need to make changes.
Steps To Take
Using SQL Management Studio
- Generate A Script To Alter The Following Views
vw_Profile –> Right Click –>Script View as –> ALTER To –>New Query Editor Window
vw_Role_Tag_Associations –> Right Click –>Script View as –> ALTER To –>New Query Editor Window
- Make the desired changes to the Profile table e.g increase the size of a column or columns
- Make sure you are able to save changes to the database by Going to the Menu>Tools>Options>Designers>Uncheck “Prevent saving changes that require table re-creation“
- Then save the changes by going to Menu, click on File> Save [SERVER NAME] – dbo.Profile
- You will be presented with a Prompt saying “The following tables will be saved to your database, Do you want to continue?“, click “Yes” to continue
- You will then be presented with a second prompt warning you of changes to the two previously backed up views in step 1, click “Yes” to continue
- Once save is complete, execute the two scripts from step 1 to restore the schema binding removed by step 6.
Your changes are now complete.