Using a dedicated security database for multiple LightSwitch apps. (Part 2)

Introduction

Introducing a dedicated database for the security tables and sharing this database between several LightSwitch apps can be an interesting option in an enterprise context. I’m envisioning in this post the scenario where all applications are independent from each other, i.e. roles, users and permissions  are not shared between applications. The reason why I have put, in part one of this blog series, special emphasis on the role-permission table, is that it requires special attention in the setup of of a shared security database. I encountered a major difference when setting up the shared security database whether you are using an intrinsic database for you apps or whether you are using a database as external data source.

Scenario 1: Applications use intrinsic databases

When envisioning a setup where all your apps use an intrinsic database and you use one dedicated database for the security tables, you end up with following web config for one of the apps:

<connectionStrings>
<add name="_IntrinsicData" connectionString="Data Source=MyServer;Database=ApplicationXYZDatabase;uid=dbUserId;Pwd=myPassword;" />
<add name="CommonSecurity" connectionString="Data Source=MyServer;Database=CommonSecurity;uid=dbUserId;Pwd=myPassword;" />
</connectionStrings>
<system.web>
<membership defaultProvider="AspNetMembershipProvider">
<providers>
<clear />
<add name="AspNetMembershipProvider" type="System.Web.Security.SqlMembershipProvider"

connectionStringName="CommonSecurity" applicationName="ApplicationXYZ" />
</providers>
</membership>
<roleManager enabled="True" defaultProvider="AspNetRoleProvider">
<providers>
<clear />
<add name="AspNetRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="CommonSecurity"

applicationName="ApplicationXYZ" />
</providers>
</roleManager>
<profile enabled="True" defaultProvider="AspNetProfileProvider">
<providers>
<clear />
<add name="AspNetProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="CommonSecurity"

applicationName="Application XYZ" />
</providers>
<properties>
<add name="FullName" />
</properties>
</profile>

Some observations:

  • the _IntrinsicData refers to your application database, and the different security providers (membership, role and profile) point to the CommonSecurity connectionstring.

  • in each security provider, it’s important to specify the application name (e.g. applicationName=”ApplicationXYZ”)

  • the security database can be easily created by setting once a Lightswitch application with no application tables, open the deployment package and run the sql script. Easy !

  • it’s sufficient that your different application databases contain only your application tables, except the role permission table.
  • There will never be a collision between security data (users, roles) between application. So, delete “ROLE_ABC” in APP1 will not impact APP2 even if a role with name “ROLE_ABC” also exists in APP2. Your role-permissions table is always safe because it is application specific.

 Scenario 2: Applications use an external database

When envisioning a setup where all your apps use an external  database and you use one dedicated database for the security tables, you end up with following web config for one of the apps:

<connectionStrings>

<add name="_ExternalDataSourceForApplicationXYZ" connectionString="Data Source=MyServer;Database=ApplicationXYZDatabase;uid=dbUserId;Pwd=myPassword;" />
<add name="_IntrinsicData" connectionString="Data Source=MyServer;Database=CommonSecurity;uid=dbUserId;Pwd=myPassword;" />
</connectionStrings>
<system.web>
<membership defaultProvider="AspNetMembershipProvider">
<providers>
<clear />
<add name="AspNetMembershipProvider" type="System.Web.Security.SqlMembershipProvider"
connectionStringName="_IntrinsicData" applicationName="ApplicationXYZ" />
</providers>
</membership>
<roleManager enabled="True" defaultProvider="AspNetRoleProvider">
<providers>
<clear />
<add name="AspNetRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="_IntrinsicData"
applicationName="ApplicationXYZ" />
</providers>
</roleManager>
<profile enabled="True" defaultProvider="AspNetProfileProvider">
<providers>
<clear />
<add name="AspNetProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="_IntrinsicData"
applicationName="Application XYZ" />
</providers>
<properties>
<add name="FullName" />
</properties>
</profile>

Again some observations:

  • the _IntrinsicData refers now to the security database and the different security providers (membership, role and profile) point to this connection string.
  • each application database has a separate connection string (in my example _ExternalDataSourceForApplicationXYZ) .
  • the security database can be easily created by setting once a Lightswitch application with no application tables, open the deployment package and run the sql script. Easy !
  • this time to role-permission table sits in the the security database and is,  as a result, shared amongst all applications. That’s good because it means that our external databases are really external (no Lightswitch specific stuff in it).
  • Nonetheless, and refer back now to part one of this series, you have to make sure that role names and permission names you specify have a unique name per application.  The reason is that the role-permission table doesn’t make use of technical IDs !

Conclusion

Sharing a security database amongst different LightSwitch applications is nicely supported by LightSwitch both in the scenario where you use intrinsic databases and external databases for your applications.