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