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