Here is a brief document I put together that explains the concept of what I have done. When I have some more documentation that doesn't have any sensitive information included I will try and upload it somewhere. Sorry for some of the formatting but cut and paste didn't copy the formatting.
Background
When trying to secure data in web applications, there are several methods that can be used to secure the data at the database level. These methods however require you to use some form of authentication with the database that restricts your design decisions and also limit your scalability of the application.
Requirements
- Authentication must be heterogeneous to enable authentication of clients developed using technologies other than Microsoft .NET.
- Authentication must be available to partners and clients that are not included in the Active Directory.
- Authentication must be scalable to potentially hundreds of thousands of users.
- Authentication should ideally be consistent across all of the applications in the suite of products provided by Worldsmart Technology.
- Management of users is to be managed through the Merchant Access web application.
- Users with applicable roles must be able to create new users in the application and be able assign applicable roles to those users.
- Users with applicable roles must be able to view and edit only users that are associated with their own or related Merchants.
- System Administrators must be able to view and edit all users.
- System Administrators must be able to add/remove roles from the application.
- System Administrators must be able to add/remove the roles that can be granted by roles with the ability to add/edit users.
- Authentication is required to support database replication scenarios.
Overview
Authentication is provided by a solution based on the SQL Authentication Providers shipped as part of the Microsoft .NET 2.0 Framework. The provider has been extended where necessary to provide the functionality required to satisfy the requirements.
CodeSmith and .NetTiers templates are used to create and maintain the data access layers of the application and have been modified where necessary to satisfy the requirements.
A User is assigned to a Merchant, and Merchants are stored in a hierarchical structure in the database. A User has the ability to view information related to their Merchant and any Merchants below them in the hierarchy. The user is restricted in the functions they can perform by the role to which they have been granted.
A System Administrator is able to modify the stored procedures to which each user has access.
Solution
Using the SQL Authentication Provider, the schema was extended as shown in Diagram 1. Custom tables were added with care taken not to modify existing schema. Customised tables were added with the prefix aspnet_cust_ to differentiate these tables from the tables used by the provider.
aspnet_cust_RoleProcedures - This table is used to assign a role with permission to execute a stored procedure.
aspnet_cust_RoleGrantPermissions – This table is used to defined the list of roles that another role has permission to grant (e.g. Merchant Administrators have permission to grant Merchant User role and, Licensor Administrator has permission to grant Licensor Administrator, Licensor User, Group Administrator, Group User, Merchant Administrator and Merchant User roles).
aspnet_cust_UsersMerchants – This table is used to assign the user to a Merchant.
Diagram 1: Authentication Schema
Stored Procedures are secured using the following methods:
ALTER PROCEDURE [dbo].[wst_Merchant_Get_List]
(
@SecurityUsername VARCHAR(50)
)
AS
DECLARE @SecurityIdentityGuid UNIQUEIDENTIFIER
SET @SecurityIdentityGuid = dbo.SecurityGetIdentityGuidByUsername(@SecurityUsername);
-- Verify the identity has permission to perform task on the specified object
IF NOT EXISTS (
SELECT
RoleId
FROM
[dbo].[fnSecurityGetIdentityObjectRolesForProcedure](@SecurityIdentityGuid, 'dbo.wst_Merchant_Get_List'))
BEGIN
RAISERROR('identity is not a member of a valid role', 12 ,1)
RETURN -1
END
ELSE
BEGIN
The first couple of lines get the GUID for the username that is running the stored procedure. The next section calls a function that gets the role to which the user is assigned and that have been granted permission to execute the stored procedure. If there are no records for this the user does not have the permission required and an error is raised. Otherwise the user has permission and the stored procedure is able to execute as normal. This section is simply added to the scriptsql.xsl file in the templates
The second part of the customisations made to the stored procedures created by the .NetTiers application is the part that performs the row level filtering.
SELECT
[MerchantId],
[MerchantName],
[MerchantTypeId],
[RelationMerchantId],
[GroupMerchantId],
:
:
FROM
dbo.[Merchant]
WHERE
[MerchantId] IN
(SELECT
sec.MerchantId
FROM
[dbo].[fnSecurityGetUserMerchants] (@SecurityIdentityGuid) sec)
Select @@ROWCOUNT
The templates use columns names identified as requiring security filtering and adds the highlighted code to the WHERE clause. This function uses a hierarchical query to determine the MerchantIds that the specified user has permissions to. This functionality is added by modifying the StoredProcedureXml.cst file:
// [ab 012605] nonKeys/cols sans computed/read-only columns. This is for Insert/Update operations
ColumnSchemaCollection colsUpdatable = new ColumnSchemaCollection(cols.Count);
ColumnSchemaCollection nonKeysUpdatable = new ColumnSchemaCollection(nonKeys.Count);
// markd - Added to define the collection of column names that we want to secure through filtering
ColumnSchemaCollection colsSecured = new ColumnSchemaCollection(cols.Count);
// Holds the RowVersion column is there's any
ColumnSchema RowVersion = null;
foreach (ColumnSchema column in cols)
{
// markd - Added IsRowGuidColumn check to ensure that rowguid columns are not marked as updateable
// as this causes errors at runtime from SQL Server
if ( ! IsIdentityColumn(column) && ! IsComputed(column) && ! IsRowGuidColumn(column) )
colsUpdatable.Add(column);
if (column.NativeType.ToLower() == "timestamp")
RowVersion = column;
// markd - Added to populate the columns in this table that match the criteria for requiring filtering
if (column.Name.ToLower() == "merchantid"
|| column.Name.ToLower() == "siteid"
|| column.Name.ToLower() == "locationid"
|| column.Name.ToLower() == "productlocationid"
|| column.Name.ToLower() == "terminalid")
colsSecured.Add(column);
}
The above code contains several modifications to this section. First added a collection to hold the columns found in the table that require securing. Second added an if statement that looks at the list column name and compares it to the ones I have identified that need securing and if a match adds the column to this collection.
The third is a bug fix and not directly related to the security side of things, it now checks the rowguid property when determining if a column is updateable or not.
Then you need to modify the sections where the stored procedure types are generated. Here is an example of modification made to the GetList stored procedure section:
// -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
// GetList
if (IncludeGetList)
{%>
<procedure owner="<%=SourceTable.Owner%>" name="<%= ProcedurePrefix + commandStem + SelectSuffix + SelectAllSuffix %>" <%if (GrantUser != "") {%>grant="<%=GrantUser%>"<%}%>>
<comment><![CDATA[<%= GetProcSqlComment(string.Format("Gets all records from the {0} table", name), tableComment) %>]]></comment>
<parameters/>
<body><
.Name %>]<% if (i < cols.Count - 1) { %>,<% } %>
<% } %>
FROM
<%=owner%>[<%= SourceTable.Name %>]
<% if (colsSecured.Count > 0) { %>
WHERE
<% for(int index=0; index < colsSecured.Count; index++) { %>
<% if (index > 0) { %>AND <% } %><% = colsSecured[index].Name %> IN
(SELECT
sec.<% = colsSecured[index].Name %>
FROM
[dbo].[fnSecurityGet<% = colsSecured[index].Name %>] (@SecurityIdentityGuid) sec)
<% } %>
<% } %>
Select @@ROWCOUNT
]]></body>
</procedure>
As you can see I simply added some logic that checks if the collection of secured fields has any members and creates the necessary where clause that calls the function that has been designed to return the list of id’s applicable to that user.
The last part of the puzzle is how you pass the username to the stored procedure without making it a parameter that could simply be set by someone using the library.
By modifying the StoreProcedureProvider.cst file I add the parameter to the stored procedure automatically when the GetCommandWrapper is requested. When the Command Wrapper is requested the @SecurityUsername is added and the value is populated with the username on the thread.
NOTE: I have not tested this section fully yet as I am still having issues with the tiers and some of the changes in our schema. In my previous iteration I added this in each section of the SqlEntityProviderBase.Generated.cst file and this new method is much neater providing it works.
/// <summary>
/// Get the command wrapper, either from the stored procedures or from the embedded queries.
/// </summary>
/// <param name="database">The database instance.</param>
/// <param name="commandName">the name of the sp.</param>
/// <param name="useStoredProcedure">Indicates if we want stored procedure.</param>
/// <returns></returns>
public static DbCommand GetCommandWrapper(Database database, string commandName, bool useStoredProcedure)
{
if (useStoredProcedure)
{
DbCommand command = database.GetStoredProcCommand(commandName);
command.CommandTimeout = DataRepository.Provider.DefaultCommandTimeout;
database.AddInParameter(command, "@SecurityUsername", DbType.String, Thread.CurrentPrincipal.Identity.Name)
return command;
}
else
{
DbCommand command =
database.GetSqlStringCommand(StoredProcedureProvider.GetProcedureBodyFromEmbeddedResource(commandName));
command.CommandTimeout = DataRepository.Provider.DefaultCommandTimeout;
return command;
}
}
Web Services
I have tested this by building a Security Token Service that is able to issue SAML tokens providing a solution for heterogeneous clients and meaning the Web Service layer is able to attach the users identity to the thread and therefore the users identity is able to be tracked and verified through the applications layers.
References
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
http://www.gotdotnet.com/codegallery/codegallery.aspx?id=8da852b9-2c0d-4eb7-a2de-77222a4075f6
http://nettiers.com
http://codesmithtools.com