Modifying the Core Access Assurance Suite Profile Table Schema with MSSQL

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.

Alteration Don’ts

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.