using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Web.Hosting;
using System.Web.Security;
namespace MvcSecurityApplication.Tools.SqlCeMembership
{
[SuppressMessage("Microsoft.Naming", "CA1709:IdentifiersShouldBeCasedCorrectly", MessageId = "Ce")]
public sealed class SqlCeRoleProvider : RoleProvider
{
//
// Global connection string, generic exception message, event log info.
//
private Guid _applicationId;
private string _connectionString;
private ConnectionStringSettings _connectionStringSettings;
private string eventLog = "Application";
private string eventSource = "SqlCeRoleProvider";
private string exceptionMessage = "An exception occurred. Please check the Event Log.";
//
// If false, exceptions are thrown to the caller. If true,
// exceptions are written to the event log.
//
public bool WriteExceptionsToEventLog { get; set; }
//
// System.Configuration.Provider.ProviderBase.Initialize Method
//
public override string ApplicationName { get; set; }
public override void Initialize(string name, NameValueCollection config)
{
//
// Initialize values from web.config.
//
if (config == null)
throw new ArgumentNullException("config");
if (name.Length == 0)
name = "SqlCeRoleProvider";
if (String.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SqlCe Role provider");
}
// Initialize the abstract base class.
base.Initialize(name, config);
ApplicationName = string.IsNullOrWhiteSpace(config["applicationName"])
? HostingEnvironment.ApplicationVirtualPath
: config["applicationName"];
if (config["writeExceptionsToEventLog"] != null)
{
if (config["writeExceptionsToEventLog"].ToUpperInvariant() == "TRUE")
WriteExceptionsToEventLog = true;
}
//
// Initialize SqlCeConnection.
//
_connectionStringSettings = ConfigurationManager.
ConnectionStrings[config["connectionStringName"]];
if (_connectionStringSettings == null || string.IsNullOrWhiteSpace(_connectionStringSettings.ConnectionString))
throw new ProviderException("Connection string cannot be blank.");
_connectionString = _connectionStringSettings.ConnectionString;
SqlCeMembershipUtils.CreateDatabaseIfRequired(_connectionString, ApplicationName);
_applicationId = SqlCeMembershipUtils.GetApplicationId(_connectionString, ApplicationName);
}
//
// System.Web.Security.RoleProvider methods.
//
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
if (roleNames.Any(rolename => !RoleExists(rolename)))
throw new ProviderException("Role name not found.");
foreach (var username in usernames)
{
if (username.IndexOf(',') > 0)
throw new ArgumentException("User names cannot contain commas.");
if (roleNames.Any(rolename => IsUserInRole(username, rolename)))
throw new ProviderException("User is already in role.");
}
using (var conn = new SqlCeConnection(_connectionString))
{
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
foreach (var username in usernames)
{
using (var cmd = new SqlCeCommand("DELETE FROM [aspnet_Users] " +
"WHERE Lower(UserName)=@UserName AND ApplicationID=@ApplicationID", conn))
{
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = username.ToLower();
cmd.Parameters.Add("@ApplicationID", SqlDbType.UniqueIdentifier).Value = _applicationId;
cmd.ExecuteNonQuery();
}
using (var cmd = new SqlCeCommand("INSERT INTO [aspnet_Users] " +
" (ApplicationID, UserName, LoweredUserName, LastActivityDate) " +
" Values(@ApplicationID, @UserName, @LoweredUserName, GetDate())", conn))
{
cmd.Parameters.Add("@ApplicationID", SqlDbType.UniqueIdentifier).Value = _applicationId;
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = username;
cmd.Parameters.Add("@LoweredUserName", SqlDbType.NVarChar).Value = username.ToLower();
cmd.ExecuteNonQuery();
}
}
using (var cmd = new SqlCeCommand("INSERT INTO [aspnet_UsersInRoles] " +
" (UserId, RoleId) " +
" Values(@UserId, @RoleId)", conn))
{
var userParm = cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
var roleParm = cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier);
cmd.Transaction = tran;
foreach (var username in usernames)
{
foreach (var rolename in roleNames)
{
userParm.Value = GetUserId(username);
roleParm.Value = GetRoleId(rolename);
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
}
catch (SqlCeException e)
{
if (tran != null) tran.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "AddUsersToRoles");
else
throw;
}
}
}
//
// RoleProvider.CreateRole
//
public override void CreateRole(string roleName)
{
if (roleName.IndexOf(',') > 0)
throw new ArgumentException("Role names cannot contain commas.");
if (RoleExists(roleName))
throw new ProviderException("Role name already exists.");
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand("INSERT INTO [aspnet_Roles] " +
" (Rolename, LoweredRoleName, ApplicationId) " +
" Values(@Rolename, @LoweredRoleName, @ApplicationId)", conn))
{
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 256).Value = roleName;
cmd.Parameters.Add("@LoweredRolename", SqlDbType.NVarChar, 256).Value = roleName.ToLowerInvariant();
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "CreateRole");
else
throw;
}
}
}
}
//
// RoleProvider.DeleteRole
//
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
if (!RoleExists(roleName))
throw new ProviderException("Role does not exist.");
if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0)
throw new ProviderException("Cannot delete a populated role.");
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand("DELETE FROM [aspnet_Roles] " +
" WHERE RoleId = @RoleId AND ApplicationId = @ApplicationId", conn))
{
cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = GetRoleId(roleName);
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
using (var cmd2 = new SqlCeCommand("DELETE FROM [aspnet_UsersInRoles]" +
" WHERE RoleId = @RoleId", conn))
{
cmd2.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = GetRoleId(roleName);
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteNonQuery();
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (SqlCeException e)
{
if (tran != null) tran.Rollback();
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
return false;
}
throw;
}
}
}
}
return true;
}
//
// RoleProvider.GetAllRoles
//
public override string[] GetAllRoles()
{
var tmpRoleNames = "";
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand("SELECT Rolename FROM [aspnet_Roles]" +
" WHERE ApplicationId = @ApplicationId", conn))
{
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
SqlCeDataReader reader;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
tmpRoleNames += reader.GetString(0) + ",";
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetAllRoles");
else
throw;
}
}
}
if (tmpRoleNames.Length > 0)
{
// Remove trailing comma.
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
return tmpRoleNames.Split(',');
}
return new string[0];
}
//
// RoleProvider.GetRolesForUser
//
public override string[] GetRolesForUser(string username)
{
var tmpRoleNames = "";
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand("SELECT Rolename FROM [aspnet_Roles] r, [aspnet_UsersInRoles] ur " +
" WHERE r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId and ur.UserId = @UserId ORDER BY RoleName", conn))
{
cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = GetUserId(username);
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
try
{
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
tmpRoleNames += reader.GetString(0) + ",";
}
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetRolesForUser");
else
throw;
}
}
}
if (tmpRoleNames.Length > 0)
{
// Remove trailing comma.
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
return tmpRoleNames.Split(',');
}
return new string[0];
}
//
// RoleProvider.GetUsersInRole
//
public override string[] GetUsersInRole(string roleName)
{
var tmpUserNames = "";
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand(
@" SELECT u.UserName
FROM aspnet_Users u, aspnet_UsersInRoles ur
WHERE u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
ORDER BY u.UserName", conn))
{
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
try
{
conn.Open();
var roleId = GetRoleId(roleName);
cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier).Value = roleId;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
tmpUserNames += reader.GetString(0) + ",";
}
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetUsersInRole");
else
throw;
}
}
}
if (tmpUserNames.Length > 0)
{
// Remove trailing comma.
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
return tmpUserNames.Split(',');
}
return new string[0];
}
//
// RoleProvider.IsUserInRole
//
public override bool IsUserInRole(string username, string roleName)
{
var userIsInRole = false;
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand(@"SELECT COUNT(*) FROM [aspnet_UsersInRoles], [aspnet_Users], [aspnet_Roles]
WHERE [aspnet_UsersInRoles].UserId = [aspnet_Users].UserId AND [aspnet_UsersInRoles].RoleId = aspnet_Roles.RoleId
AND [aspnet_Users].Username = @Username AND [aspnet_Roles].Rolename = @Rolename", conn))
{
cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 256).Value = username;
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 256).Value = roleName;
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
try
{
conn.Open();
var numRecs = (int)cmd.ExecuteScalar();
if (numRecs > 0)
userIsInRole = true;
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "IsUserInRole");
else
throw;
}
}
}
return userIsInRole;
}
//
// RoleProvider.RemoveUsersFromRoles
//
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
if (roleNames.Any(rolename => !RoleExists(rolename)))
throw new ProviderException("Role name not found.");
if ((from username in usernames from rolename in roleNames where !IsUserInRole(username, rolename) select username).Any())
throw new ProviderException("User is not in role.");
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand("DELETE FROM [aspnet_UsersInRoles]" +
" WHERE UserId = @UserId AND RoleId = @RoleId", conn))
{
var userParm = cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
var roleParm = cmd.Parameters.Add("@RoleId", SqlDbType.UniqueIdentifier);
SqlCeTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (var username in usernames)
{
foreach (var rolename in roleNames)
{
userParm.Value = GetUserId(username);
roleParm.Value = GetRoleId(rolename);
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (SqlCeException e)
{
if (tran != null) tran.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "RemoveUsersFromRoles");
else
throw;
}
}
}
}
//
// RoleProvider.RoleExists
//
public override bool RoleExists(string roleName)
{
var exists = false;
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand("SELECT COUNT(*) FROM [aspnet_Roles]" +
" WHERE Rolename = @Rolename AND ApplicationId = @ApplicationId", conn))
{
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 256).Value = roleName;
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
try
{
conn.Open();
var numRecs = (int)cmd.ExecuteScalar();
if (numRecs > 0)
exists = true;
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "RoleExists");
else
throw;
}
}
}
return exists;
}
//
// RoleProvider.FindUsersInRole
//
public override string[] FindUsersInRole(string roleName, string usernameToMatch)
{
var tmpUserNames = "";
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand("SELECT Username FROM [aspnet_UsersInRoles]" +
"WHERE Username LIKE @UsernameSearch AND Rolename = @Rolename AND ApplicationId = @ApplicationId", conn))
{
cmd.Parameters.Add("@UsernameSearch", SqlDbType.NVarChar, 256).Value = usernameToMatch;
cmd.Parameters.Add("@RoleName", SqlDbType.NVarChar, 256).Value = roleName;
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
try
{
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
tmpUserNames += reader.GetString(0) + ",";
}
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "FindUsersInRole");
else
throw;
}
}
}
if (tmpUserNames.Length > 0)
{
// Remove trailing comma.
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
return tmpUserNames.Split(',');
}
return new string[0];
}
//
// WriteToEventLog
// A helper function that writes exception detail to the event log. Exceptions
// are written to the event log as a security measure to avoid private database
// details from being returned to the browser. If a method does not return a status
// or boolean indicating the action succeeded or failed, a generic exception is also
// thrown by the caller.
//
private Guid GetRoleId(string roleName)
{
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand(@"SELECT RoleId
FROM aspnet_Roles
WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId", conn))
{
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 256).Value = roleName;
{
try
{
conn.Open();
var result = cmd.ExecuteScalar() as Guid?;
if (result.HasValue)
return result.Value;
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetRoleId");
else
throw;
}
}
}
}
return Guid.Empty;
}
private Guid GetUserId(string userName)
{
using (var conn = new SqlCeConnection(_connectionString))
{
using (var cmd = new SqlCeCommand(@"SELECT UserId
FROM aspnet_Users
WHERE LOWER(@UserName) = LoweredUserName AND ApplicationId = @ApplicationId", conn))
{
cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = _applicationId;
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = userName;
{
try
{
conn.Open();
var result = cmd.ExecuteScalar() as Guid?;
if (result.HasValue)
return result.Value;
}
catch (SqlCeException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetUserId");
else
throw;
}
}
}
}
return Guid.Empty;
}
private void WriteToEventLog(SqlCeException e, string action)
{
using (var log = new EventLog())
{
log.Source = eventSource;
log.Log = eventLog;
var message = exceptionMessage + "\n\n";
message += "Action: " + action + "\n\n";
message += "Exception: " + e;
log.WriteEntry(message);
}
}
}
}
Submit an article or tip