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;
}
/// <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);
}
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 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="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="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;
}
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="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;
}
/// <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>
/// 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;
}
}
}
}
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)));
}
/// <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 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 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);
}
}
protected void viewHomePage_PreRender(object sender, EventArgs e)
{
IEnumerable <RcReport> listRc;
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
const string QUERY = @"
select t.report_id as report_id, t.user_name as user_name, t.report_version as report_version,
t.approval_status_date as approval_status_date,
<if c='$version'>
case when <a pre=""t.report_version IN ("" sep="","" post="")"">(:version)</a> then approval_status end
</if>
<else>
NULL
</else>
as approval_status,
t.comments as comments
from DCMSLIVE_USER_REPORT t
where <a pre=""t.report_id IN ("" sep="","" post="")"">(:report_id)</a>
";
db.CreateConnection(ConfigurationManager.ConnectionStrings["dcmslive"].ConnectionString, HttpContext.Current.User.Identity.Name);
var binder = SqlBinder.Create(row => new
{
ReportId = row.GetString("report_id"),
RcApprover = new RcUserApprovalStatus
{
DbStatus = row.GetString("approval_status"),
StatusDate = row.GetDate("approval_status_date"),
UserId = row.GetString("user_name"),
UserComment = row.GetString("comments")
},
VersionNumber = row.GetString("report_version"),
});
binder.ParameterXmlArray("report_id", SiteMap.Providers["Rc"].RootNode.ChildNodes.Cast <SiteMapNode>().Select(p => p.Key).ToArray());
binder.ParameterXmlArray("version", SiteMap.Providers["Rc"].RootNode.ChildNodes.Cast <SiteMapNode>().Select(p => p["version"]).ToArray());
var result = db.ExecuteReader(QUERY, binder);
// LinqQuery generates an entry for each RC report. Each RC report contains a list of approval statuses
listRc = from flatData in
(from SiteMapNode node in SiteMap.Providers["Rc"].RootNode.ChildNodes
join row in result on node.Key equals row.ReportId into outer
from row in outer.DefaultIfEmpty()
where node["Browsable"] != "false"
select new
{
Node = node,
Row = row
})
group flatData by flatData.Node into g
select new RcReport(g.Key, g.Where(p => p.Row != null).Select(p => p.Row.RcApprover));
}
repRc.DataSource = listRc;
repRc.DataBind();
panelRc.HeaderText = string.Format(panelRc.HeaderText, listRc.Count());
// This is a list of rc waiting for current user's approval
var query = (from rc in listRc
where rc.ListPending.Any(p => p.UserId == HttpContext.Current.User.Identity.Name)
select rc.ReportNumber).ToList();
if (query.Count > 0)
{
string reportno = query.Count == 1 ? "Report" : "Reports";
string reports = string.Join(",", query);
divMsg.InnerText = string.Format("{0} {1} waiting for your approval", reportno, reports);
}
}
/// <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>
/// 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>
/// 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 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 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>
/// 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 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>
/// 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;
}