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;
49 ConnectionStringSettings connectionString;
50 DbProviderFactory factory;
52 DbConnection CreateConnection ()
54 DbConnection connection = factory.CreateConnection ();
55 connection.ConnectionString = connectionString.ConnectionString;
61 static void AddParameter (DbCommand command, string parameterName, object parameterValue)
63 AddParameter (command, parameterName, ParameterDirection.Input, parameterValue);
66 static DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
68 DbParameter dbp = command.CreateParameter ();
69 dbp.ParameterName = parameterName;
70 dbp.Value = parameterValue;
71 dbp.Direction = direction;
72 command.Parameters.Add (dbp);
76 public override void AddUsersToRoles (string [] usernames, string [] rolenames)
78 Hashtable h = new Hashtable ();
80 foreach (string u in usernames) {
82 throw new ArgumentNullException ("null element in usernames array");
83 if (h.ContainsKey (u))
84 throw new ArgumentException ("duplicate element in usernames array");
85 if (u.Length == 0 || u.Length > 256 || u.IndexOf (",") != -1)
86 throw new ArgumentException ("element in usernames array in illegal format");
91 foreach (string r in rolenames) {
93 throw new ArgumentNullException ("null element in rolenames array");
94 if (h.ContainsKey (r))
95 throw new ArgumentException ("duplicate element in rolenames array");
96 if (r.Length == 0 || r.Length > 256 || r.IndexOf (",") != -1)
97 throw new ArgumentException ("element in rolenames array in illegal format");
101 using (DbConnection connection = CreateConnection ()) {
102 /* add the user/role combination to dbo.aspnet_UsersInRoles */
103 DbCommand command = factory.CreateCommand ();
104 command.CommandText = @"dbo.aspnet_UsersInRoles_AddUsersToRoles";
105 command.Connection = connection;
106 command.CommandType = CommandType.StoredProcedure;
108 AddParameter (command, "RoleNames", String.Join (",", rolenames));
109 AddParameter (command, "UserNames", String.Join (",", usernames));
110 AddParameter (command, "ApplicationName", ApplicationName);
111 AddParameter (command, "CurrentTimeUtc", DateTime.UtcNow);
112 DbParameter dbpr = AddParameter (command, null, ParameterDirection.ReturnValue, null);
114 command.ExecuteNonQuery ();
116 int returnValue = (int) dbpr.Value;
117 if (returnValue == 0)
119 else if (returnValue == 2)
120 throw new ProviderException ("One or more of the specified user/role names was not found.");
121 else if (returnValue == 3)
122 throw new ProviderException ("One or more of the specified user names is already associated with one or more of the specified role names.");
124 throw new ProviderException ("Failed to create new user/role association.");
128 public override void CreateRole (string rolename)
130 if (rolename == null)
131 throw new ArgumentNullException ("rolename");
133 if (rolename.Length == 0 || rolename.Length > 256 || rolename.IndexOf (",") != -1)
134 throw new ArgumentException ("rolename is in invalid format");
136 using (DbConnection connection = CreateConnection ()) {
137 DbCommand command = factory.CreateCommand ();
138 command.CommandText = @"dbo.aspnet_Roles_CreateRole";
139 command.Connection = connection;
140 command.CommandType = CommandType.StoredProcedure;
142 AddParameter (command, "ApplicationName", ApplicationName);
143 AddParameter (command, "RoleName", rolename);
144 DbParameter dbpr = AddParameter (command, null, ParameterDirection.ReturnValue, null);
146 command.ExecuteNonQuery ();
147 int returnValue = (int) dbpr.Value;
149 if (returnValue == 1)
150 throw new ProviderException (rolename + " already exists in the database");
156 public override bool DeleteRole (string rolename, bool throwOnPopulatedRole)
158 if (rolename == null)
159 throw new ArgumentNullException ("rolename");
161 if (rolename.Length == 0 || rolename.Length > 256 || rolename.IndexOf (",") != -1)
162 throw new ArgumentException ("rolename is in invalid format");
164 using (DbConnection connection = CreateConnection ()) {
166 DbCommand command = factory.CreateCommand ();
167 command.CommandText = @"dbo.aspnet_Roles_DeleteRole";
168 command.Connection = connection;
169 command.CommandType = CommandType.StoredProcedure;
170 AddParameter (command, "ApplicationName", ApplicationName);
171 AddParameter (command, "RoleName", rolename);
172 AddParameter (command, "DeleteOnlyIfRoleIsEmpty", throwOnPopulatedRole);
173 DbParameter dbpr = AddParameter (command, null, ParameterDirection.ReturnValue, null);
175 command.ExecuteNonQuery ();
176 int returnValue = (int)dbpr.Value;
178 if (returnValue == 0)
180 if (returnValue == 1)
181 return false; //role does not exists
182 else if (returnValue == 2 && throwOnPopulatedRole)
183 throw new ProviderException (rolename + " is not empty");
189 public override string [] FindUsersInRole (string roleName, string usernameToMatch)
191 if (roleName == null)
192 throw new ArgumentNullException ("roleName");
193 if (usernameToMatch == null)
194 throw new ArgumentNullException ("usernameToMatch");
195 if (roleName.Length == 0 || roleName.Length > 256 || roleName.IndexOf (",") != -1)
196 throw new ArgumentException ("roleName is in invalid format");
197 if (usernameToMatch.Length == 0 || usernameToMatch.Length > 256)
198 throw new ArgumentException ("usernameToMatch is in invalid format");
200 using (DbConnection connection = CreateConnection ()) {
201 DbCommand command = factory.CreateCommand ();
202 command.Connection = connection;
203 command.CommandText = @"dbo.aspnet_UsersInRoles_FindUsersInRole";
204 command.CommandType = CommandType.StoredProcedure;
206 AddParameter (command, "ApplicationName", ApplicationName);
207 AddParameter (command, "RoleName", roleName);
208 AddParameter (command, "UsernameToMatch", usernameToMatch);
210 DbDataReader reader = command.ExecuteReader ();
211 ArrayList userList = new ArrayList ();
212 while (reader.Read ())
213 userList.Add (reader.GetString (0));
216 return (string []) userList.ToArray (typeof (string));
220 public override string [] GetAllRoles ()
222 using (DbConnection connection = CreateConnection ()) {
223 DbCommand command = factory.CreateCommand ();
224 command.CommandText = @"dbo.aspnet_Roles_GetAllRoles";
225 command.Connection = connection;
227 command.CommandType = CommandType.StoredProcedure;
228 AddParameter (command, "ApplicationName", ApplicationName);
230 DbDataReader reader = command.ExecuteReader ();
231 ArrayList roleList = new ArrayList ();
232 while (reader.Read ())
233 roleList.Add (reader.GetString (0));
236 return (string []) roleList.ToArray (typeof (string));
240 public override string [] GetRolesForUser (string username)
242 using (DbConnection connection = CreateConnection ()) {
243 DbCommand command = factory.CreateCommand ();
244 command.CommandText = @"dbo.aspnet_UsersInRoles_GetRolesForUser";
245 command.Connection = connection;
247 command.CommandType = CommandType.StoredProcedure;
248 AddParameter (command, "UserName", username);
249 AddParameter (command, "ApplicationName", ApplicationName);
251 DbDataReader reader = command.ExecuteReader ();
252 ArrayList roleList = new ArrayList ();
253 while (reader.Read ())
254 roleList.Add (reader.GetString (0));
257 return (string []) roleList.ToArray (typeof (string));
261 public override string [] GetUsersInRole (string rolename)
263 using (DbConnection connection = CreateConnection ()) {
264 DbCommand command = factory.CreateCommand ();
265 command.CommandText = @"dbo.aspnet_UsersInRoles_GetUsersInRoles";
266 command.Connection = connection;
268 command.CommandType = CommandType.StoredProcedure;
269 AddParameter (command, "RoleName", rolename);
270 AddParameter (command, "ApplicationName", ApplicationName);
272 DbDataReader reader = command.ExecuteReader ();
273 ArrayList userList = new ArrayList ();
274 while (reader.Read ())
275 userList.Add (reader.GetString (0));
278 return (string []) userList.ToArray (typeof (string));
282 string GetStringConfigValue (NameValueCollection config, string name, string def)
285 string val = config [name];
292 public override void Initialize (string name, NameValueCollection config)
295 throw new ArgumentNullException ("config");
297 base.Initialize (name, config);
299 applicationName = config ["applicationName"];
300 string connectionStringName = config ["connectionStringName"];
302 if (applicationName.Length > 256)
303 throw new ProviderException ("The ApplicationName attribute must be 256 characters long or less.");
304 if (connectionStringName == null || connectionStringName.Length == 0)
305 throw new ProviderException ("The ConnectionStringName attribute must be present and non-zero length.");
307 // XXX check connectionStringName and commandTimeout
309 connectionString = WebConfigurationManager.ConnectionStrings [connectionStringName];
310 if (connectionString == null)
311 throw new ProviderException (String.Format("The connection name '{0}' was not found in the applications configuration or the connection string is empty.", connectionStringName));
312 factory = String.IsNullOrEmpty (connectionString.ProviderName) ?
313 System.Data.SqlClient.SqlClientFactory.Instance :
314 ProvidersHelper.GetDbProviderFactory (connectionString.ProviderName);
317 public override bool IsUserInRole (string username, string rolename)
319 using (DbConnection connection = CreateConnection ()) {
320 DbCommand command = factory.CreateCommand ();
321 command.CommandText = @"dbo.aspnet_UsersInRoles_IsUserInRole";
322 command.Connection = connection;
324 command.CommandType = CommandType.StoredProcedure;
325 AddParameter (command, "RoleName", rolename);
326 AddParameter (command, "UserName", username);
327 AddParameter (command, "ApplicationName", ApplicationName);
328 DbParameter dbpr = AddParameter (command, null, ParameterDirection.ReturnValue, null);
330 command.ExecuteNonQuery ();
331 int returnValue = (int) dbpr.Value;
333 if (returnValue == 1)
340 public override void RemoveUsersFromRoles (string [] usernames, string [] rolenames)
342 Hashtable h = new Hashtable ();
344 foreach (string u in usernames) {
346 throw new ArgumentNullException ("null element in usernames array");
347 if (h.ContainsKey (u))
348 throw new ArgumentException ("duplicate element in usernames array");
349 if (u.Length == 0 || u.Length > 256 || u.IndexOf (",") != -1)
350 throw new ArgumentException ("element in usernames array in illegal format");
354 h = new Hashtable ();
355 foreach (string r in rolenames) {
357 throw new ArgumentNullException ("null element in rolenames array");
358 if (h.ContainsKey (r))
359 throw new ArgumentException ("duplicate element in rolenames array");
360 if (r.Length == 0 || r.Length > 256 || r.IndexOf (",") != -1)
361 throw new ArgumentException ("element in rolenames array in illegal format");
365 using (DbConnection connection = CreateConnection ()) {
366 DbCommand command = factory.CreateCommand ();
367 command.CommandText = @"dbo.aspnet_UsersInRoles_RemoveUsersFromRoles";
368 command.Connection = connection;
369 command.CommandType = CommandType.StoredProcedure;
371 AddParameter (command, "UserNames", String.Join (",", usernames));
372 AddParameter (command, "RoleNames", String.Join (",", rolenames));
373 AddParameter (command, "ApplicationName", ApplicationName);
374 DbParameter dbpr = AddParameter (command, null, ParameterDirection.ReturnValue, null);
376 command.ExecuteNonQuery ();
377 int returnValue = (int) dbpr.Value;
379 if (returnValue == 0)
381 else if (returnValue == 1)
382 throw new ProviderException ("One or more of the specified user names was not found.");
383 else if (returnValue == 2)
384 throw new ProviderException ("One or more of the specified role names was not found.");
385 else if (returnValue == 3)
386 throw new ProviderException ("One or more of the specified user names is not associated with one or more of the specified role names.");
388 throw new ProviderException ("Failed to remove users from roles");
392 public override bool RoleExists (string rolename)
394 using (DbConnection connection = CreateConnection ()) {
396 DbCommand command = factory.CreateCommand ();
397 command.CommandText = @"dbo.aspnet_Roles_RoleExists";
398 command.Connection = connection;
399 command.CommandType = CommandType.StoredProcedure;
401 AddParameter (command, "ApplicationName", ApplicationName);
402 AddParameter (command, "RoleName", rolename);
403 DbParameter dbpr = AddParameter (command, null, ParameterDirection.ReturnValue, null);
405 command.ExecuteNonQuery ();
406 int returnValue = (int) dbpr.Value;
408 if (returnValue == 1)
416 public override string ApplicationName
418 get { return applicationName; }
421 applicationName = value;