Deploying Security Data

Whenever a secured application is deployed to the end-user, a number of required tables must exist within the specified database with the structure specified below. These tables should be deployed with all security data pertaining to the desired security project. If the Database Deployment Toolkit is being used, both the deployment data packages and the required table structures may be created automatically via the Deployment Package Wizard.

Deploying Security Data Using the DDT

It is highly recommended that the Database Deployment Toolkit be used to generate both the structure and deployment data for the security system. Not only does the available Deployment Package Wizard greatly streamline the deployment process, it also ensures accuracy and completeness for the structure and data.

To generate the required structure and deployment packages using the Database Deployment Toolkit:

Open Visual Studio.
Select Database Deployment Toolkit under the StrataFrame menu.
Select the desired DDT Profile and click the Open button.
Right-click on the Deployment Data item in the left panel and select Deployment Package Wizard from the context menu.
Choose Role Based Security Data as the Deployment Data Type.
When prompted, select the desired source database, destination database, and security project.
Once the wizard is complete, both the security table structures and deployment data will be generated and ready for distribution.

Note: A more in-depth step-by-step for the above process is available in the Deployment Package Wizard topic within the StrataFrame DDT help file.

Deploying Security Data Without the DDT

Even if the Database Deployment Toolkit is not being used, the required table structures and deployment data will still need to be created on the end-user system.

For convenience, the required table structures and relationships are listed below. Again, if using the Database Deployment Toolkit, both the below structures and all deployment data present in the development databases may be packaged automatically using the Deployment Package Wizard.

Table Structures SQL Script

The SecurityTables.sql script is a SQL Server 2005 script that can be used to create the StrataFrame security tables within a database. You can run this script independently, or you may incorporate it into your application's own database creation script.

To run the script:

Download the Script - Click here to download the script. Once downloaded, extract the .sql file from the Zip.
Replace Database Name - Before the script may be run, a single line must be changed. The very first non commented line in the script reads "USE [DatabaseName]". Replace "DatabaseName" with the name of the desired database.
Run the Script - The script is now ready to be run.

Note: This script is designed for SQL Server 2005 only, and must be modified before it can be used on SQL Server 2000.

Explicit Table Definitions

The explicit table definitions for all security tables along with their required relationships are listed below.

Preferences - SFSPreferences

Permissions - SFSPermissions

Roles - SFSRoles

Users - SFSUsers

Restrictions - SFSRestrictions

Restriction Items - SFSRestrictionItems

Roles to Permissions Link Table - SFSRolesXPermissions

Users to Permissions Link Table - SFSUsersXPermissions

Users to Roles Link Table - SFSUsersXRoles

Table Relationships

The above listed tables must exist with the following relationships:

Security Relationships
The following diagram shows the basic outline of all table relationships within security:
Permission, Roles, and Users Diagram
The following diagram shows a more detailed breakdown of the relationships concerning all Permission, Role, and User tables:
Workstation Restrictions Diagram
The following diagram shows a more detailed breakdown of the relationship between the SFSRestrictions and SFSRestrictionItems tables:

StrataFrame Security Documentation	Send comments on this topic.
Deploying Security Data