//******************************************************************************************************//  AdoSecurityProvider.cs - Gbtc////  Copyright © 2012, Grid Protection Alliance.  All Rights Reserved.////  Licensed to the Grid Protection Alliance (GPA) under one or more contributor license agreements. See//  the NOTICE file distributed with this work for additional information regarding copyright ownership.//  The GPA licenses this file to you under the MIT License (MIT), the "License"; you may//  not use this file except in compliance with the License. You may obtain a copy of the License at:////      http://www.opensource.org/licenses/MIT////  Unless agreed to in writing, the subject software distributed under the License is distributed on an//  "AS-IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Refer to the//  License for the specific language governing permissions and limitations.////  Code Modification History://  ----------------------------------------------------------------------------------------------------//  11/24/2010 - Mehulbhai P Thakkar//       Generated original version of source code.//  07/18/2011 - Stephen C. Wills//       Modified a SELECT statement in RefreshData to explicitly alias columns to//       fix an error when using SQLite databases.//  09/01/2011 - Stephen C. Wills//       Modified references to views in the database whose names have changed.//  09/28/2011 - Stephen C. Wills//       Modified database queries to work with Oracle database.//  12/20/2012 - Starlynn Danyelle Gilliam//       Modified Header.//  01/16/2014 - J. Ritchie Carroll//       Added security context caching so security system can load without database access//       using last cached security state.////****************************************************************************************************** using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Diagnostics;using System.Linq;using System.Security;using System.Text.RegularExpressions;using System.Threading;using GSF.Collections;using GSF.Configuration;using GSF.Data;using GSF.Diagnostics;using GSF.Identity; #pragma warning disable S2068 namespace GSF.Security{    /// <summary>    /// Represents an <see cref="ISecurityProvider"/> that uses ADO.NET data source (SQL Server, MySQL, Oracle, etc.) for its    /// backend data store and authenticates internal users against Active Directory and external users against the database.    /// </summary>    /// <example>    /// Required config file entries (automatically added):    /// <code>    /// <![CDATA[    /// <?xml version="1.0"?>    /// <configuration>    ///   <configSections>    ///     <section name="categorizedSettings" type="GSF.Configuration.CategorizedSettingsSection, GSF.Core" />    ///   </configSections>    ///   <categorizedSettings>    ///     <securityProvider>    ///       <add name="ProviderType" value="GSF.Security.AdoSecurityProvider, GSF.Security" description="The type to be used for enforcing security."    ///         encrypted="false" />    ///       <add name="UserCacheTimeout" value="5" description="Defines the timeout, in whole minutes, for a user's provider cache. Any value less than 1 will cause cache reset every minute."    ///         encrypted="false" />    ///       <add name="ConnectionString" value="Eval(systemSettings.ConnectionString)" description="Configuration database connection string"    ///         encrypted="false"/>    ///       <add name="DataProviderString" value="Eval(systemSettings.DataProviderString)" description="Configuration database ADO.NET data provider assembly type creation string"    ///         encrypted="false"/>        ///       <add name="LdapPath" value="" description="Specifies the LDAP path used to initialize the security provider."    ///         encrypted="false" />    ///       <add name="ApplicationName" value="SEC_APP" description="Name of the application being secured."    ///         encrypted="false" />    ///       <add name="IncludedResources" value="*=*" description="Semicolon delimited list of resources to be secured along with role names."    ///         encrypted="false" />    ///       <add name="ExcludedResources" value="" description="Semicolon delimited list of resources to be excluded from being secured."    ///         encrypted="false" />        ///       <add name="NotificationSmtpServer" value="localhost" description="SMTP server to be used for sending out email notification messages."    ///         encrypted="false" />    ///       <add name="NotificationSenderEmail" value="sender@company.com" description="Email address of the sender of email notification messages."     ///         encrypted="false" />    ///       <add name="CacheRetryDelayInterval" value="200" description="Wait interval, in milliseconds, before retrying load of user data cache."    ///         encrypted="false"/>    ///       <add name="CacheMaximumRetryAttempts" value="10" description="Maximum retry attempts allowed for loading user data cache."    ///         encrypted="false"/>    ///       <add name="EnableOfflineCaching" value="True" description="True to enable caching of user information for authentication in offline state, otherwise False."    ///         encrypted="false"/>    ///       <add name="PasswordRequirementsRegex" value="^.*(?=.{8,})(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).*$" description="Regular expression used to validate new passwords for database users."    ///         encrypted="false" />    ///       <add name="PasswordRequirementsError" value="Invalid Password: Password must be at least 8 characters; must contain at least 1 number, 1 upper case letter, and 1 lower case letter" description="Error message to be displayed when new database user password fails regular expression test."    ///         encrypted="false" />    ///       <add name="DefaultRoles" value="Viewer" description="If set this is a list of Roles assigned to a user that has no defined Roles."    ///         encrypted="false" />    ///     </securityProvider>    ///     <activeDirectory>    ///       <add name="PrivilegedDomain" value="" description="Domain of privileged domain user account."    ///         encrypted="false" />    ///       <add name="PrivilegedUserName" value="" description="Username of privileged domain user account."    ///         encrypted="false" />    ///       <add name="PrivilegedPassword" value="" description="Password of privileged domain user account."    ///         encrypted="true" />    ///     </activeDirectory>    ///   </categorizedSettings>    /// </configuration>    /// ]]>    /// </code>    /// </example>    /// <remarks>    /// Minimum expected table schema for ADO Security Provider:    /// <code>    /// <![CDATA[    /// CREATE TABLE UserAccount    /// (    ///     ID UNIQUEINDENTIFIER NOT NULL DEFAULT NEWID(),    ///     Name VARCHAR(200) NOT NULL,    ///     Password VARCHAR(200) DEFAULT NULL,    ///     FirstName VARCHAR(200) DEFAULT NULL,    ///     LastName VARCHAR(200) DEFAULT NULL,    ///     Phone VARCHAR(200) DEFAULT NULL,    ///     Email VARCHAR(200) DEFAULT NULL,    ///     LockedOut TINYINT NOT NULL DEFAULT 0,    ///     UseADAuthentication TINYINT NOT NULL DEFAULT 1,    ///     ChangePasswordOn DATETIME DEFAULT NULL,    ///     CONSTRAINT PK_UserAccount PRIMARY KEY (ID ASC),    ///     CONSTRAINT IX_UserAccount UNIQUE KEY (Name)    /// );    ///     /// CREATE TABLE SecurityGroup    /// (    ///     ID UNIQUEINDENTIFIER NOT NULL DEFAULT NEWID(),    ///     Name VARCHAR(200) NOT NULL,    ///     CONSTRAINT PK_SecurityGroup PRIMARY KEY (ID ASC),    ///     CONSTRAINT IX_SecurityGroup UNIQUE KEY (Name)    /// );    ///     /// CREATE TABLE SecurityGroupUserAccount    /// (    ///     SecurityGroupID UNIQUEINDENTIFIER NOT NULL,    ///     UserAccountID UNIQUEINDENTIFIER NOT NULL    /// );    ///     /// CREATE TABLE ApplicationRole    /// (    ///     ID UNIQUEINDENTIFIER NOT NULL DEFAULT NEWID(),    ///     Name VARCHAR(200) NOT NULL,    ///     NodeID UNIQUEINDENTIFIER NOT NULL,    ///     CONSTRAINT PK_ApplicationRole PRIMARY KEY (ID ASC),    ///     CONSTRAINT IX_ApplicationRole UNIQUE KEY (NodeID, Name)    /// );    ///     /// CREATE TABLE ApplicationRoleUserAccount    /// (    ///     ApplicationRoleID UNIQUEINDENTIFIER NOT NULL,    ///     UserAccountID UNIQUEINDENTIFIER NOT NULL      /// );    ///     /// CREATE TABLE ApplicationRoleSecurityGroup    /// (    ///     ApplicationRoleID UNIQUEINDENTIFIER NOT NULL,    ///     SecurityGroupID UNIQUEINDENTIFIER NOT NULL      /// );    /// ]]>    /// </code>    /// </remarks>    public class AdoSecurityProvider : LdapSecurityProvider    {        #region [ Members ]         // Constants        private const string UserAccountTable = "UserAccount";                                      // Table name for user accounts        private const string SecurityGroupTable = "SecurityGroup";                                  // Table name for security groups        private const string SecurityGroupUserAccountTable = "SecurityGroupUserAccount";            // Table name for security group user accounts        private const string ApplicationRoleTable = "ApplicationRole";                              // Table name for application roles        private const string ApplicationRoleUserAccountTable = "ApplicationRoleUserAccount";        // Table name for application role assignments for user accounts        private const string ApplicationRoleSecurityGroupTable = "ApplicationRoleSecurityGroup";    // Table name for application role assignments for security groups         /// <summary>        /// Default Roles to be used if no Roles are supplied for a user.        /// </summary>        private const string DefaultDefaultRoles = "";         /// <summary>        /// Default regular expression used to validate new database user passwords.        /// </summary>        public const string DefaultPasswordRequirementsRegex = "^.*(?=.{8,})(?=.*\\d)(?=.*[a-z])(?=.*[A-Z]).*$";         /// <summary>        /// Default error message displayed when databases users fail regular expression test.        /// </summary>        public const string DefaultPasswordRequirementsError = "Invalid Password: Password must be at least 8 characters; must contain at least 1 number, 1 upper case letter, and 1 lower case letter";         /// <summary>        /// Default value for <see cref="UseDatabaseLogging"/>.        /// </summary>        public const bool DefaultUseDatabaseLogging = true;         /// <summary>        /// Defines the provider ID for the <see cref="AdoSecurityProvider"/>.        /// </summary>        public new const int ProviderID = 1;         private bool m_successfulPassThroughAuthentication;        private string m_passwordRequirementsRegex;        private string m_passwordRequirementsError;        private bool? m_lastLoggedLoginResult;         #endregion         #region [ Constructor ]         /// <summary>        /// Initializes a new instance of the <see cref="AdoSecurityProvider"/> class.        /// </summary>        /// <param name="username">Name that uniquely identifies the user.</param>        public AdoSecurityProvider(string username)            : this(username, true, false, true)        {        }         /// <summary>        /// Initializes a new instance of the <see cref="AdoSecurityProvider"/> class.        /// </summary>        /// <param name="username">Name that uniquely identifies the user.</param>        /// <param name="canRefreshData">true if the security provider can refresh <see cref="UserData"/> from the backend data store, otherwise false.</param>        /// <param name="canResetPassword">true if the security provider can reset user password, otherwise false.</param>        /// <param name="canChangePassword">true if the security provider can change user password, otherwise false.</param>        protected AdoSecurityProvider(string username, bool canRefreshData, bool canResetPassword, bool canChangePassword)            : base(username, canRefreshData, canResetPassword, canChangePassword)        {            base.ConnectionString = "Eval(systemSettings.ConnectionString)";             m_passwordRequirementsRegex = DefaultPasswordRequirementsRegex;            m_passwordRequirementsError = DefaultPasswordRequirementsError;            UseDatabaseLogging = DefaultUseDatabaseLogging;        }         #endregion         #region [ Properties ]         /// <summary>        /// Gets last exception reported by the <see cref="AdoSecurityProvider"/>.        /// </summary>        public Exception LastException        {            get;            set;        }         /// <summary>        /// Gets or sets flag that determines if <see cref="LogAuthenticationAttempt"/> and <see cref="LogError"/> should        /// write to the database. Defaults to <c>true</c>.        /// </summary>        /// <remarks>        /// Setting this flag to <c>false</c> may be necessary in cases where a database has been setup to use authentication        /// but does not include an "AccessLog" or "ErrorLog" table.        /// </remarks>        public bool UseDatabaseLogging        {            get;            set;        }          /// <summary>        /// Gets or sets the Default Roles used when a user does not have a role defined.        /// The user still needs to exist but they won't require a Role and will be assigned the DefaultRoles.        /// It is a comma separate list for multiple Roles. If an empty String is supplied a Role is required for the user.        /// </summary>        public string DefaultRoles        {            get;            set;        }         #endregion         #region [ Methods ]         /// <summary>        /// Loads saved security provider settings from the config file if the <see cref="SecurityProviderBase.PersistSettings"/> property is set to true.        /// </summary>        /// <exception cref="ConfigurationErrorsException"><see cref="SecurityProviderBase.SettingsCategory"/> has a value of null or empty string.</exception>        public override void LoadSettings()        {            base.LoadSettings();             // Make sure default settings exist            ConfigurationFile config = ConfigurationFile.Current;            CategorizedSettingsElementCollection settings = config.Settings[SettingsCategory];             settings.Add("DataProviderString", "Eval(systemSettings.DataProviderString)", "Configuration database ADO.NET data provider assembly type creation string to be used for connection to the backend security data store.");            settings.Add("LdapPath", "", "Specifies the LDAP path used to initialize the security provider.");            settings.Add("PasswordRequirementsRegex", DefaultPasswordRequirementsRegex, "Regular expression used to validate new passwords for database users.");            settings.Add("PasswordRequirementsError", DefaultPasswordRequirementsError, "Error message to be displayed when new database user password fails regular expression test.");            settings.Add("UseDatabaseLogging", DefaultUseDatabaseLogging, "Flag that determines if provider should write logs to the database.");            settings.Add("DefaultRoles", DefaultDefaultRoles, "If set this is a list of Roles assigned to a user that has no defined Roles.");             DefaultRoles = settings["DefaultRoles"].ValueAs(DefaultRoles);            m_passwordRequirementsRegex = settings["PasswordRequirementsRegex"].ValueAs(m_passwordRequirementsRegex);            m_passwordRequirementsError = settings["PasswordRequirementsError"].ValueAs(m_passwordRequirementsError);        }         /// <summary>        /// Refreshes the <see cref="UserData"/>.        /// </summary>        /// <returns>true if <see cref="SecurityProviderBase.UserData"/> is refreshed, otherwise false.</returns>        public override bool RefreshData()        {            if (string.IsNullOrEmpty(UserData.Username))                return false;             try            {                UserData userData = new UserData(UserData.Username);                 // Initialize user data.                userData.Initialize();                 // We'll retrieve all the data we need about a user.                //   Table1: Information about the user.                //   Table2: Groups the user is a member of.                //   Table3: Roles that are assigned to the user either implicitly (NT groups) or explicitly (database) or through a group.                 DataSet securityContext;                 try                {                    // Attempt to extract current security context from the database                    using (AdoDataConnection database = new AdoDataConnection(SettingsCategory))                    {                        securityContext = ExtractSecurityContext(database.Connection, ex => LogError(ex.Source, ex.ToString()), UserData.Username);                    }                }                catch (InvalidOperationException)                {                    // Failed to open ADO connection, attempt to fall back on using security context from last known cached information                    using (AdoSecurityCache cache = AdoSecurityCache.GetCurrentCache())                        securityContext = cache.DataSet;                }                 try                {                    // Make sure primary keys are defined on tables that we use "Find" function on later...                    UpdatePrimaryKey(securityContext.Tables[SecurityGroupTable], "SecurityGroupID");                    UpdatePrimaryKey(securityContext.Tables[ApplicationRoleTable], "ApplicationRoleID");                }                catch (Exception ex)                {                    // Just going to fall back on slower "Select" function if this fails...                    Logger.SwallowException(ex, "Failed to update primary key from cached security context, falling back on slower select function.", additionalFlags: MessageFlags.PerformanceIssue);                }                 // Validate that needed security tables exist in the data set                if ((object)securityContext == null)                    throw new SecurityException("Failed to load a valid security context. Cannot proceed with user authentication.");                 foreach (string securityTable in s_securityTables)                {                    if (!securityContext.Tables.Contains(securityTable))                        throw new SecurityException($"Failed to load a valid security context - missing table '{securityTable}'. Cannot proceed with user authentication.");                }                 if (securityContext.Tables[ApplicationRoleTable].Rows.Count == 0 && string.IsNullOrEmpty(DefaultRoles))                    throw new SecurityException($"Failed to load a valid security context - no application roles were found for node ID '{DefaultNodeID}', verify the node ID in the config file '{ConfigurationFile.Current.Configuration.FilePath}'. Cannot proceed with user authentication.");                 DataRow userAccount = null;                Guid userAccountID = Guid.Empty;                string userSID = UserInfo.UserNameToSID(userData.Username);                 // Filter user account data for the current user.                DataRow[] userAccounts = securityContext.Tables[UserAccountTable].Select($"Name = '{EncodeEscapeSequences(userSID)}'");                 // If SID based lookup failed, try lookup by user name.  Note that is critical that SID based lookup                // take precedence over name based lookup for proper cross-platform authentication.                if (userAccounts.Length == 0)                    userAccounts = securityContext.Tables[UserAccountTable].Select($"Name = '{EncodeEscapeSequences(userData.Username)}'");                 if (userAccounts.Length == 0)                {                    // User doesn't exist in the database, however, user may exist in an NT authentication group which                    // may have an explicit role assignment. To test for this case we make the assumption that this is                    // a Windows authenticated user and test for rights within groups                    userData.IsDefined = true;                    userData.IsExternal = false;                }                else                {                    userAccount = userAccounts[0];                    userData.IsDefined = true;                    userData.IsExternal = !Convert.ToBoolean(userAccount["UseADAuthentication"]);                    userAccountID = Guid.Parse(Convert.ToString(userAccount["ID"]));                }                 if (userData.IsExternal && (object)userAccount != null)                {                    // Load database user details                    if (string.IsNullOrEmpty(userData.LoginID))                        userData.LoginID = userData.Username;                     if (!Convert.IsDBNull(userAccount["Password"]))                        userData.Password = Convert.ToString(userAccount["Password"]);                     if (!Convert.IsDBNull(userAccount["FirstName"]))                        userData.FirstName = Convert.ToString(userAccount["FirstName"]);                     if (!Convert.IsDBNull(userAccount["LastName"]))                        userData.LastName = Convert.ToString(userAccount["LastName"]);                     if (!Convert.IsDBNull(userAccount["Phone"]))                        userData.PhoneNumber = Convert.ToString(userAccount["Phone"]);                     if (!Convert.IsDBNull(userAccount["Email"]))                        userData.EmailAddress = Convert.ToString(userAccount["Email"]);                     if (!Convert.IsDBNull(userAccount["ChangePasswordOn"]))                        userData.PasswordChangeDateTime = Convert.ToDateTime(userAccount["ChangePasswordOn"]);                     if (!Convert.IsDBNull(userAccount["CreatedOn"]))                        userData.AccountCreatedDateTime = Convert.ToDateTime(userAccount["CreatedOn"]);                     // For possible future use:                    //if (!Convert.IsDBNull(userDataRow["UserCompanyName"]))                    //    userData.CompanyName = Convert.ToString(userDataRow["UserCompanyName"]);                    //if (!Convert.IsDBNull(userDataRow["UserSecurityQuestion"]))                    //    userData.SecurityQuestion = Convert.ToString(userDataRow["UserSecurityQuestion"]);                    //if (!Convert.IsDBNull(userDataRow["UserSecurityAnswer"]))                    //    userData.SecurityAnswer = Convert.ToString(userDataRow["UserSecurityAnswer"]);                }                else                {                    // Load implicitly assigned groups - this happens via user's NT/AD groups that get loaded into the                    // user data group collection. When database group definitions are defined with the same name as                    // their NT/AD equivalents, this will allow automatic external group management.                    base.RefreshData(userData, userData.Groups, ProviderID);                }                 // Administrator can lock out NT/AD user as well as database-only user via database                if (!userData.IsLockedOut && (object)userAccount != null && !Convert.IsDBNull(userAccount["LockedOut"]))                    userData.IsLockedOut = Convert.ToBoolean(userAccount["LockedOut"]);                 // At this point an NT/AD based user will have a list of groups that is known to be available to the user. A database                // user will have no groups defined yet. The next step will be to load any explicitly assigned groups the user is a                // member of. Users can be explicitly assigned to any group, database or NT/AD group. This allows flexibility for                // local database users to be assigned to an NT/AD group (in the database) as well as NT/AD users to be a member of                // local database groups - allowing customizable role assignments to be fully managed via groups.                 // Note: some groups, such as "Everyone" (S-1-1-0), will not be automatically returned as valid groups by the base                // LdapSecurityProvider (see UserInfo class for details). If the security provider needs to support such groups,                // these groups will need to be manually added to the groups list here before testing to see if the user is a                // member of these groups. Since these types of groups could be considered a security risk by some applications,                // it is recommended that if this type of functionality is enabled, that it can be disabled in the configuration.                 if (userAccountID != Guid.Empty)                {                    // Filter explicitly assigned security groups for current user                    DataRow[] userGroups = securityContext.Tables[SecurityGroupUserAccountTable].Select($"UserAccountID = '{EncodeEscapeSequences(userAccountID.ToString())}'");                     foreach (DataRow row in userGroups)                    {                        // Locate associated security group record                        DataRow securityGroup = null;                         if (securityContext.Tables[SecurityGroupTable].PrimaryKey.Length > 0)                        {                            securityGroup = securityContext.Tables[SecurityGroupTable].Rows.Find(row["SecurityGroupID"]);                        }                        else                        {                            DataRow[] securityGroups = securityContext.Tables[SecurityGroupTable].Select($"ID = '{EncodeEscapeSequences(row["SecurityGroupID"].ToString())}'");                             if (securityGroups.Length > 0)                                securityGroup = securityGroups[0];                        }                         if ((object)securityGroup == null || Convert.IsDBNull(securityGroup["Name"]))                            continue;                         // Just in case a database user was manually assigned to an NT/AD group, make sure to convert                        // the group name back to it's human readable name (from a SID). The UserInfo.SIDToAccountName                        // function will return the original parameter value if name cannot be converted - this allows                        // the function to work for database group names as well.                        string groupName = UserInfo.SIDToAccountName(Convert.ToString(securityGroup["Name"]));                         if (!userData.Groups.Contains(groupName, StringComparer.OrdinalIgnoreCase))                            userData.Groups.Add(groupName);                    }                }                 // Explicitly assigned user roles will take precedence over any roles that may be implicitly available to the                // user based on what group they may be a member of. In practice this means even though a user may be a member                // of a group with role assignments, if there are any roles directly assigned to the user these will be their

Lines 1–500 of 1279. Load more