mcs/class/System.Web/System.Web.Security/SqlRoleProvider.cs

   1 //
   2 // System.Web.Security.SqlRoleProvider
   3 //
   4 // Authors:
   5 //      Ben Maurer (bmaurer@users.sourceforge.net)
   6 //      Chris Toshok (toshok@ximian.com)
   7 //
   8 // (C) 2003 Ben Maurer
   9 // Copyright (c) 2005,2006 Novell, Inc (http://www.novell.com)
  10 //
  11 // Permission is hereby granted, free of charge, to any person obtaining
  12 // a copy of this software and associated documentation files (the
  13 // "Software"), to deal in the Software without restriction, including
  14 // without limitation the rights to use, copy, modify, merge, publish,
  15 // distribute, sublicense, and/or sell copies of the Software, and to
  16 // permit persons to whom the Software is furnished to do so, subject to
  17 // the following conditions:
  18 //
  19 // The above copyright notice and this permission notice shall be
  20 // included in all copies or substantial portions of the Software.
  21 //
  22 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  23 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  24 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  25 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  26 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  27 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  28 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  29 //
  30
  31
  32 using System.Collections;
  33 using System.Collections.Specialized;
  34 using System.Data;
  35 using System.Data.Common;
  36 using System.Configuration;
  37 using System.Configuration.Provider;
  38 using System.Web.Configuration;
  39
  40 namespace System.Web.Security
  41 {
  42
  43         public class SqlRoleProvider : RoleProvider
  44         {
  45
  46                 string applicationName;
  47                 bool schemaIsOk = false;
  48
  49                 ConnectionStringSettings connectionString;
  50                 DbProviderFactory factory;
  51
  52                 DbConnection CreateConnection ()
  53                 {
  54                         if (!schemaIsOk && !(schemaIsOk = AspNetDBSchemaChecker.CheckMembershipSchemaVersion (factory, connectionString.ConnectionString, "role manager", "1")))
  55                                 throw new ProviderException ("Incorrect ASP.NET DB Schema Version.");
  56
  57                         DbConnection connection = factory.CreateConnection ();
  58                         connection.ConnectionString = connectionString.ConnectionString;
  59
  60                         connection.Open ();
  61                         return connection;
  62                 }
  63
  64                 static void AddParameter (DbCommand command, string parameterName, object parameterValue)
  65                 {
  66                         AddParameter (command, parameterName, ParameterDirection.Input, parameterValue);
  67                 }
  68
  69                 static DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
  70                 {
  71                         DbParameter dbp = command.CreateParameter ();
  72                         dbp.ParameterName = parameterName;
  73                         dbp.Value = parameterValue;
  74                         dbp.Direction = direction;
  75                         command.Parameters.Add (dbp);
  76                         return dbp;
  77                 }
  78
  79                 static DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, DbType type, object parameterValue)
  80                 {
  81                         DbParameter dbp = command.CreateParameter ();
  82                         dbp.ParameterName = parameterName;
  83                         dbp.Value = parameterValue;
  84                         dbp.Direction = direction;
  85                         dbp.DbType = type;
  86                         command.Parameters.Add (dbp);
  87                         return dbp;
  88                 }
  89
  90                 public override void AddUsersToRoles (string [] usernames, string [] roleNames)
  91                 {
  92                         Hashtable h = new Hashtable ();
  93
  94                         foreach (string u in usernames) {
  95                                 if (u == null)
  96                                         throw new ArgumentNullException ("null element in usernames array");
  97                                 if (h.ContainsKey (u))
  98                                         throw new ArgumentException ("duplicate element in usernames array");
  99                                 if (u.Length == 0 || u.Length > 256 || u.IndexOf (',') != -1)
 100                                         throw new ArgumentException ("element in usernames array in illegal format");
 101                                 h.Add (u, u);
 102                         }
 103
 104                         h = new Hashtable ();
 105                         foreach (string r in roleNames) {
 106                                 if (r == null)
 107                                         throw new ArgumentNullException ("null element in rolenames array");
 108                                 if (h.ContainsKey (r))
 109                                         throw new ArgumentException ("duplicate element in rolenames array");
 110                                 if (r.Length == 0 || r.Length > 256 || r.IndexOf (',') != -1)
 111                                         throw new ArgumentException ("element in rolenames array in illegal format");
 112                                 h.Add (r, r);
 113                         }
 114
 115                         using (DbConnection connection = CreateConnection ()) {
 116                                 /* add the user/role combination to dbo.aspnet_UsersInRoles */
 117                                 DbCommand command = factory.CreateCommand ();
 118                                 command.CommandText = @"dbo.aspnet_UsersInRoles_AddUsersToRoles";
 119                                 command.Connection = connection;
 120                                 command.CommandType = CommandType.StoredProcedure;
 121
 122                                 AddParameter (command, "@RoleNames", String.Join (",", roleNames));
 123                                 AddParameter (command, "@UserNames", String.Join (",", usernames));
 124                                 AddParameter (command, "@ApplicationName", ApplicationName);
 125                                 AddParameter (command, "@CurrentTimeUtc", DateTime.UtcNow);
 126                                 DbParameter dbpr = AddParameter (command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
 127
 128                                 command.ExecuteNonQuery ();
 129
 130                                 int returnValue = (int) dbpr.Value;
 131                                 if (returnValue == 0)
 132                                         return;
 133                                 else if (returnValue == 2)
 134                                         throw new ProviderException ("One or more of the specified user/role names was not found.");
 135                                 else if (returnValue == 3)
 136                                         throw new ProviderException ("One or more of the specified user names is already associated with one or more of the specified role names.");
 137                                 else
 138                                         throw new ProviderException ("Failed to create new user/role association.");
 139                         }
 140                 }
 141
 142                 public override void CreateRole (string roleName)
 143                 {
 144                         if (roleName == null)
 145                                 throw new ArgumentNullException ("roleName");
 146
 147                         if (roleName.Length == 0 || roleName.Length > 256 || roleName.IndexOf (',') != -1)
 148                                 throw new ArgumentException ("rolename is in invalid format");
 149
 150                         using (DbConnection connection = CreateConnection ()) {
 151                                 DbCommand command = factory.CreateCommand ();
 152                                 command.CommandText = @"dbo.aspnet_Roles_CreateRole";
 153                                 command.Connection = connection;
 154                                 command.CommandType = CommandType.StoredProcedure;
 155
 156                                 AddParameter (command, "@ApplicationName", ApplicationName);
 157                                 AddParameter (command, "@RoleName", roleName);
 158                                 DbParameter dbpr = AddParameter (command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
 159
 160                                 command.ExecuteNonQuery ();
 161                                 int returnValue = (int) dbpr.Value;
 162
 163                                 if (returnValue == 1)
 164                                         throw new ProviderException (roleName + " already exists in the database");
 165                                 else
 166                                         return;
 167                         }
 168                 }
 169
 170                 public override bool DeleteRole (string roleName, bool throwOnPopulatedRole)
 171                 {
 172                         if (roleName == null)
 173                                 throw new ArgumentNullException ("roleName");
 174
 175                         if (roleName.Length == 0 || roleName.Length > 256 || roleName.IndexOf (',') != -1)
 176                                 throw new ArgumentException ("rolename is in invalid format");
 177
 178                         using (DbConnection connection = CreateConnection ()) {
 179
 180                                 DbCommand command = factory.CreateCommand ();
 181                                 command.CommandText = @"dbo.aspnet_Roles_DeleteRole";
 182                                 command.Connection = connection;
 183                                 command.CommandType = CommandType.StoredProcedure;
 184                                 AddParameter (command, "@ApplicationName", ApplicationName);
 185                                 AddParameter (command, "@RoleName", roleName);
 186                                 AddParameter (command, "@DeleteOnlyIfRoleIsEmpty", throwOnPopulatedRole);
 187                                 DbParameter dbpr = AddParameter (command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
 188
 189                                 command.ExecuteNonQuery ();
 190                                 int returnValue = (int)dbpr.Value;
 191
 192                                 if (returnValue == 0)
 193                                         return true;
 194                                 if (returnValue == 1)
 195                                         return false; //role does not exist
 196                                 else if (returnValue == 2 && throwOnPopulatedRole)
 197                                         throw new ProviderException (roleName + " is not empty");
 198                                 else
 199                                         return false;
 200                         }
 201                 }
 202
 203                 public override string [] FindUsersInRole (string roleName, string usernameToMatch)
 204                 {
 205                         if (roleName == null)
 206                                 throw new ArgumentNullException ("roleName");
 207                         if (usernameToMatch == null)
 208                                 throw new ArgumentNullException ("usernameToMatch");
 209                         if (roleName.Length == 0 || roleName.Length > 256 || roleName.IndexOf (',') != -1)
 210                                 throw new ArgumentException ("roleName is in invalid format");
 211                         if (usernameToMatch.Length == 0 || usernameToMatch.Length > 256)
 212                                 throw new ArgumentException ("usernameToMatch is in invalid format");
 213
 214                         using (DbConnection connection = CreateConnection ()) {
 215                                 DbCommand command = factory.CreateCommand ();
 216                                 command.Connection = connection;
 217                                 command.CommandText = @"dbo.aspnet_UsersInRoles_FindUsersInRole";
 218                                 command.CommandType = CommandType.StoredProcedure;
 219
 220                                 AddParameter (command, "@ApplicationName", ApplicationName);
 221                                 AddParameter (command, "@RoleName", roleName);
 222                                 AddParameter (command, "@UsernameToMatch", usernameToMatch);
 223
 224                                 DbDataReader reader = command.ExecuteReader ();
 225                                 ArrayList userList = new ArrayList ();
 226                                 while (reader.Read ())
 227                                         userList.Add (reader.GetString (0));
 228                                 reader.Close ();
 229
 230                                 return (string []) userList.ToArray (typeof (string));
 231                         }
 232                 }
 233
 234                 public override string [] GetAllRoles ()
 235                 {
 236                         using (DbConnection connection = CreateConnection ()) {
 237                                 DbCommand command = factory.CreateCommand ();
 238                                 command.CommandText = @"dbo.aspnet_Roles_GetAllRoles";
 239                                 command.Connection = connection;
 240
 241                                 command.CommandType = CommandType.StoredProcedure;
 242                                 AddParameter (command, "@ApplicationName", ApplicationName);
 243
 244                                 DbDataReader reader = command.ExecuteReader ();
 245                                 ArrayList roleList = new ArrayList ();
 246                                 while (reader.Read ())
 247                                         roleList.Add (reader.GetString (0));
 248                                 reader.Close ();
 249
 250                                 return (string []) roleList.ToArray (typeof (string));
 251                         }
 252                 }
 253
 254                 public override string [] GetRolesForUser (string username)
 255                 {
 256                         using (DbConnection connection = CreateConnection ()) {
 257                                 DbCommand command = factory.CreateCommand ();
 258                                 command.CommandText = @"dbo.aspnet_UsersInRoles_GetRolesForUser";
 259                                 command.Connection = connection;
 260
 261                                 command.CommandType = CommandType.StoredProcedure;
 262                                 AddParameter (command, "@UserName", username);
 263                                 AddParameter (command, "@ApplicationName", ApplicationName);
 264
 265                                 DbDataReader reader = command.ExecuteReader ();
 266                                 ArrayList roleList = new ArrayList ();
 267                                 while (reader.Read ())
 268                                         roleList.Add (reader.GetString (0));
 269                                 reader.Close ();
 270
 271                                 return (string []) roleList.ToArray (typeof (string));
 272                         }
 273                 }
 274
 275                 public override string [] GetUsersInRole (string roleName)
 276                 {
 277                         using (DbConnection connection = CreateConnection ()) {
 278                                 DbCommand command = factory.CreateCommand ();
 279                                 command.CommandText = @"dbo.aspnet_UsersInRoles_GetUsersInRoles";
 280                                 command.Connection = connection;
 281
 282                                 command.CommandType = CommandType.StoredProcedure;
 283                                 AddParameter (command, "@RoleName", roleName);
 284                                 AddParameter (command, "@ApplicationName", ApplicationName);
 285
 286                                 DbDataReader reader = command.ExecuteReader ();
 287                                 ArrayList userList = new ArrayList ();
 288                                 while (reader.Read ())
 289                                         userList.Add (reader.GetString (0));
 290                                 reader.Close ();
 291
 292                                 return (string []) userList.ToArray (typeof (string));
 293                         }
 294                 }
 295
 296                 string GetStringConfigValue (NameValueCollection config, string name, string def)
 297                 {
 298                         string rv = def;
 299                         string val = config [name];
 300                         if (val != null)
 301                                 rv = val;
 302                         return rv;
 303                 }
 304
 305                 public override void Initialize (string name, NameValueCollection config)
 306                 {
 307                         if (config == null)
 308                                 throw new ArgumentNullException ("config");
 309
 310                         base.Initialize (name, config);
 311
 312                         applicationName = GetStringConfigValue (config, "applicationName", "/");
 313                         string connectionStringName = config ["connectionStringName"];
 314
 315                         if (applicationName.Length > 256)
 316                                 throw new ProviderException ("The ApplicationName attribute must be 256 characters long or less.");
 317                         if (connectionStringName == null || connectionStringName.Length == 0)
 318                                 throw new ProviderException ("The ConnectionStringName attribute must be present and non-zero length.");
 319
 320                         // XXX check connectionStringName and commandTimeout
 321
 322                         connectionString = WebConfigurationManager.ConnectionStrings [connectionStringName];
 323                         if (connectionString == null)
 324                                 throw new ProviderException (String.Format("The connection name '{0}' was not found in the applications configuration or the connection string is empty.", connectionStringName));
 325                         factory = String.IsNullOrEmpty (connectionString.ProviderName) ?
 326                                 System.Data.SqlClient.SqlClientFactory.Instance :
 327                                 ProvidersHelper.GetDbProviderFactory (connectionString.ProviderName);
 328                 }
 329
 330                 public override bool IsUserInRole (string username, string roleName)
 331                 {
 332                         using (DbConnection connection = CreateConnection ()) {
 333                                 DbCommand command = factory.CreateCommand ();
 334                                 command.CommandText = @"dbo.aspnet_UsersInRoles_IsUserInRole";
 335                                 command.Connection = connection;
 336
 337                                 command.CommandType = CommandType.StoredProcedure;
 338                                 AddParameter (command, "@RoleName", roleName);
 339                                 AddParameter (command, "@UserName", username);
 340                                 AddParameter (command, "@ApplicationName", ApplicationName);
 341                                 DbParameter dbpr = AddParameter (command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
 342
 343                                 command.ExecuteNonQuery ();
 344                                 int returnValue = (int) dbpr.Value;
 345
 346                                 if (returnValue == 1)
 347                                         return true;
 348
 349                                 return false;
 350                         }
 351                 }
 352
 353                 public override void RemoveUsersFromRoles (string [] usernames, string [] roleNames)
 354                 {
 355                         Hashtable h = new Hashtable ();
 356
 357                         foreach (string u in usernames) {
 358                                 if (u == null)
 359                                         throw new ArgumentNullException ("null element in usernames array");
 360                                 if (h.ContainsKey (u))
 361                                         throw new ArgumentException ("duplicate element in usernames array");
 362                                 if (u.Length == 0 || u.Length > 256 || u.IndexOf (',') != -1)
 363                                         throw new ArgumentException ("element in usernames array in illegal format");
 364                                 h.Add (u, u);
 365                         }
 366
 367                         h = new Hashtable ();
 368                         foreach (string r in roleNames) {
 369                                 if (r == null)
 370                                         throw new ArgumentNullException ("null element in rolenames array");
 371                                 if (h.ContainsKey (r))
 372                                         throw new ArgumentException ("duplicate element in rolenames array");
 373                                 if (r.Length == 0 || r.Length > 256 || r.IndexOf (',') != -1)
 374                                         throw new ArgumentException ("element in rolenames array in illegal format");
 375                                 h.Add (r, r);
 376                         }
 377
 378                         using (DbConnection connection = CreateConnection ()) {
 379                                 DbCommand command = factory.CreateCommand ();
 380                                 command.CommandText = @"dbo.aspnet_UsersInRoles_RemoveUsersFromRoles";
 381                                 command.Connection = connection;
 382                                 command.CommandType = CommandType.StoredProcedure;
 383
 384                                 AddParameter (command, "@UserNames", String.Join (",", usernames));
 385                                 AddParameter (command, "@RoleNames", String.Join (",", roleNames));
 386                                 AddParameter (command, "@ApplicationName", ApplicationName);
 387                                 DbParameter dbpr = AddParameter (command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
 388
 389                                 command.ExecuteNonQuery ();
 390                                 int returnValue = (int) dbpr.Value;
 391
 392                                 if (returnValue == 0)
 393                                         return;
 394                                 else if (returnValue == 1)
 395                                         throw new ProviderException ("One or more of the specified user names was not found.");
 396                                 else if (returnValue == 2)
 397                                         throw new ProviderException ("One or more of the specified role names was not found.");
 398                                 else if (returnValue == 3)
 399                                         throw new ProviderException ("One or more of the specified user names is not associated with one or more of the specified role names.");
 400                                 else
 401                                         throw new ProviderException ("Failed to remove users from roles");
 402                         }
 403                 }
 404
 405                 public override bool RoleExists (string roleName)
 406                 {
 407                         using (DbConnection connection = CreateConnection ()) {
 408
 409                                 DbCommand command = factory.CreateCommand ();
 410                                 command.CommandText = @"dbo.aspnet_Roles_RoleExists";
 411                                 command.Connection = connection;
 412                                 command.CommandType = CommandType.StoredProcedure;
 413
 414                                 AddParameter (command, "@ApplicationName", ApplicationName);
 415                                 AddParameter (command, "@RoleName", roleName);
 416                                 DbParameter dbpr = AddParameter (command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
 417
 418                                 command.ExecuteNonQuery ();
 419                                 int returnValue = (int) dbpr.Value;
 420
 421                                 if (returnValue == 1)
 422                                         return true;
 423
 424                                 return false;
 425                         }
 426                 }
 427
 428                 public override string ApplicationName
 429                 {
 430                         get { return applicationName; }
 431                         set
 432                         {
 433                                 applicationName = value;
 434                         }
 435                 }
 436         }
 437 }
 438