context: // Copyright (c) 2004-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc. 2014, Oracle and/or its affiliates. All rights reserved.
  //
  // MySQL Connector/NET is licensed under the terms of the GPLv2
  // <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most 
  // MySQL Connectors. There are special exceptions to the terms and 
  // conditions of the GPLv2 as it is applied to this software, see the 
  // FLOSS License Exception
  // <http://www.mysql.com/about/legal/licensing/foss-exception.html>.
  //
  // This program is free software; you can redistribute it and/or modify 
  // it under the terms of the GNU General Public License as published 
  // by the Free Software Foundation; version 2 of the License.
  //
  // This program is distributed in the hope that it will be useful, but 
  // WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 
  // or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License 
  // for more details.
  //
  // You should have received a copy of the GNU General Public License along 
  // with this program; if not, write to the Free Software Foundation, Inc., 
  // 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
  
  //  This code was contributed by Sean Wright (srwright@alcor.concordia.ca) on 2007-01-12
  //  The copyright was assigned and transferred under the terms of
  //  the MySQL Contributor License Agreement (CLA)
  
  using System;
  using System.Collections.Specialized;
  using System.Configuration;
  using System.Configuration.Provider;
  using System.Diagnostics;
  using System.Web.Hosting;
  using System.Web.Security;
  using MySql.Data.MySqlClient;
  using System.Collections.Generic;
  using MySql.Web.Common;
  using MySql.Web.Properties;
  using System.Web;
  using MySql.Web.General;
  
  namespace MySql.Web.Security
  {
    /// <summary>
    /// Manages storage of role membership information for an ASP.NET application in a MySQL database. 
    /// </summary>
    public sealed class MySQLRoleProvider : RoleProvider
    {
      private string eventSource = "MySQLRoleProvider";
      private string eventLog = "Application";
      private string exceptionMessage = "An exception occurred. Please check the Event Log.";
      private ConnectionStringSettings pConnectionStringSettings;
      private string connectionString;
      private bool pWriteExceptionsToEventLog = false;
      private Application app;
  
      /// <summary>
      /// Initializes the provider.
      /// </summary>
      /// <param name="name">The friendly name of the provider.</param>
      /// <param name="config">A collection of the name/value pairs representing the provider-specific attributes specified in the configuration for this provider.</param>
      /// <exception cref="T:System.ArgumentNullException">The name of the provider is null.</exception>
      /// <exception cref="T:System.ArgumentException">The name of the provider has a length of zero.</exception>
      /// <exception cref="T:System.InvalidOperationException">An attempt is made to call <see cref="M:System.Configuration.Provider.ProviderBase.Initialize(System.String,System.Collections.Specialized.NameValueCollection)"/> on a provider after the provider has already been initialized.</exception>
      public override void Initialize(string name, NameValueCollection config)
      {
        if (config == null)
        {
          throw new ArgumentNullException("config");
        }
        if (name == null || name.Length == 0)
        {
          name = "MySQLRoleProvider";
        }
        if (string.IsNullOrEmpty(config["description"]))
        {
          config.Remove("description");
          config.Add("description", "MySQL Role provider");
        }
        base.Initialize(name, config);
  
        string applicationName = HostingEnvironment.ApplicationVirtualPath;
        if (!String.IsNullOrEmpty(config["applicationName"]))
          applicationName = config["applicationName"];
  
        if (!(config["writeExceptionsToEventLog"] == null))
        {
          if (config["writeExceptionsToEventLog"].ToUpper() == "TRUE")
          {
            pWriteExceptionsToEventLog = true;
          }
        }
        pConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
        if (pConnectionStringSettings != null)
          connectionString = pConnectionStringSettings.ConnectionString.Trim();
        else
          connectionString = "";
  
        if (String.IsNullOrEmpty(connectionString)) return;
  
        // make sure our schema is up to date
        SchemaManager.CheckSchema(connectionString, config);
  
        app = new Application(applicationName, Description);
      }
  
      #region Properties
  
      /// <summary>
      /// Gets or sets the name of the application to store and retrieve role information for.
      /// </summary>
      /// <value>The name of the application to store and retrieve role information for.</value>
      /// <example>
      /// <code source="CodeExamples\RoleCodeExample1.xml"/>
      /// </example>
      public override string ApplicationName
      {
        get { return app.Name; }
        set { app.Name = value; }
      }
  
      /// <summary>
      /// Gets or sets a value indicating whether [write exceptions to event log].
      /// </summary>
      /// <value>
      /// 	<c>true</c> if exceptions should be written to the event log; otherwise, <c>false</c>.
      /// </value>
      /// <example>
      /// <code source="CodeExamples\RoleCodeExample1.xml"/>
      /// </example>
      public bool WriteExceptionsToEventLog
      {
        get { return pWriteExceptionsToEventLog; }
        set { pWriteExceptionsToEventLog = value; }
      }
  
      #endregion
  
      #region Public Methods
  
      /// <summary>
      /// Adds the users to roles.
      /// </summary>
      /// <param name="usernames">The usernames.</param>
      /// <param name="rolenames">The rolenames.</param>
      public override void AddUsersToRoles(string[] usernames, string[] rolenames)
      {
        if (rolenames == null || rolenames.Length == 0) return;
        if (usernames == null || usernames.Length == 0) return;
  
        foreach (string rolename in rolenames)
        {
          if (String.IsNullOrEmpty(rolename))
            throw new ArgumentException(Resources.IllegalRoleName, "rolenames");
          if (!RoleExists(rolename))
            throw new ProviderException(Resources.RoleNameNotFound);
        }
  
        foreach (string username in usernames)
        {
          if (String.IsNullOrEmpty(username))
            throw new ArgumentException(Resources.IllegalUserName, "usernames");
          if (username.IndexOf(',') != -1)
            throw new ArgumentException(Resources.InvalidCharactersInUserName);
  
          foreach (string rolename in rolenames)
          {
            if (IsUserInRole(username, rolename))
              throw new ProviderException(Resources.UserIsAlreadyInRole);
          }
        }
  
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
          MySqlTransaction txn = null;
          try
          {
            connection.Open();
            txn = connection.BeginTransaction();
            MySqlCommand cmd = new MySqlCommand(
                "INSERT INTO my_aspnet_usersinroles VALUES(@userId, @roleId)", connection);
            cmd.Parameters.Add("@userId", MySqlDbType.Int32);
            cmd.Parameters.Add("@roleId", MySqlDbType.Int32);
            foreach (string username in usernames)
            {
              // either create a new user or fetch the existing user id
              long userId = SchemaManager.CreateOrFetchUserId(connection,
                  username, app.FetchId(connection), true);
              foreach (string rolename in rolenames)
              {
                int roleId = GetRoleId(connection, rolename);
                cmd.Parameters[0].Value = userId;
                cmd.Parameters[1].Value = roleId;
                cmd.ExecuteNonQuery();
              }
            }
            txn.Commit();
          }
          catch (Exception ex)
          {
            if (txn != null)
              txn.Rollback();
            if (WriteExceptionsToEventLog)
              WriteToEventLog(ex, "AddUsersToRoles");
            throw;
          }
        }
      }
  
      /// <summary>
      /// Creates the role.
      /// </summary>
      /// <param name="rolename">The rolename.</param>
      public override void CreateRole(string rolename)
      {
        if (rolename.IndexOf(',') != -1)
          throw new ArgumentException(Resources.InvalidCharactersInUserName);
        if (RoleExists(rolename))
          throw new ProviderException(Resources.RoleNameAlreadyExists);
  
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
          try
          {
            connection.Open();
  
            MySqlCommand cmd = new MySqlCommand(
                    @"INSERT INTO my_aspnet_roles Values(NULL, @appId, @name)", connection);
            cmd.Parameters.AddWithValue("@appId", app.EnsureId(connection));
            cmd.Parameters.AddWithValue("@name", rolename);
            cmd.ExecuteNonQuery();
          }
          catch (MySqlException e)
          {
            if (WriteExceptionsToEventLog)
              WriteToEventLog(e, "CreateRole");
            throw;
          }
        }
      }
  
      /// <summary>
      /// Deletes the role.
      /// </summary>
      /// <param name="rolename">The rolename.</param>
      /// <param name="throwOnPopulatedRole">if set to <c>true</c> [throw on populated role].</param>
      /// <returns>true if the role was successfully deleted; otherwise, false. </returns>
      public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
      {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
          MySqlTransaction txn = null;
          try
          {
            if (!(RoleExists(rolename)))
              throw new ProviderException(Resources.RoleNameNotFound);
  
gt:           if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)