/// <summary>
/// Returns all matching users who do not own any schema objects. Thus all users returned can potentially be deleted.
/// </summary>
/// <param name="usernameToMatch">This can contain the wildcard character %</param>
/// <param name="pageIndex">Not used</param>
/// <param name="pageSize">Not used</param>
/// <param name="totalRecords">Not used</param>
/// <returns></returns>
public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
{
EnsureDefaultProfile();
const string QUERY = @"SELECT U.USERNAME, U.USER_ID, U.LOCK_DATE, U.CREATED, U.EXPIRY_DATE
FROM DBA_USERS U
WHERE UPPER(U.USERNAME) LIKE :TERM
AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)
AND u.profile IN <a pre='(' sep=',' post=')'>:profile</a>
ORDER BY U.USERNAME";
var binder = SqlBinder.Create(src => new OracleMembershipUser(
userName: src.GetString("USERNAME"),
providerUserKey: src.GetInteger("USER_ID").ToString(),
lastLockoutDate: src.GetDate("LOCK_DATE") ?? DateTime.MinValue,
createDate: src.GetDate("created") ?? DateTime.MinValue,
passwordExpiryDate: src.GetDate("expiry_date") ?? DateTime.MinValue
));
binder.Parameter("TERM", usernameToMatch.ToUpper());
binder.ParameterXmlArray("profile", _visibleProfiles);
var result = new MembershipUserCollection();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, string.Empty);
var usersList = db.ExecuteReader(QUERY, binder);
foreach (var user in usersList)
{
result.Add(user);
}
}
totalRecords = result.Count;
return(result);
}
public virtual ActionResult KillSession(UserSession model)
{
OracleDatastore db = null;
try
{
db = new OracleDatastore(this.HttpContext.Trace);
DbConnectionStringBuilder dcms8 = new DbConnectionStringBuilder();
dcms8.ConnectionString = ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString;
// Creating the connection as super user
db.CreateConnection(dcms8.ConnectionString, string.Empty);
const string QUERY_ALTER_USER = "******";
var sql = string.Format(QUERY_ALTER_USER, model.SessionId, model.SerialNumber);
db.ExecuteNonQuery(sql, null);
AddStatusMessage(string.Format("Session of user {0} kill successfully", model.UserName));
}
catch (ProviderException ex)
{
ModelState.AddModelError("", ex.Message);
}
finally
{
if (db != null)
{
db.Dispose();
}
}
return(RedirectToAction(Actions.ManageUser(model.UserName)));
}
public SkuEntityRepository(string userName, string clientInfo)
{
_db = new OracleDatastore(HttpContext.Current.Trace);
_db.CreateConnection(ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString, userName);
_db.ModuleName = "Inquiry_CartonEntity";
_db.ClientInfo = clientInfo;
}
internal void Initialize(TraceContext trace, string connectionString, string userName, string clientInfo)
{
var store = new OracleDatastore(trace);
store.CreateConnection(connectionString, userName);
store.ModuleName = "DcmsLite";
store.ClientInfo = clientInfo;
_db = store;
}
/// <summary>
/// Constructor of class used to create the connection to database.
/// </summary>
/// <param name="userName"></param>
/// <param name="clientInfo"></param>
/// <param name="trace"></param>
/// <param name="connectString"> </param>
public RestockRepository(string userName, string clientInfo, TraceContext trace, string connectString)
{
var store = new OracleDatastore(trace);
store.CreateConnection(connectString, userName);
store.ClientInfo = clientInfo;
_db = store;
_userName = userName;
}
/// <summary>
/// Constructor of class used to create the connection to database.
/// </summary>
/// <param name="userName"></param>
/// <param name="clientInfo"></param>
/// <param name="trace"></param>
/// <param name="connectString"> </param>
public DiagnosticRepository(string userName, string clientInfo, TraceContext trace, string connectString)
{
var store = new OracleDatastore(trace);
store.CreateConnection(connectString, userName);
store.ClientInfo = clientInfo;
store.ModuleName = "PieceReplenish";
_db = store;
}
/// <summary>
/// Constructor of class used to create the connection to database.
/// </summary>
/// <param name="connectString"> </param>
/// <param name="userName"></param>
/// <param name="clientInfo"></param>
/// <param name="ctx"> </param>
public AutoCompleteRepository(TraceContext ctx, string connectString, string userName, string clientInfo)
{
var db = new OracleDatastore(ctx);
db.CreateConnection(connectString, userName);
db.ModuleName = MODULE_NAME;
db.ClientInfo = clientInfo;
db.DefaultMaxRows = 10000; // Allow retrieving up to 10000 rows. Number of cartons can be huge
_db = db;
}
public void Dispose()
{
var disp = _db as IDisposable;
if (disp != null)
{
disp.Dispose();
}
_db = null;
}
public VasConfigurationRepository(TraceContext ctx, string connectString, string userName, string clientInfo, string moduleName)
{
var db = new OracleDatastore(ctx);
db.CreateConnection(connectString, userName);
db.ModuleName = moduleName;
db.ClientInfo = clientInfo;
_db = db;
}
/// <summary>
/// Constructor of class used to create the connection to database.
/// </summary>
/// <param name="userName"></param>
/// <param name="moduleName"></param>
/// <param name="clientInfo"></param>
/// <param name="trace"></param>
public CartonAreasRepository(string userName, string moduleName, string clientInfo, TraceContext trace)
{
Contract.Assert(ConfigurationManager.ConnectionStrings["dcms8"] != null);
var store = new OracleDatastore(trace);
store.CreateConnection(ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString,
userName);
store.ModuleName = moduleName;
store.ClientInfo = clientInfo;
_db = store;
}
/// <summary>
/// Constructor of class used to create the connection to database.
/// </summary>
/// <param name="requestContext"></param>
public RepackRepository(RequestContext requestContext)
{
_db = new OracleDatastore(requestContext.HttpContext.Trace);
_db.CreateConnection(ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString,
requestContext.HttpContext.SkipAuthorization ? string.Empty : requestContext.HttpContext.User.Identity.Name);
// Sharad 20 Dec 2011: The module code must be RPK because reason code philosophy embedded in
// package IFR_ISI special handles reason codes for this module.
_db.ModuleName = "RPK";
_db.ClientInfo = string.IsNullOrEmpty(requestContext.HttpContext.Request.UserHostName) ? requestContext.HttpContext.Request.UserHostAddress :
requestContext.HttpContext.Request.UserHostName;
}
public HomeRepository(TraceContext trace)
{
var connectStringName = "dcms8";
Contract.Assert(ConfigurationManager.ConnectionStrings[connectStringName] != null);
var store = new OracleDatastore(trace);
store.CreateConnection(ConfigurationManager.ConnectionStrings[connectStringName].ConnectionString, string.Empty);
store.ModuleName = "Inquiry";
_db = store;
}
//protected const string MODULE_CODE = "PickWaveManager";
protected PickWaveRepositoryBase(TraceContext ctx, string userName, string clientInfo)
{
var db = new OracleDatastore(ctx);
var connectString = ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString;
db.CreateConnection(connectString, userName);
db.ModuleName = "PickWaveManager";
db.ClientInfo = clientInfo;
_db = db;
}
public BoxPickRepository(RequestContext requestContext)
{
Contract.Assert(ConfigurationManager.ConnectionStrings["dcms8"] != null);
var store = new OracleDatastore(requestContext.HttpContext.Trace);
store.CreateConnection(ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString, requestContext.HttpContext.User.Identity.Name);
store.ModuleName = "BOXPICK";
store.ClientInfo = string.IsNullOrEmpty(requestContext.HttpContext.Request.UserHostName) ? requestContext.HttpContext.Request.UserHostAddress :
requestContext.HttpContext.Request.UserHostName;
_db = store;
}
public IntransityEntityRepository(TraceContext ctx, string connectString, string userName, string clientInfo)
{
var db = new OracleDatastore(ctx);
db.CreateConnection(connectString, userName);
db.ModuleName = "Inquiry";
db.ClientInfo = clientInfo;
db.DefaultMaxRows = 10000; // Allow retrieving up to 10000 rows. Number of cartons can be huge
_db = db;
}
/// <summary>
/// Returns true if we are able to successfully connect to oracle using the supplied username and password.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <exception cref="System.Web.Security.MembershipPasswordException">Password has expired and needs to be changed before login can be allowed</exception>
/// <returns></returns>
public override bool ValidateUser(string username, string password)
{
var builder = new OracleConnectionStringBuilder(_connectionString)
{
UserID = username,
Password = password,
Pooling = false,
ProxyUserId = string.Empty,
ProxyPassword = string.Empty
};
OracleDatastore db = null;
try
{
db = new OracleDatastore(HttpContext.Current.Trace);
db.CreateConnection(builder.ConnectionString, string.Empty);
db.Connection.Open();
return(true);
}
catch (OracleException ex)
{
// Connection could not be opened
Trace.TraceWarning(ex.Message);
switch (ex.Number)
{
case 1017:
// Invalid user name password
Trace.TraceWarning("Invalid password specified for user {0}", username);
return(false);
case 28001:
// Password expired
throw new MembershipPasswordException("Password has expired. Please change your password and try again.", ex);
default:
throw;
}
}
finally
{
// For clearing the cached roles of the user.
OracleRoleProvider orp = Roles.Providers.OfType <OracleRoleProvider>().SingleOrDefault();
if (orp != null)
{
orp.ClearRoleCache(username);
}
if (db != null)
{
db.Dispose();
}
}
}
/// <summary>
/// Constructor of class used to create the connection to database.
/// </summary>
/// <param name="userName"></param>
/// <param name="moduleName"></param>
/// <param name="clientInfo"></param>
/// <param name="trace"></param>
public ReqRepository(string userName, string moduleName, string clientInfo, TraceContext trace)
{
const string MODULE_CODE = "REQ2";
Contract.Assert(ConfigurationManager.ConnectionStrings["dcms8"] != null);
var store = new OracleDatastore(trace);
store.CreateConnection(ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString,
userName);
store.ModuleName = MODULE_CODE;
store.ClientInfo = clientInfo;
_db = store;
}
/// <summary>
/// Constructor of class used to create the connection to database.
/// </summary>
/// <param name="requestContext"></param>
public RadRepository(RequestContext requestContext)
{
Contract.Assert(ConfigurationManager.ConnectionStrings["dcms4"] != null);
var store = new OracleDatastore(requestContext.HttpContext.Trace);
store.CreateConnection(ConfigurationManager.ConnectionStrings["dcms4"].ConnectionString,
requestContext.HttpContext.SkipAuthorization ? string.Empty : requestContext.HttpContext.User.Identity.Name);
Debug.Assert(requestContext.HttpContext.Request.Url != null, "requestContext.HttpContext.Request.Url != null");
store.ModuleName = requestContext.HttpContext.Request.Url.AbsoluteUri;
store.ClientInfo = string.IsNullOrEmpty(requestContext.HttpContext.Request.UserHostName) ? requestContext.HttpContext.Request.UserHostAddress :
requestContext.HttpContext.Request.UserHostName;
_db = store;
}
/// <summary>
/// Constructor of class used to create the connection to database.
/// </summary>
/// <param name="requestContext"></param>
public ReceivingRepository(RequestContext requestContext)
{
var db = new OracleDatastore(requestContext.HttpContext.Trace);
db.CreateConnection(ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString,
requestContext.HttpContext.SkipAuthorization ? string.Empty : requestContext.HttpContext.User.Identity.Name);
// This is a well known module code so that receving reports can reliably access receiving records from src_carton_process table.
db.ModuleName = MODULE_NAME;
db.ClientInfo = string.IsNullOrEmpty(requestContext.HttpContext.Request.UserHostName) ? requestContext.HttpContext.Request.UserHostAddress :
requestContext.HttpContext.Request.UserHostName;
_db = db;
}
/// <summary>
/// Item1 is name. Item2 is description
/// </summary>
/// <returns></returns>
public static IList <Tuple <string, string> > GetPrinters(OracleDatastore db, PrinterType printerType)
{
Contract.Assert(db != null);
const string QUERY = @"
SELECT NAME AS NAME, DESCRIPTION AS DESCRIPTION
FROM <proxy />TAB_PRINTER
WHERE PRINTER_TYPE = :PRINTER_TYPE
ORDER BY NAME ASC
";
var binder = SqlBinder.Create(row => Tuple.Create(row.GetString("NAME"), row.GetString("DESCRIPTION")))
.Parameter("PRINTER_TYPE", printerType == PrinterType.LabelPrinter ? "ZEBRA" : "LASER");
return(db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// The password is changed to <paramref name="answer"/>. The password is set to expire immediately which will force the user to change password at next login.
/// </summary>
/// <param name="username">Name of the user need to reset password</param>
/// <param name="answer">The new password, or empty to randomply generate a password</param>
/// <returns>This function will return the new assigned password</returns>
/// <remarks>
/// <para>
/// The logged in user must have the rights for resetting password of a user. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT ALTER USER TO <user-name>;
/// ]]>
/// </code>
///</remarks>
///
public override string ResetPassword(string username, string answer)
{
var rand = new Random();
if (string.IsNullOrEmpty(answer))
{
answer = rand.Next(1, (int)Math.Pow(10, this.MinRequiredPasswordLength) - 1).ToString().PadRight(this.MinRequiredPasswordLength, '1');
}
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for resetting a user's password");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("ALTER USER {0} IDENTIFIED BY \"{1}\" PASSWORD EXPIRE", username, answer);
db.ExecuteNonQuery(sqlQuery, null);
return(answer);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1935:
//1935: missing user or role name (comes when username is null). Not expected as we are already checking the passed user.
case 922:
//922: Missing or invalid option (comes when username contains special chars or whitespace)
throw new ProviderException("User name is invalid", ex);
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for resetting password.", ex);
default:
throw;
}
}
}
}
/// <summary>
/// This function is for deleting an existing user.
/// </summary>
/// <param name="username"></param>
/// <param name="deleteAllRelatedData">Ignored</param>
/// <returns>This function will return True if user successfully deleted else return False</returns>
/// <remarks>
/// <para>
/// The logged in user must have the rights to drop a User. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT DROP USER To <user-name>;
/// ]]>
/// </code>
/// </remarks>
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for deleting a user");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("DROP USER {0}", username);
db.ExecuteNonQuery(sqlQuery, null);
return(true);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for deleting a user.", ex);
case 1918:
//1918: user does not exist
throw new ProviderException(string.Format("User {0} does not exits", username), ex);
case 921:
//921: invalid username
throw new ProviderException("User name is invalid", ex);
case 1940:
//1940: Already logged in user is trying to delete itself.
throw new ProviderException("Cannot drop a user that is currently connected");
default:
throw;
}
}
}
}
/// <summary>
/// Following function will return all users that are assigned with the passed role.
/// </summary>
/// <param name="roleName"></param>
/// <returns>Array of roles</returns>
/// <remarks>
/// Users who own schema objects are not returned by this function.
/// </remarks>
public override string[] GetUsersInRole(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
/* Oracle 11gR2 hierarchical query
* Inspired by http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
*/
const string ROLE_USERS = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT DISTINCT NVL(SUBSTR(Q.PATH, 1, INSTR(Q.PATH, '/', 1 , 1) - 1), Q.PATH) AS USERS
FROM Q1 Q
WHERE Q.GRANTED_ROLE = :roleName
AND NVL(SUBSTR(Q.PATH, 1, INSTR(Q.PATH, '/', 1 , 1) - 1), Q.PATH) not in
(SELECT OWNER FROM DBA_OBJECTS)
ORDER BY USERS";
//var binder = new SqlBinder<string>("Retreving users.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(ROLE_USERS);
//binder.Query = ROLE_USERS;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
var roles = db.ExecuteReader(ROLE_USERS, binder);
var usersInRole = roles.ToArray();
return(usersInRole);
}
}
private static IList <RcUserApprovalStatus> GetApprovalStatus(string reportId, string version)
{
IList <RcUserApprovalStatus> approvers;
const string QUERY = @"
Select user_name,
<if c='$version'>
case when report_version =:version then approval_status end
</if>
<else>
NULL
</else>
as approval_status,
<if c='$version'>
case when report_version =:version then comments end
</if>
<else>
NULL
</else>
as comments,
approval_status_date
from dcmslive_user_report
where report_id = :report_id and is_approver = 'Y'
";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(ConfigurationManager.ConnectionStrings["dcmslive"].ConnectionString, "");
var binder = SqlBinder.Create(row => new RcUserApprovalStatus
{
UserId = row.GetString("user_name"),
DbStatus = row.GetString("approval_status"),
UserComment = row.GetString("comments"),
StatusDate = row.GetDate("approval_status_date")
});
binder.Parameter("report_id", reportId);
binder.Parameter("version", version);
approvers = db.ExecuteReader(QUERY, binder);
}
return(approvers);
}
/// <summary>
/// This function is for checking whether the passed role is a priv.
/// </summary>
/// <param name="roleName"></param>
private bool IsRolePriv(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
if (!_privTablesExist)
{
// This cannot be a privilege
return(false);
}
const string strQuery = @"
SELECT PRIV.PRIV_ID FROM <proxy />PRIV PRIV WHERE PRIV.PRIV_ID = :rolename
";
//var binder = new SqlBinder<string>("Retreving priv.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(strQuery);
//binder.Query = strQuery;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
string strPriv = "";
try
{
strPriv = db.ExecuteSingle(strQuery, binder);
}
catch (OracleDataStoreException ex)
{
if (ex.OracleErrorNumber == 942)
{
// Table PRIV does not exist. Stay silent
_privTablesExist = false;
}
}
return(!string.IsNullOrEmpty(strPriv));
}
}
/// <summary>
/// This function is for un-locking a locked user account.
/// </summary>
/// <param name="userName"></param>
/// <remarks>
/// <para>
/// The logged in user must have the rights for resetting password of a user. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT ALTER USER TO <user-name>;
/// ]]>
/// </code>
///</remarks>
/// <returns>This function will return True on successful unlock else return False</returns>
public override bool UnlockUser(string userName)
{
if (string.IsNullOrWhiteSpace(userName))
{
throw new ArgumentNullException("userName");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for un locking a user account");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("ALTER USER {0} ACCOUNT UNLOCK", userName);
db.ExecuteNonQuery(sqlQuery, null);
return(true);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for unlocking a locked user account.", ex);
case 1918:
//1918: user does not exist
throw new ProviderException(string.Format("User {0} does not exits", userName), ex);
default:
throw;
}
}
}
}
/// <summary>
/// This function is for checking whether the passed role exists or not.
/// </summary>
/// <param name="roleName"></param>
/// <exception cref="NotImplementedException"></exception>
/// <returns>boolean</returns>
public override bool RoleExists(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
const string QUERY_PRIV = @"SELECT PRIV_ID FROM <proxy />PRIV WHERE PRIV_ID = :rolename";
const string QUERY_ROLE = @"SELECT ROLE AS ROLE FROM DBA_ROLES WHERE ROLE = :rolename";
var strQuery = IsRolePriv(roleName) ? QUERY_PRIV : QUERY_ROLE;
//var binder = new SqlBinder<string>("Retreving users.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(strQuery);
//binder.Query = strQuery;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
return(!string.IsNullOrEmpty(db.ExecuteSingle(strQuery, binder)));
}
}
public virtual ActionResult LockedUser(ManageUserViewModel model)
{
if (string.IsNullOrWhiteSpace(model.User.UserName))
{
throw new ArgumentNullException("userName");
}
OracleDatastore db = null;
try
{
db = new OracleDatastore(this.HttpContext.Trace);
DbConnectionStringBuilder dcms8 = new DbConnectionStringBuilder();
dcms8.ConnectionString = ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString;
// Creating the connection as super user
db.CreateConnection(dcms8.ConnectionString, string.Empty);
const string QUERY_ALTER_USER = "******";
var sql = string.Format(QUERY_ALTER_USER, model.User.UserName);
db.ExecuteNonQuery(sql, null);
AddStatusMessage(string.Format("{0} user account has been locked", model.User.UserName));
}
catch (ProviderException ex)
{
ModelState.AddModelError("", ex.Message);
}
finally
{
if (db != null)
{
db.Dispose();
}
}
return(RedirectToAction(Actions.ManageUser(model.User.UserName)));
}
public VasConfigurationRepository(OracleDatastore db)
{
_db = db;
}
/// <summary>
/// Following function will return all users that are assigned with the passed role.
/// </summary>
/// <param name="roleName"></param>
/// <returns>Array of roles</returns>
/// <remarks>
/// Users who own schema objects are not returned by this function.
/// </remarks>
public override string[] GetUsersInRole(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
/* Oracle 11gR2 hierarchical query
* Inspired by http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
*/
const string ROLE_USERS = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT DISTINCT NVL(SUBSTR(Q.PATH, 1, INSTR(Q.PATH, '/', 1 , 1) - 1), Q.PATH) AS USERS
FROM Q1 Q
WHERE Q.GRANTED_ROLE = :roleName
AND NVL(SUBSTR(Q.PATH, 1, INSTR(Q.PATH, '/', 1 , 1) - 1), Q.PATH) not in
(SELECT OWNER FROM DBA_OBJECTS)
ORDER BY USERS";
//var binder = new SqlBinder<string>("Retreving users.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(ROLE_USERS);
//binder.Query = ROLE_USERS;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
var roles = db.ExecuteReader(ROLE_USERS, binder);
var usersInRole = roles.ToArray();
return usersInRole;
}
}
/// <summary>
/// This function is for revoking the passed roles from the passed users.
/// </summary>
/// <param name="usernames"></param>
/// <param name="roleNames"></param>
/// <remarks>
/// <para>
/// Empty user names and roles are silently ignored. All user names and roles are converted to upper case before they are processed.
/// </para>
/// <para>
/// The logged in user must have the rights to revoke roles. The logged in user must also have the delete rights on table upriv to delete user's priv.
/// Follwing are the scripts.
/// </para>
/// <code>
/// <![CDATA[
/// grant GRANT ANY ROLE to <user-name>
/// grant DELETE on URPIV to <user-name>
/// ]]>
/// </code>
/// </remarks>
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials to remove roles from users");
}
var joinUsersRoles = from user in usernames
from role in roleNames
where !string.IsNullOrEmpty(user) &&
!string.IsNullOrEmpty(role)
select new
{
Role = role.Trim().ToUpper(),
User = user.Trim().ToUpper()
};
const string QUERY_PRIV = @"DELETE <proxy />UPRIV WHERE ORACLE_USER_NAME ='{0}' AND PRIV_ID ='{1}'";
const string QUERY_ROLE = @"REVOKE {1} FROM {0}";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
foreach (var item in joinUsersRoles)
{
var query = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
ClearRoleCache(item.User);
try
{
db.ExecuteNonQuery(query, null);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1919:
case 942:
case 1031:
// 1919: Role does not exist
// 942 : UPRIV table does not exist. To us this means no rights to delete from UPRIV
// 1031: Rights to revoke the role are not avaliable.
throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to revoke this role", item.Role));
case 1951:
// Role not granted. Check whether the role has been granted inderectly.
const string QUERY_ROLE_PATH = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT substr(path, instr(path, '/') + 1)
FROM Q1 Q
WHERE Q.PATH LIKE :username || '/%'
and q.granted_role = :rolename
";
// Execute this query as super user
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
//var binder = new SqlBinder<string>("Get Role Path");
var binder = SqlBinder.Create(row => row.GetString(0));
//binder.CreateMapper(QUERY_ROLE_PATH);
//binder.Query = QUERY_ROLE_PATH;
//binder.Factory = row => row.GetString();
binder.Parameter("username", item.User);
binder.Parameter("rolename", item.Role);
var path = db.ExecuteSingle<string>(QUERY_ROLE_PATH, binder);
if (!string.IsNullOrEmpty(path))
{
var roleToRevoke = path.Split('/').First();
throw new ProviderException(
string.Format(
"Role {0} has indirectly granted to user {1} and cannot be revoked directly. {2}/{0}. To revoke {0} role revoke {3} role.",
item.Role, item.User, path, roleToRevoke));
}
throw new ProviderException(ex.Message);
case 1917:
throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));
default:
throw;
}
}
}
}
}
/// <summary>
/// This function is for checking whether the passed role exists or not.
/// </summary>
/// <param name="roleName"></param>
/// <exception cref="NotImplementedException"></exception>
/// <returns>boolean</returns>
public override bool RoleExists(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
const string QUERY_PRIV = @"SELECT PRIV_ID FROM <proxy />PRIV WHERE PRIV_ID = :rolename";
const string QUERY_ROLE = @"SELECT ROLE AS ROLE FROM DBA_ROLES WHERE ROLE = :rolename";
var strQuery = IsRolePriv(roleName) ? QUERY_PRIV : QUERY_ROLE;
//var binder = new SqlBinder<string>("Retreving users.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(strQuery);
//binder.Query = strQuery;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
return !string.IsNullOrEmpty(db.ExecuteSingle(strQuery, binder));
}
}
/// <summary>
/// This function is for granting the passed roles to the passed users.
/// </summary>
/// <param name="usernames"></param>
/// <param name="roleNames"></param>
/// <remarks>
/// <para>
/// The logged in user must have the rights to add roles. The logged in user must also have the insert rights to add upriv.
/// Following are the scripts.
/// </para>
/// <code>
/// <![CDATA[
/// grant GRANT ANY ROLE to <user-name>
/// grant INSERT on URPIV to <user-name>
/// ]]>
/// </code>
/// </remarks>
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials to add role to a user");
}
var joinUsersRoles = from user in usernames
from role in roleNames
where !string.IsNullOrEmpty(user) &&
!string.IsNullOrEmpty(role)
select new
{
Role = role.Trim().ToUpper(),
User = user.Trim().ToUpper()
};
const string QUERY_PRIV = @"INSERT INTO <proxy />UPRIV (PRIV_ID,ORACLE_USER_NAME) VALUES ('{1}','{0}')";
const string QUERY_ROLE = @"GRANT {1} to {0}";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
//var binder = new SqlBinder<string>("Granting Roles.");
var binder = SqlBinder.Create();
foreach (var item in joinUsersRoles)
{
var sqlQuery = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
ClearRoleCache(item.User);
try
{
db.ExecuteNonQuery(sqlQuery, null);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1919:
case 942:
case 1031:
// 1919: Role does not exist
// 942 : UPRIV table does not exist. To us this means no rights to insert into table UPRIV
//1031 : Rights to insert the upriv are not avaliable
throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to grant this role", item.Role));
case 1917:
throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));
case 1:
//Priv already assigned to the user(UNIQUE CONSTRAINT VOILATED) remain silent and move further.
continue;
default:
throw;
}
}
}
}
}
/// <summary>
/// This function is for checking whether the passed role is a priv.
/// </summary>
/// <param name="roleName"></param>
private bool IsRolePriv(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
if (!_privTablesExist)
{
// This cannot be a privilege
return false;
}
const string strQuery = @"
SELECT PRIV.PRIV_ID FROM <proxy />PRIV PRIV WHERE PRIV.PRIV_ID = :rolename
";
//var binder = new SqlBinder<string>("Retreving priv.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(strQuery);
//binder.Query = strQuery;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
string strPriv = "";
try
{
strPriv = db.ExecuteSingle(strQuery, binder);
}
catch (OracleDataStoreException ex)
{
if (ex.OracleErrorNumber == 942)
{
// Table PRIV does not exist. Stay silent
_privTablesExist = false;
}
}
return !string.IsNullOrEmpty(strPriv);
}
}
/// <summary>
/// Returns all roles assigned to the passed user
/// </summary>
/// <param name="username"></param>
/// <returns>Array of roles</returns>
/// <remarks>
/// <para>
/// Roles assigned to roles are also properly handled
/// </para>
/// </remarks>
public override string[] GetRolesForUser(string username)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
RoleCache cached;
if (_userRoles.TryGetValue(username, out cached))
{
// Found the roles in the cache.
if (DateTime.Now - cached.TimeStamp > MAX_CACHE_DURATION)
{
// Cache is stale. Ignore it.
_userRoles.TryRemove(username, out cached);
}
else
{
// Thankfully query is avoided
return cached.Roles;
}
}
if (string.Compare(_connectionStringBuilder.ProxyUserId, username, true) == 0)
{
return new[] { "WEB_PROXYUSER" };
}
/*
* TODO: Use this new query which uses recursive subquery syntax instead of CONNECT BY. This syntax was introduced in 11gR2
* Inspired by http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
*/
const string QUERY_ALL_ROLES = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT DISTINCT Q.GRANTED_ROLE AS ROLES
FROM Q1 Q
WHERE (Q.PATH = :username OR Q.PATH LIKE :username || '/%')
ORDER BY ROLES
";
const string QUERY_PRIVILEGES = @"
SELECT T.PRIV_ID AS PRIVS
FROM <proxy />UPRIV T
WHERE T.ORACLE_USER_NAME = :username
ORDER BY PRIVS
";
cached = new RoleCache { TimeStamp = DateTime.Now };
//var binder = new SqlBinder<string>("Querying Roles and privileges");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("username", username.ToUpper());
//binder.Query = QUERY_ALL_ROLES;
//binder.Factory = row => row.GetString();
//binder.CreateMapper(QUERY_ALL_ROLES);
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
IEnumerable<string> roles = db.ExecuteReader(QUERY_ALL_ROLES, binder);
//binder.Query = QUERY_PRIVILEGES;
IEnumerable<string> privs;
try
{
privs = db.ExecuteReader(QUERY_PRIVILEGES, binder);
}
catch (OracleDataStoreException ex)
{
if (ex.OracleErrorNumber == 942)
{
// Table or view does not exist. Stay silent
privs = Enumerable.Empty<string>();
}
else
{
throw;
}
}
cached.Roles = roles.Concat(privs).ToArray();
_userRoles.TryAdd(username, cached);
return cached.Roles;
}
}
/// <summary>
/// This method return session log of user
/// </summary>
/// <param name="username"></param>
/// <param name="db"></param>
/// <returns>
/// The returned info contains session log of the user as well.
/// </returns>
private IEnumerable<OracleMembershipUserSession> DoGetUserSessions(string username, OracleDatastore db)
{
const string QUERY_SESSIONS = @"
SELECT S.SID,
S.SERIAL#,
S.PROGRAM,
S.STATUS,
S.OSUSER,
S.MACHINE,
S.MODULE,
S.ACTION,
S.CLIENT_INFO,
S.LOGON_TIME,
S.STATE
FROM GV$SESSION S
WHERE S.TYPE = 'USER'
AND S.USERNAME IS NOT NULL
AND S.USERNAME = :USERNAME
ORDER BY S.USERNAME DESC
";
//var binderSession = new SqlBinder<OracleMembershipUserSession>("Querying User properties");
var binderSession = SqlBinder.Create(row => new OracleMembershipUserSession
{
SessionId = row.GetInteger("SID").Value,
SerialNumber = row.GetInteger("SERIAL#").Value,
OsExecutableName = row.GetString("PROGRAM"),
IsActive = row.GetString("STATUS") == "ACTIVE",
OsUserName = row.GetString("OSUSER"),
MachineName = row.GetString("MACHINE"),
Module = row.GetString("MODULE"),
ActionName = row.GetString("ACTION"),
ClientInfo = row.GetString("CLIENT_INFO"),
LogonTime = row.GetDate("LOGON_TIME").Value,
State = row.GetString("STATE")
});
binderSession.Parameter("USERNAME", username.ToUpper());
//binderSession.Query = QUERY_SESSIONS;
//binderSession.Factory = row => new OracleMembershipUserSession
//{
// SessionId = row.GetInteger("SID").Value,
// SerialNumber = row.GetInteger("SERIAL#").Value,
// OsExecutableName = row.GetString("PROGRAM"),
// IsActive = row.GetString("STATUS") == "ACTIVE",
// OsUserName = row.GetString("OSUSER"),
// MachineName = row.GetString("MACHINE"),
// Module = row.GetString("MODULE"),
// ActionName = row.GetString("ACTION"),
// ClientInfo = row.GetString("CLIENT_INFO"),
// LogonTime = row.GetDate("LOGON_TIME").Value,
// State = row.GetString("STATE")
//};
//binderSession.CreateMapper(QUERY_SESSIONS, config => config.CreateMap<OracleMembershipUserSession>()
// .MapField("SID", p => p.SessionId)
// .MapField("SERIAL#", p => p.SerialNumber)
// .MapField("PROGRAM", p => p.OsExecutableName)
// .ForMember(dest => dest.IsActive, opt => opt.MapFrom(src => src.GetValue<string>("STATUS") == "ACTIVE"))
// .MapField("OSUSER", p => p.OsUserName)
// .MapField("MACHINE", p => p.MachineName)
// .MapField("MODULE", p => p.Module)
// .MapField("ACTION", p => p.ActionName)
// .MapField("CLIENT_INFO", p => p.ClientInfo)
// .MapField("LOGON_TIME", p => p.LogonTime)
// .MapField("STATE", p => p.State)
//);
return db.ExecuteReader(QUERY_SESSIONS, binderSession);
}
//private int _queryCount;
/// <summary>
/// For injecting the value through unit tests
/// </summary>
/// <param name="db"></param>
public ReceivingRepository(OracleDatastore db)
{
_db = db;
}
/// <summary>
/// Returns true if we are able to successfully connect to oracle using the supplied username and password.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <exception cref="System.Web.Security.MembershipPasswordException">Password has expired and needs to be changed before login can be allowed</exception>
/// <returns></returns>
public override bool ValidateUser(string username, string password)
{
var builder = new OracleConnectionStringBuilder(_connectionString)
{
UserID = username,
Password = password,
Pooling = false,
ProxyUserId = string.Empty,
ProxyPassword = string.Empty
};
OracleDatastore db = null;
try
{
db = new OracleDatastore(HttpContext.Current.Trace);
db.CreateConnection(builder.ConnectionString, string.Empty);
db.Connection.Open();
return true;
}
catch (OracleException ex)
{
// Connection could not be opened
Trace.TraceWarning(ex.Message);
switch (ex.Number)
{
case 1017:
// Invalid user name password
Trace.TraceWarning("Invalid password specified for user {0}", username);
return false;
case 28001:
// Password expired
throw new MembershipPasswordException("Password has expired. Please change your password and try again.", ex);
default:
throw;
}
}
finally
{
// For clearing the cached roles of the user.
OracleRoleProvider orp = Roles.Providers.OfType<OracleRoleProvider>().SingleOrDefault();
if (orp != null)
{
orp.ClearRoleCache(username);
}
if (db != null)
{
db.Dispose();
}
}
}
/// <summary>
/// This method return audit log of user
/// </summary>
/// <param name="username"></param>
/// <param name="db"></param>
/// <returns>
/// The returned info contains audit log of the user as well.
/// </returns>
private static IList<OracleMembershipUserAudit> DoGetUserAudit(string username, OracleDatastore db)
{
const string QUERY_AUDIT = @"
(SELECT T.ACTION_NAME AS ACTION_NAME,
NULL AS ROLE_NAME,
T.USERNAME AS USERNAME,
T.OS_USERNAME AS OS_USERNAME,
T.TERMINAL AS TERMINAL,
DECODE(T.RETURNCODE, 0, 'SUCCESS', 'FAILURE') AS RESULT,
T.TIMESTAMP AS TIMESTAMP
FROM DBA_AUDIT_TRAIL T
WHERE T.ACTION_NAME IN ('CREATE USER', 'DROP USER', 'ALTER USER')
AND T.OBJ_NAME = :USERNAME
UNION ALL
SELECT T.ACTION_NAME,
T.OBJ_NAME,
T.USERNAME,
T.OS_USERNAME,
T.TERMINAL,
DECODE(T.RETURNCODE, 0, 'SUCCESS', 'FAILURE') AS RESULT,
T.TIMESTAMP
FROM DBA_AUDIT_TRAIL T
WHERE T.ACTION_NAME IN ('GRANT ROLE', 'REVOKE ROLE')
AND T.GRANTEE = :USERNAME
UNION ALL
SELECT 'GRANT DCMS PRIVILEGE',
PRIV_ID,
NVL(MODIFIED_BY, CREATED_BY),
NULL,
NULL,
'SUCCESS',
NVL(DATE_MODIFIED, DATE_CREATED)
FROM UPRIV
WHERE UPRIV.ORACLE_USER_NAME = :USERNAME)
ORDER BY TIMESTAMP DESC";
//var binderAudit = new SqlBinder<OracleMembershipUserAudit>("Querying User properties");
var binderAudit = SqlBinder.Create(row => new OracleMembershipUserAudit
{
ActionName = row.GetString("ACTION_NAME"),
RoleName = row.GetString("ROLE_NAME"),
Result = row.GetString("RESULT"),
ByOsUserName = row.GetString("OS_USERNAME"),
TerminalName = row.GetString("TERMINAL"),
ActionTime = row.GetDate("TIMESTAMP").Value,
ByOracleUserName = row.GetString("USERNAME"),
});
binderAudit.Parameter("USERNAME", username.ToUpper());
//binderAudit.Query = QUERY_AUDIT;
//binderAudit.Factory = row => new OracleMembershipUserAudit
//{
// ActionName = row.GetString("ACTION_NAME"),
// RoleName = row.GetString("ROLE_NAME"),
// Result = row.GetString("RESULT"),
// ByOsUserName = row.GetString("OS_USERNAME"),
// TerminalName = row.GetString("TERMINAL"),
// ActionTime = row.GetDate("TIMESTAMP").Value,
// ByOracleUserName = row.GetString("USERNAME"),
//};
//binderAudit.CreateMapper(QUERY_AUDIT, config => config.CreateMap<OracleMembershipUserAudit>()
// .MapField("ACTION_NAME", p => p.ActionName)
// .MapField("ROLE_NAME", p => p.RoleName)
// .MapField("RESULT", p => p.Result)
// .MapField("OS_USERNAME", p => p.ByOsUserName)
// .MapField("TERMINAL", p => p.TerminalName)
// .MapField("TIMESTAMP", p => p.ActionTime)
// .MapField("USERNAME", p => p.ByOracleUserName)
//);
return db.ExecuteReader(QUERY_AUDIT, binderAudit);
}
/// <summary>
/// The password is changed to <paramref name="answer"/>. The password is set to expire immediately which will force the user to change password at next login.
/// </summary>
/// <param name="username">Name of the user need to reset password</param>
/// <param name="answer">The new password, or empty to randomply generate a password</param>
/// <returns>This function will return the new assigned password</returns>
/// <remarks>
/// <para>
/// The logged in user must have the rights for resetting password of a user. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT ALTER USER TO <user-name>;
/// ]]>
/// </code>
///</remarks>
///
public override string ResetPassword(string username, string answer)
{
var rand = new Random();
if (string.IsNullOrEmpty(answer))
{
answer = rand.Next(1, (int)Math.Pow(10, this.MinRequiredPasswordLength) - 1).ToString().PadRight(this.MinRequiredPasswordLength, '1');
}
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for resetting a user's password");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("ALTER USER {0} IDENTIFIED BY \"{1}\" PASSWORD EXPIRE", username, answer);
db.ExecuteNonQuery(sqlQuery, null);
return answer;
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1935:
//1935: missing user or role name (comes when username is null). Not expected as we are already checking the passed user.
case 922:
//922: Missing or invalid option (comes when username contains special chars or whitespace)
throw new ProviderException("User name is invalid", ex);
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for resetting password.", ex);
default:
throw;
}
}
}
}
/// <summary>
/// This function is for un-locking a locked user account.
/// </summary>
/// <param name="userName"></param>
/// <remarks>
/// <para>
/// The logged in user must have the rights for resetting password of a user. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT ALTER USER TO <user-name>;
/// ]]>
/// </code>
///</remarks>
/// <returns>This function will return True on successful unlock else return False</returns>
public override bool UnlockUser(string userName)
{
if (string.IsNullOrWhiteSpace(userName))
{
throw new ArgumentNullException("userName");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for un locking a user account");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("ALTER USER {0} ACCOUNT UNLOCK", userName);
db.ExecuteNonQuery(sqlQuery, null);
return true;
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for unlocking a locked user account.", ex);
case 1918:
//1918: user does not exist
throw new ProviderException(string.Format("User {0} does not exits", userName), ex);
default:
throw;
}
}
}
}
/// <summary>
/// Returns full information about the passed user
/// </summary>
/// <param name="username"></param>
/// <param name="userIsOnline"></param>
/// <returns></returns>
/// <remarks>
/// The returned info contains audit log of the user as well
/// and also returned info contains session log of user.
/// Users who own schema objects are not visible to this function since our goal is to manage application users only. We do not want to manage
/// application owners.
/// </remarks>
public override MembershipUser GetUser(string username, bool userIsOnline)
{
if (string.IsNullOrEmpty(username))
{
throw new ArgumentNullException("username");
}
OracleMembershipUser user;
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
//db.ConnectionString = _connectionString;
//db.ProviderName = _providerName;
db.CreateConnection(_connectionString, string.Empty);
const string QUERY = @"
SELECT U.USERNAME AS USERNAME,
U.LOCK_DATE AS LOCK_DATE,
U.CREATED AS CREATION_DATE,
U.USER_ID AS USER_ID,
U.EXPIRY_DATE AS EXPIRYDATE
FROM DBA_USERS U
WHERE U.USERNAME = :USERNAME
AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)";
//var binder = new SqlBinder<OracleMembershipUser>("Querying User properties");
var binder = SqlBinder.Create(src => new OracleMembershipUser(
userName: src.GetString("USERNAME"),
providerUserKey: src.GetInteger("USER_ID").ToString(),
lastLockoutDate: src.GetDate("LOCK_DATE") ?? DateTime.MinValue,
createDate: src.GetDate("CREATION_DATE").Value,
passwordExpiryDate: src.GetDate("ExpiryDate") ?? DateTime.MinValue
));
binder.Parameter("username", username.ToUpper());
//binder.CreateMapper(QUERY, config => config.CreateMap<OracleMembershipUser>()
// .ConstructUsing(src => new OracleMembershipUser
// (
// userName: src.GetValue<string>("USERNAME"),
// providerUserKey: src.GetValue<int>("USER_ID").ToString(),
// lastLockoutDate: src.GetValue<DateTime>("LOCK_DATE"),
// createDate: src.GetValue<DateTime>("CREATION_DATE"),
// passwordExpiryDate: src.GetValue<DateTime>("ExpiryDate")
// )).ForAllMembers(opt => opt.Ignore()));
//binder.Query = QUERY;
//binder.Factory = src => new OracleMembershipUser(
// userName: src.GetString("USERNAME"),
// providerUserKey: src.GetInteger("USER_ID").ToString(),
// lastLockoutDate: src.GetDate("LOCK_DATE").Value,
// createDate: src.GetDate("CREATION_DATE").Value,
// passwordExpiryDate: src.GetDate("ExpiryDate").Value
// );
user = db.ExecuteSingle(QUERY, binder);
if (user != null)
{
user.AuditLog = DoGetUserAudit(username, db);
user.Sessions = DoGetUserSessions(username, db);
}
}
return user;
}
/// <summary>
/// Returns all matching users who do not own any schema objects. Thus all users returned can potentially be deleted.
/// </summary>
/// <param name="usernameToMatch">This can contain the wildcard character %</param>
/// <param name="pageIndex">Not used</param>
/// <param name="pageSize">Not used</param>
/// <param name="totalRecords">Not used</param>
/// <returns></returns>
public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
{
EnsureDefaultProfile();
const string QUERY = @"SELECT U.USERNAME, U.USER_ID, U.LOCK_DATE, U.CREATED, U.EXPIRY_DATE
FROM DBA_USERS U
WHERE UPPER(U.USERNAME) LIKE :TERM
AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)
AND u.profile IN <a pre='(' sep=',' post=')'>:profile</a>
ORDER BY U.USERNAME";
var binder = SqlBinder.Create(src => new OracleMembershipUser(
userName: src.GetString("USERNAME"),
providerUserKey: src.GetInteger("USER_ID").ToString(),
lastLockoutDate: src.GetDate("LOCK_DATE") ?? DateTime.MinValue,
createDate: src.GetDate("created") ?? DateTime.MinValue,
passwordExpiryDate: src.GetDate("expiry_date") ?? DateTime.MinValue
));
binder.Parameter("TERM", usernameToMatch.ToUpper());
binder.ParameterXmlArray("profile", _visibleProfiles);
var result = new MembershipUserCollection();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, string.Empty);
var usersList = db.ExecuteReader(QUERY, binder);
foreach (var user in usersList)
{
result.Add(user);
}
}
totalRecords = result.Count;
return result;
}
/// <summary>
/// This function is for deleting an existing user.
/// </summary>
/// <param name="username"></param>
/// <param name="deleteAllRelatedData">Ignored</param>
/// <returns>This function will return True if user successfully deleted else return False</returns>
/// <remarks>
/// <para>
/// The logged in user must have the rights to drop a User. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT DROP USER To <user-name>;
/// ]]>
/// </code>
/// </remarks>
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for deleting a user");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("DROP USER {0}", username);
db.ExecuteNonQuery(sqlQuery, null);
return true;
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for deleting a user.", ex);
case 1918:
//1918: user does not exist
throw new ProviderException(string.Format("User {0} does not exits", username), ex);
case 921:
//921: invalid username
throw new ProviderException("User name is invalid", ex);
case 1940:
//1940: Already logged in user is trying to delete itself.
throw new ProviderException("Cannot drop a user that is currently connected");
default:
throw;
}
}
}
}
/// <summary>
/// This function is for creating a new user.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <param name="email">Ignored</param>
/// <param name="passwordQuestion">Ignored</param>
/// <param name="passwordAnswer">Ignored</param>
/// <param name="isApproved">Ignored</param>
/// <param name="providerUserKey">Ignored</param>
/// <param name="status">
/// <para>
/// Can return InvalidUserName, DuplicateUserName, InvalidPassword or Success
/// </para>
/// </param>
/// <returns>User object when <paramref name="status"/> = Success; null otherwise. </returns>
/// <remarks>
/// <para>
/// The user is always created with an expired password. The default profile is assigned to the user. CONNECT THROUGH rights are given to the proxy user.
/// </para>
/// <para>
/// The logged in user must have the rights to crete User. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT CREATE USER TO <user-name>
/// ]]>
/// </code>
/// </remarks>
public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer,
bool isApproved, object providerUserKey, out MembershipCreateStatus status)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (string.IsNullOrWhiteSpace(password))
{
throw new ArgumentNullException("password");
}
var e = new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(e);
if (e.Cancel)
{
// App decided to cancel user creation
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new MembershipCreateUserException("You must be logged in with proper credentials to create a user");
}
EnsureDefaultProfile();
//var builder = new OracleConnectionStringBuilder(_connectionString);
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("CREATE USER {0} IDENTIFIED BY \"{1}\" PROFILE {2} PASSWORD EXPIRE", username, password, _visibleProfiles[0]);
db.ExecuteNonQuery(sqlQuery, null);
foreach (var proxy in _proxyUsers)
{
sqlQuery = string.Format("ALTER USER {0} GRANT CONNECT THROUGH {1}", username, proxy);
db.ExecuteNonQuery(sqlQuery, null);
}
status = MembershipCreateStatus.Success;
// GetUser gets too much information, so we are using FindUserByName.
//return GetUser(username, false);
int totalRecords;
return FindUsersByName(username, 0, 100, out totalRecords).Cast<MembershipUser>().First();
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1935:
//1935: missing user or role name (comes when passing null username). Not expected as we are already checking the passed user.
case 922:
//922: Missing or invalid option (comes when password contains special chars or whitespace)
throw new MembershipCreateUserException("User name or password is invalid", ex);
case 1031:
//1031: insufficient privileges
throw new MembershipCreateUserException("You do not have sufficient privileges for creating users.", ex);
case 1920:
//1920: user name 'user-name' conflicts with another user
throw new MembershipCreateUserException(string.Format("User {0} already exists", username));
case 28003:
// ORA-28003: password verification for the specified password failed
throw new MembershipCreateUserException(ex.Message, ex);
default:
throw;
}
}
}
}
/// <summary>
/// The password change will succeed only if the old password is valid.
/// </summary>
/// <param name="username"></param>
/// <param name="oldPassword"></param>
/// <param name="newPassword"></param>
/// <returns>true if password successfully changed. false if the old password is invalid</returns>
/// <remarks>
/// Any data base exception encountered will be propagated to the caller.
/// Sharad 15 Feb 2012: Supported voluntary changes of passwords. Earlier only expired passwords could be changed.
/// Sharad 21 Feb 2012: Raising ValidatingPassword event
/// </remarks>
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (string.IsNullOrWhiteSpace(oldPassword))
{
throw new ArgumentNullException("oldPassword");
}
if (string.IsNullOrWhiteSpace(newPassword))
{
throw new ArgumentNullException("newPassword");
}
var e = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(e);
if (e.Cancel)
{
// App decided to cancel user creation
return false;
}
var builder = new OracleConnectionStringBuilder(_connectionString)
{
UserID = username,
Password = oldPassword,
Pooling = false,
ProxyUserId = string.Empty,
ProxyPassword = string.Empty
};
// Try to login as passed user with old password to ensure that the old password is valid
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
var msg = string.Format("Opening connection to {0} for user {1}",
builder.DataSource, builder.UserID);
Trace.WriteLine(msg, "OracleMembershipProvider");
db.CreateConnection(builder.ConnectionString, builder.UserID);
Trace.WriteLine(msg, "Opening connection with old password");
try
{
db.Connection.Open();
}
catch (OracleException ex)
{
switch (ex.Number)
{
case 1017:
// Invalid user name password
Trace.TraceWarning("Invalid password specified for user {0}", username);
return false;
case 28001:
// If we are using ODP.NET, we can change the password now
// This will only work if the user's password has expired
Trace.WriteLine(msg, "Password expired error oracle exception encountered");
db.Connection.OpenWithNewPassword(newPassword);
return true;
default:
throw;
}
}
// If we get here, the old password was valid. Now we will change the password
//REPLACE is used to remove exception ORA-28221
Trace.WriteLine(msg, "Executing ALTER USER with new password");
var query = string.Format("ALTER USER {0} IDENTIFIED BY \"{1}\" REPLACE \"{2}\"", username, newPassword, oldPassword);
db.ExecuteNonQuery(query, null);
}
return true;
}
