/// <summary>
/// return is credential provide by user is right or not
/// </summary>
/// <param name="model">login model class</param>
/// <param name="roles">out roles , has role list of user</param>
/// <param name="ctx" ></param>
/// <returns>true if athenicated</returns>
public static bool Login(LoginModel model, out List <KeyNamePair> roles)
{
roles = null;
// loginModel = null;
//bool isMatch = false;
SecureEngine.Encrypt("t"); //Initialize
// Cannot use encrypted password
if (model.Login1Model.Password != null && SecureEngine.IsEncrypted(model.Login1Model.Password))
{
//log.warning("Cannot use Encrypted Password");
return(false);
}
// Authentification
bool authenticated = false;
bool isLDAP = false;
MSystem system = MSystem.Get(new Ctx());
if (system != null && system.IsLDAP())
{
authenticated = system.IsLDAP(model.Login1Model.UserName, model.Login1Model.Password);
if (authenticated)
{
model.Login1Model.Password = null;
}
isLDAP = true;
// if not authenticated, use AD_User as backup
}
StringBuilder sql = new StringBuilder("SELECT u.AD_User_ID, r.AD_Role_ID,r.Name,")
.Append(" u.ConnectionProfile, u.Password ") // 4,5
.Append("FROM AD_User u")
.Append(" INNER JOIN AD_User_Roles ur ON (u.AD_User_ID=ur.AD_User_ID AND ur.IsActive='Y')")
.Append(" INNER JOIN AD_Role r ON (ur.AD_Role_ID=r.AD_Role_ID AND r.IsActive='Y') ");
//.Append("WHERE COALESCE(u.LDAPUser,u.Name)=@username") // #1
if (isLDAP && authenticated)
{
sql.Append(" WHERE (COALESCE(u.LDAPUser,u.Value)=@username)");
}
else if (isLDAP && !authenticated && model.Login1Model.Password == null)// If user not authenicated using LDAP, then if LDAP user is available
{
sql.Append(" WHERE (u.LDAPUser=@username OR u.Name=@username OR u.Value=@username)");
}
else
{
sql.Append(" WHERE (u.Name=@username OR u.Value=@username)");
}
sql.Append(" AND u.IsActive='Y' ")
.Append(" AND u.IsLoginUser='******' ")
.Append(" AND EXISTS (SELECT * FROM AD_Client c WHERE u.AD_Client_ID=c.AD_Client_ID AND c.IsActive='Y')")
.Append(" AND EXISTS (SELECT * FROM AD_Client c WHERE r.AD_Client_ID=c.AD_Client_ID AND c.IsActive='Y')");
string sqlEnc = "select isencrypted from ad_column where ad_table_id=(select ad_table_id from ad_table where tablename='AD_User') and columnname='Password'";
char isEncrypted = Convert.ToChar(DB.ExecuteScalar(sqlEnc));
if (model.Login1Model.Password != null)
{
if (isEncrypted == 'Y')
{
sql.Append(" AND (u.Password='******')"); // #2/3
}
else
{
sql.Append(" AND (u.Password='******')"); // #2/3
}
}
sql.Append(" ORDER BY r.Name");
IDataReader dr = null;
//try
//{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@username", model.Login1Model.UserName);
// execute a query
dr = DB.ExecuteReader(sql.ToString(), param);
if (!dr.Read()) // no record found
{
dr.Close();
return(false);
}
int AD_User_ID = Util.GetValueOfInt(dr[0].ToString()); //User Id
roles = new List <KeyNamePair>(); //roles
List <int> usersRoles = new List <int>();
do // read all roles
{
AD_User_ID = Util.GetValueOfInt(dr[0].ToString());
int AD_Role_ID = Util.GetValueOfInt(dr[1].ToString());
String Name = dr[2].ToString();
KeyNamePair p = new KeyNamePair(AD_Role_ID, Name);
roles.Add(p);
usersRoles.Add(AD_Role_ID);
}while (dr.Read());
dr.Close();
model.Login1Model.AD_User_ID = AD_User_ID;
IDataReader drLogin = null;
if (model.Login2Model == null)
{
try
{
//* Change sub query into ineer join */
drLogin = DB.ExecuteReader(" SELECT l.AD_Role_ID," +
" (SELECT r.Name FROM AD_ROLE r WHERE r.AD_Role_ID=l.AD_ROLE_ID) as RoleName," +
" l.AD_Org_ID," +
" (SELECT o.Name FROM AD_Org o WHERE o.AD_Org_ID=l.AD_Org_ID) as OrgName," +
" l.AD_Client_ID," +
" (SELECT c.Name FROM AD_Client c WHERE c.AD_Client_ID=l.AD_Client_ID) as ClientName," +
" l.M_Warehouse_ID," +
" (SELECT m.Name FROM M_Warehouse m WHERE m.M_Warehouse_Id = l.M_Warehouse_ID) as WarehouseName" +
" FROM AD_LoginSetting l WHERE l.IsActive = 'Y' AND l.AD_User_ID=" + AD_User_ID);
if (drLogin.Read())
{
bool deleteRecord = false;
//1 firt check - Check role exist
//if (usersRoles.Contains(Util.GetValueOfInt(drLogin[0])))
//{
// //check for Org Access Setting
// bool isUseUserOrgAccess = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsUseUserOrgAccess FROM AD_ROLE WHERE AD_ROLE_ID = " + drLogin[0].ToString())) == "Y";
// if (isUseUserOrgAccess) //User User Org
// {
// if (Convert.ToInt32(DB.ExecuteScalar("SELECT Count(1) FROM AD_User_OrgAccess WHERE AD_User_ID = " + AD_User_ID + " AND AD_ORG_ID= " + drLogin[2].ToString() + " AND IsActive='Y'")) < 1)
// {
// deleteRecord = true;
// }
// }
// else //User Role Org Access
// {
// if (Convert.ToInt32(DB.ExecuteScalar("SELECT Count(1) FROM AD_Role_OrgAccess WHERE AD_Role_ID = " + drLogin[0] + " AND AD_ORG_ID= " + drLogin[2].ToString() + " AND IsActive='Y'")) < 1)
// {
// deleteRecord = true;
// }
// }
//}
//else
//{
// deleteRecord = true;
//}
//Delete Login Setting
if (deleteRecord)
{
DB.ExecuteQuery("DELETE FROM AD_LoginSetting WHERE AD_User_ID = " + AD_User_ID);
}
else
{
model.Login2Model = new Login2Model();
model.Login2Model.Role = drLogin[0].ToString();
model.Login2Model.RoleName = drLogin[1].ToString();
model.Login2Model.Org = drLogin[2].ToString();
model.Login2Model.OrgName = drLogin[3].ToString();
model.Login2Model.Client = drLogin[4].ToString();
model.Login2Model.ClientName = drLogin[5].ToString();
model.Login2Model.Warehouse = drLogin[6].ToString();
model.Login2Model.WarehouseName = drLogin[7].ToString();
model.Login2Model.Date = System.DateTime.Now.Date;
}
}
drLogin.Close();
}
catch
{
if (drLogin != null)
{
drLogin.Close();
}
}
}
return(true);
}
/// <summary>
/// return is credential provide by user is right or not
/// </summary>
/// <param name="model">login model class</param>
/// <param name="roles">out roles , has role list of user</param>
/// <param name="ctx" ></param>
/// <returns>true if athenicated</returns>
public static bool Login(LoginModel model, out List <KeyNamePair> roles)
{
// loginModel = null;
//bool isMatch = false;
roles = null;
SecureEngine.Encrypt("t"); //Initialize
// Cannot use encrypted password
//if ())
//{
// //log.warning("Cannot use Encrypted Password");
// return false;
//}
// Authentification
bool authenticated = false;
bool isLDAP = false;
MSystem system = MSystem.Get(new Ctx());
string output = "";
if (system != null && system.IsLDAP())
{
authenticated = system.IsLDAP(model.Login1Model.UserValue, model.Login1Model.Password, out output);
isLDAP = true;
}
//Save Failed Login Count and Password validty in cache
GetSysConfigForlogin();
int fCount = Util.GetValueOfInt(cache[Common.Failed_Login_Count_Key]);
int passwordValidUpto = Util.GetValueOfInt(cache[Common.Password_Valid_Upto_Key]);
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@username", model.Login1Model.UserValue);
DataSet dsUserInfo = DB.ExecuteDataset("SELECT AD_User_ID, Value, Password,IsLoginUser,FailedLoginCount, IsOnlyLDAP FROM AD_User WHERE Value=@username", param);
if (dsUserInfo != null && dsUserInfo.Tables[0].Rows.Count > 0)
{
// skipped Login user check for SuperUser (100)
if (!cache["SuperUserVal"].Equals(model.Login1Model.UserValue) &&
!dsUserInfo.Tables[0].Rows[0]["IsLoginUser"].ToString().Equals("Y"))
{
throw new Exception("NotLoginUser");
}
// output length will be greater than 0 if there is any error while ldap auth.
//output check is applied to becuase after first login, when user redriect to home page, this functioexecutes again and password is null on that time.
// so ldap reject auth , but user is actually authenticated. so to avoid error, this check is used.
if (!cache["SuperUserVal"].Equals(model.Login1Model.UserValue) && dsUserInfo.Tables[0].Rows[0]["IsOnlyLDAP"].ToString().Equals("Y") &&
isLDAP && !authenticated)
{
throw new Exception(output);
}
}
else
{
throw new Exception("UserNotFound");
}
//if authenticated by LDAP or password is null(Means request from home page)
if (!authenticated && model.Login1Model.Password != null)
{
string sqlEnc = "SELECT isencrypted FROM ad_column WHERE ad_table_id=(SELECT ad_table_id FROM ad_table WHERE tablename='AD_User') AND columnname='Password'";
char isEncrypted = Convert.ToChar(DB.ExecuteScalar(sqlEnc));
string originalpwd = model.Login1Model.Password;
if (isEncrypted == 'Y' && model.Login1Model.Password != null)
{
model.Login1Model.Password = SecureEngine.Encrypt(model.Login1Model.Password);
}
// DataSet dsUserInfo = DB.ExecuteDataset("SELECT AD_User_ID, Value, Password,IsLoginUser,FailedLoginCount FROM AD_User WHERE Value=@username", param);
if (dsUserInfo != null && dsUserInfo.Tables[0].Rows.Count > 0)
{
//if username or password is not matching
if ((!dsUserInfo.Tables[0].Rows[0]["Value"].Equals(model.Login1Model.UserValue) ||
!dsUserInfo.Tables[0].Rows[0]["Password"].Equals(model.Login1Model.Password)) ||
(originalpwd != null && SecureEngine.IsEncrypted(originalpwd)))
{
//if current user is Not superuser, then increase failed login count
if (!cache["SuperUserVal"].Equals(model.Login1Model.UserValue))
{
param[0] = new SqlParameter("@username", model.Login1Model.UserValue);
int count = DB.ExecuteQuery("UPDATE AD_User Set FAILEDLOGINCOUNT=FAILEDLOGINCOUNT+1 WHERE Value=@username ", param);
if (fCount > 0 && fCount <= Util.GetValueOfInt(dsUserInfo.Tables[0].Rows[0]["FailedLoginCount"]) + 1)
{
throw new Exception("MaxFailedLoginAttempts");
}
}
throw new Exception("UserPwdError");
}
else// if username and password matched, then check if account is locked or not
{
if (fCount > 0 && fCount <= Util.GetValueOfInt(dsUserInfo.Tables[0].Rows[0]["FailedLoginCount"]))
{
throw new Exception("MaxFailedLoginAttempts");
}
}
}
}
IDataReader dr = GetRoles(model.Login1Model.UserValue, authenticated, isLDAP);
if (!dr.Read()) // no record found, then return msaage that role not found.
{
dr.Close();
throw new Exception("RoleNotDefined");
}
// if user logged in successfully, then set failed login count to 0
DB.ExecuteQuery("UPDATE AD_User SET FailedLoginCount=0 WHERE Value=@username", param);
int AD_User_ID = Util.GetValueOfInt(dr[0].ToString()); //User Id
if (!cache["SuperUserVal"].Equals(model.Login1Model.UserValue))
{
String Token2FAKey = Util.GetValueOfString(dr["TokenKey2FA"]);
bool enable2FA = Util.GetValueOfString(dr["Is2FAEnabled"]) == "Y";
if (enable2FA)
{
model.Login1Model.QRFirstTime = false;
TwoFactorAuthenticator tfa = new TwoFactorAuthenticator();
SetupCode setupInfo = null;
string userSKey = Util.GetValueOfString(dr["Value"]);
int ADUserID = Util.GetValueOfInt(dr["AD_User_ID"]);
// if token key don't exist for user, then create new
if (Token2FAKey.Trim() == "")
{
model.Login1Model.QRFirstTime = true;
Token2FAKey = userSKey;
// get Random Number
model.Login1Model.TokenKey2FA = GetRndNum();
// create Token key based on Value, UserID and Random Number
Token2FAKey = userSKey + ADUserID.ToString() + model.Login1Model.TokenKey2FA;
}
else
{
// Decrypt token key saved in database
string decKey = SecureEngine.Decrypt(Token2FAKey);
Token2FAKey = userSKey + ADUserID.ToString() + decKey;
}
string url = Util.GetValueOfString(HttpContext.Current.Request.Url.AbsoluteUri).Replace("VIS/Account/JsonLogin", "").Replace("https://", "").Replace("http://", "");
setupInfo = tfa.GenerateSetupCode("VA ", url + " " + userSKey, Token2FAKey, 150, 150);
model.Login1Model.QRCodeURL = setupInfo.QrCodeSetupImageUrl;
}
model.Login1Model.Is2FAEnabled = enable2FA;
}
if (!authenticated)
{
DateTime?pwdExpireDate = Util.GetValueOfDateTime(dr["PasswordExpireOn"]);
if (pwdExpireDate == null || (passwordValidUpto > 0 && (DateTime.Compare(DateTime.Now, Convert.ToDateTime(pwdExpireDate)) > 0)))
{
model.Login1Model.ResetPwd = true;
//if (SecureEngine.IsEncrypted(model.Login1Model.Password))
// model.Login1Model.Password = SecureEngine.Decrypt(model.Login1Model.Password);
}
}
roles = new List <KeyNamePair>(); //roles
List <int> usersRoles = new List <int>();
string username = "";
do // read all roles
{
AD_User_ID = Util.GetValueOfInt(dr[0].ToString());
int AD_Role_ID = Util.GetValueOfInt(dr[1].ToString());
String Name = dr[2].ToString();
KeyNamePair p = new KeyNamePair(AD_Role_ID, Name);
username = Util.GetValueOfString(dr["username"].ToString());
roles.Add(p);
usersRoles.Add(AD_Role_ID);
}while (dr.Read());
dr.Close();
model.Login1Model.AD_User_ID = AD_User_ID;
model.Login1Model.DisplayName = username;
IDataReader drLogin = null;
if (model.Login2Model == null)
{
try
{
//* Change sub query into ineer join */
drLogin = DB.ExecuteReader(" SELECT l.AD_Role_ID," +
" (SELECT r.Name FROM AD_ROLE r WHERE r.AD_Role_ID=l.AD_ROLE_ID) as RoleName," +
" l.AD_Org_ID," +
" (SELECT o.Name FROM AD_Org o WHERE o.AD_Org_ID=l.AD_Org_ID) as OrgName," +
" l.AD_Client_ID," +
" (SELECT c.Name FROM AD_Client c WHERE c.AD_Client_ID=l.AD_Client_ID) as ClientName," +
" l.M_Warehouse_ID," +
" (SELECT m.Name FROM M_Warehouse m WHERE m.M_Warehouse_Id = l.M_Warehouse_ID) as WarehouseName" +
" FROM AD_LoginSetting l WHERE l.IsActive = 'Y' AND l.AD_User_ID=" + AD_User_ID);
if (drLogin.Read())
{
bool deleteRecord = false;
//Delete Login Setting
if (deleteRecord)
{
DB.ExecuteQuery("DELETE FROM AD_LoginSetting WHERE AD_User_ID = " + AD_User_ID);
}
else
{
model.Login2Model = new Login2Model();
model.Login2Model.Role = drLogin[0].ToString();
model.Login2Model.RoleName = drLogin[1].ToString();
model.Login2Model.Org = drLogin[2].ToString();
model.Login2Model.OrgName = drLogin[3].ToString();
model.Login2Model.Client = drLogin[4].ToString();
model.Login2Model.ClientName = drLogin[5].ToString();
model.Login2Model.Warehouse = drLogin[6].ToString();
model.Login2Model.WarehouseName = drLogin[7].ToString();
model.Login2Model.Date = System.DateTime.Now.Date;
}
}
drLogin.Close();
}
catch
{
if (drLogin != null)
{
drLogin.Close();
}
}
}
return(true);
}
