Example #1
0
        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)));
        }
Example #2
0
 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;
 }
Example #3
0
        /// <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;
        }
Example #6
0
        /// <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;
 }
Example #8
0
        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;
        }
Example #10
0
 /// <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;
 }
Example #11
0
        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;
        }
Example #12
0
        //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;
        }
Example #13
0
        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;
        }
Example #14
0
        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;
        }
Example #15
0
        /// <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();
                }
            }
        }
Example #16
0
        /// <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;
        }
Example #17
0
        /// <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;
        }
Example #18
0
        /// <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;
        }
Example #19
0
        /// <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;
                    }
                }
            }
        }
Example #20
0
        /// <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;
                    }
                }
            }
        }
Example #21
0
        /// <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);
            }
        }
Example #22
0
        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);
        }
Example #23
0
        /// <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));
            }
        }
Example #24
0
        /// <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;
                    }
                }
            }
        }
Example #25
0
        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)));
        }
Example #26
0
        /// <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);
            }
        }
Example #29
0
    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;
            }
        }
Example #31
0
        /// <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;
                        }
                    }
                }
            }
        }
Example #35
0
        /// <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();
                }
            }
        }
Example #37
0
        /// <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;
        }