2 // System.Web.Security.SqlRoleProvider
5 // Ben Maurer (bmaurer@users.sourceforge.net)
6 // Chris Toshok (toshok@ximian.com)
9 // Copyright (c) 2005,2006 Novell, Inc (http://www.novell.com)
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:
19 // The above copyright notice and this permission notice shall be
20 // included in all copies or substantial portions of the Software.
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.
33 using System.Collections;
34 using System.Collections.Specialized;
36 using System.Data.Common;
37 using System.Configuration;
38 using System.Configuration.Provider;
39 using System.Web.Configuration;
41 namespace System.Web.Security
44 public class SqlRoleProvider : RoleProvider
47 string applicationName;
48 bool schemaIsOk = false;
50 ConnectionStringSettings connectionString;
51 DbProviderFactory factory;
53 DbConnection CreateConnection ()
55 if (!schemaIsOk && !(schemaIsOk = AspNetDBSchemaChecker.CheckMembershipSchemaVersion (factory, connectionString.ConnectionString, "role manager", "1")))
56 throw new ProviderException ("Incorrect ASP.NET DB Schema Version.");
58 DbConnection connection = factory.CreateConnection ();
59 connection.ConnectionString = connectionString.ConnectionString;
65 static void AddParameter (DbCommand command, string parameterName, object parameterValue)
67 AddParameter (command, parameterName, ParameterDirection.Input, parameterValue);
70 static DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
72 DbParameter dbp = command.CreateParameter ();
73 dbp.ParameterName = parameterName;
74 dbp.Value = parameterValue;
75 dbp.Direction = direction;
76 command.Parameters.Add (dbp);
80 static DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, DbType type, object parameterValue)
82 DbParameter dbp = command.CreateParameter ();
83 dbp.ParameterName = parameterName;
84 dbp.Value = parameterValue;
85 dbp.Direction = direction;
87 command.Parameters.Add (dbp);
91 public override void AddUsersToRoles (string [] usernames, string [] rolenames)
93 Hashtable h = new Hashtable ();
95 foreach (string u in usernames) {
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");
105 h = new Hashtable ();
106 foreach (string r in rolenames) {
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");
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;
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);
129 command.ExecuteNonQuery ();
131 int returnValue = (int) dbpr.Value;
132 if (returnValue == 0)
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.");
139 throw new ProviderException ("Failed to create new user/role association.");
143 public override void CreateRole (string rolename)
145 if (rolename == null)
146 throw new ArgumentNullException ("rolename");
148 if (rolename.Length == 0 || rolename.Length > 256 || rolename.IndexOf (',') != -1)
149 throw new ArgumentException ("rolename is in invalid format");
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;
157 AddParameter (command, "@ApplicationName", ApplicationName);
158 AddParameter (command, "@RoleName", rolename);
159 DbParameter dbpr = AddParameter (command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
161 command.ExecuteNonQuery ();
162 int returnValue = (int) dbpr.Value;
164 if (returnValue == 1)
165 throw new ProviderException (rolename + " already exists in the database");
171 public override bool DeleteRole (string rolename, bool throwOnPopulatedRole)
173 if (rolename == null)
174 throw new ArgumentNullException ("rolename");
176 if (rolename.Length == 0 || rolename.Length > 256 || rolename.IndexOf (',') != -1)
177 throw new ArgumentException ("rolename is in invalid format");
179 using (DbConnection connection = CreateConnection ()) {
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);
190 command.ExecuteNonQuery ();
191 int returnValue = (int)dbpr.Value;
193 if (returnValue == 0)
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");
204 public override string [] FindUsersInRole (string roleName, string usernameToMatch)
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");
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;
221 AddParameter (command, "@ApplicationName", ApplicationName);
222 AddParameter (command, "@RoleName", roleName);
223 AddParameter (command, "@UsernameToMatch", usernameToMatch);
225 DbDataReader reader = command.ExecuteReader ();
226 ArrayList userList = new ArrayList ();
227 while (reader.Read ())
228 userList.Add (reader.GetString (0));
231 return (string []) userList.ToArray (typeof (string));
235 public override string [] GetAllRoles ()
237 using (DbConnection connection = CreateConnection ()) {
238 DbCommand command = factory.CreateCommand ();
239 command.CommandText = @"dbo.aspnet_Roles_GetAllRoles";
240 command.Connection = connection;
242 command.CommandType = CommandType.StoredProcedure;
243 AddParameter (command, "@ApplicationName", ApplicationName);
245 DbDataReader reader = command.ExecuteReader ();
246 ArrayList roleList = new ArrayList ();
247 while (reader.Read ())
248 roleList.Add (reader.GetString (0));
251 return (string []) roleList.ToArray (typeof (string));
255 public override string [] GetRolesForUser (string username)
257 using (DbConnection connection = CreateConnection ()) {
258 DbCommand command = factory.CreateCommand ();
259 command.CommandText = @"dbo.aspnet_UsersInRoles_GetRolesForUser";
260 command.Connection = connection;
262 command.CommandType = CommandType.StoredProcedure;
263 AddParameter (command, "@UserName", username);
264 AddParameter (command, "@ApplicationName", ApplicationName);
266 DbDataReader reader = command.ExecuteReader ();
267 ArrayList roleList = new ArrayList ();
268 while (reader.Read ())
269 roleList.Add (reader.GetString (0));
272 return (string []) roleList.ToArray (typeof (string));
276 public override string [] GetUsersInRole (string rolename)
278 using (DbConnection connection = CreateConnection ()) {
279 DbCommand command = factory.CreateCommand ();
280 command.CommandText = @"dbo.aspnet_UsersInRoles_GetUsersInRoles";
281 command.Connection = connection;
283 command.CommandType = CommandType.StoredProcedure;
284 AddParameter (command, "@RoleName", rolename);
285 AddParameter (command, "@ApplicationName", ApplicationName);
287 DbDataReader reader = command.ExecuteReader ();
288 ArrayList userList = new ArrayList ();
289 while (reader.Read ())
290 userList.Add (reader.GetString (0));
293 return (string []) userList.ToArray (typeof (string));
297 string GetStringConfigValue (NameValueCollection config, string name, string def)
300 string val = config [name];
306 public override void Initialize (string name, NameValueCollection config)
309 throw new ArgumentNullException ("config");
311 base.Initialize (name, config);
313 applicationName = GetStringConfigValue (config, "applicationName", "/");
314 string connectionStringName = config ["connectionStringName"];
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.");
321 // XXX check connectionStringName and commandTimeout
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);
331 public override bool IsUserInRole (string username, string rolename)
333 using (DbConnection connection = CreateConnection ()) {
334 DbCommand command = factory.CreateCommand ();
335 command.CommandText = @"dbo.aspnet_UsersInRoles_IsUserInRole";
336 command.Connection = connection;
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);
344 command.ExecuteNonQuery ();
345 int returnValue = (int) dbpr.Value;
347 if (returnValue == 1)
354 public override void RemoveUsersFromRoles (string [] usernames, string [] rolenames)
356 Hashtable h = new Hashtable ();
358 foreach (string u in usernames) {
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");
368 h = new Hashtable ();
369 foreach (string r in rolenames) {
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");
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;
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);
390 command.ExecuteNonQuery ();
391 int returnValue = (int) dbpr.Value;
393 if (returnValue == 0)
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.");
402 throw new ProviderException ("Failed to remove users from roles");
406 public override bool RoleExists (string rolename)
408 using (DbConnection connection = CreateConnection ()) {
410 DbCommand command = factory.CreateCommand ();
411 command.CommandText = @"dbo.aspnet_Roles_RoleExists";
412 command.Connection = connection;
413 command.CommandType = CommandType.StoredProcedure;
415 AddParameter (command, "@ApplicationName", ApplicationName);
416 AddParameter (command, "@RoleName", rolename);
417 DbParameter dbpr = AddParameter (command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
419 command.ExecuteNonQuery ();
420 int returnValue = (int) dbpr.Value;
422 if (returnValue == 1)
429 public override string ApplicationName
431 get { return applicationName; }
434 applicationName = value;