14 Replies Latest reply: Oct 2, 2013 9:53 AM by oralicious RSS

    Prevent user locking account

    oralicious

      We have a policy to lock a user after 3 failed login attempts but this can cause problems for other users.   

       

      Is there a native way to prevent a user from locking account with the wrong password by doing something like

       

      if failed login attempts = 2

         where OS_USER=xxx and MACHINE=yyyy 

         then

         prevent OS_USER=xxx and MACHINE=yyy from logging in.

         and email DBA_GROUP and OS_USER that OS_USER is being bold.

      end if

       

       

      It could be coded without too much hassle from a login trigger I suppose but wondering if theres a native Oracle security policy that could be enabled that would do same.

        • 1. Re: Prevent user locking account
          sb92075

          is application 3-tier (as below)?

           

           

           

          EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

          • 2. Re: Prevent user locking account
            EdStevens

            Failed logon attempts is controlled by the profile assigned to the user.

            If you have two different users with two different requirements, create two different profiles.

            What do you care about which machine user FRED is using?

            And as SB alluded, be aware of the difference between a user logging on with his own individual credentials vs. an application logging on with its credentials on behalf of all users.

            • 3. Re: Prevent user locking account
              oralicious

              in part but also some users use odbc to connect regularly through excel and access and we cant restrict these. 

               

              we issue preconfigured DSNs but have had a couple of stray examples of users building their own DSN with incorrect passwords or meaning to connect to a dev database but putting a production connect string in instead. Its rare enough but if it does happen can cause problems, its at this point we'd like to say "only stop this user from connecting rather than killing it for everyone else"

               

              we have auditing responsibilities so the 3 strike rule on the locked account must be kept.

              • 4. Re: Prevent user locking account
                oralicious

                hi ed, see my above answer below which may clarify the connection questions.  

                 

                We have pretty good user education where theyre told to only connect with a particular username but experience tells us that if something can go wrong it will, from a userbase of 200+ we've only had 2 examples this year of it happening but even thats too much so we'd like to take the possability of them locking a user away from them by having this higher level check.

                 

                I was interested in the machine name as it would be a way of including in a login trigger to deny access.  but it doesnt really need to have it, os_user would be fine.

                • 5. Re: Prevent user locking account
                  oralicious

                  SEC_MAX_FAILED_LOGIN_ATTEMPTS

                   

                  this will probably do it, thanks guys

                  • 6. Re: Prevent user locking account
                    oralicious

                    sorry, no, after a test, that wont do it as that parameter is for invalid logins and security feature to stop a hack on a connection, not a valid username/incorrect rotating attempted password.   account will still lock.  back to drawing board.

                    • 7. Re: Prevent user locking account
                      rp0428
                      in part but also some users use odbc to connect regularly through excel and access and we cant restrict these.

                       

                      we issue preconfigured DSNs but have had a couple of stray examples of users building their own DSN with incorrect passwords or meaning to connect to a dev database but putting a production connect string in instead. Its rare enough but if it does happen can cause problems, its at this point we'd like to say "only stop this user from connecting rather than killing it for everyone else"

                      How can one user locking their OWN account 'kill it' for anyone else?

                       

                      More likely you are letting multiple users use the SAME username and password to access the database.

                       

                      The solution is: DON'T DO THAT!

                       

                      That is a HUGE security risk and is totally unnecessary.

                      we have auditing responsibilities so the 3 strike rule on the locked account must be kept.

                       

                      Then you need to fix your GAPING HOLE in your security! The best way to do that is to LOCK those shared accounts and create a new one for each user. That will fix the problem in this thread since one user can no longer 'kill it' for anyone else.

                       

                      For those rare use cases where a single account really needs to be shared Oracle provides that functionality in the guise of 'proxy' users. This AskTom thread explains it pretty well.

                      http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:21575905259251

                       

                      See the SQL Language doc for the details

                      http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4003.htm

                      Proxy Users: Examples The following statement alters the user app_user1. The example permits the app_user1 to connect through the proxy user sh. The example also allows app_user1 to enable its warehouse_user role (created in "Creating a Role: Example") when connected through the proxy sh:

                      ALTER USER app_user1  GRANT CONNECT THROUGH sh  WITH ROLE warehouse_user;  

                      To show basic syntax, this example uses the sample database Sales History user (sh) as the proxy. Normally a proxy user would be an application server or middle-tier entity. For information on creating the interface between an application user and a database by way of an application server, refer to Oracle Call Interface Programmer's Guide.

                       

                      This Oracle-Base article has a simple example

                      http://www.oracle-base.com/articles/misc/proxy-users-and-connect-through.php#proxy-user-and-connect-through

                       

                      • 8. Re: Prevent user locking account
                        oralicious

                        I will not disagree with any of your points RP and raised the concerns when I first started here a year ago.

                         

                        We have several applications that run, lets say they use connection string with oracle user FRED.  If  someone tried to connect through odbc or sqlplus with user FRED it will lock.  The app uses its own verification (select username/password from app_login_table) and would take a lot of redesign to pull it out of it so thats not going to happen  despite the bad practice.  So I have to deal with the current situation.  

                         

                        Through user education its engrained that noone uses FRED on production database so the times its happened it has been in error.

                        • 9. Re: Prevent user locking account
                          jgarry

                          I recall one place where someone didn't like the big kahuna, simple denial of service attack by mysterious failed logins every morning.

                           

                          If that is the rule, it should be a big pain for you and the users.  You should track and report to management.  You need finer security granularity (which is what rp described).

                          • 10. Re: Prevent user locking account
                            oralicious

                            "You should track and report to management. "


                            We do and we do.

                            • 11. Re: Prevent user locking account
                              rp0428

                               

                              We have several applications that run, lets say they use connection string with oracle user FRED.  If  someone tried to connect through odbc or sqlplus with user FRED it will lock.  The app uses its own verification (select username/password from app_login_table) and would take a lot of redesign to pull it out of it so thats not going to happen  despite the bad practice.  So I have to deal with the current situation. 

                               

                              Through user education its engrained that noone uses FRED on production database so the times its happened it has been in error.

                              So what? Even if everything you just said above is true what difference does that make?

                               

                              You can just change the password to FRED so the application can use it. Just don't tell that password to the users.

                               

                              And you still haven't said why that anyone needs to connect directly to FRED. Give that 'someone' their own account and give that account ONLY the privileges that they really need. Even if you want them to have the same privileges as FRED just set up a proxy like I explained above and let them connect that way. Then they do NOT need FRED's password so they can't possibly lock the FRED account.

                               

                              I still don't see any issue.

                              • 12. Re: Prevent user locking account
                                oralicious

                                rp0428 wrote:

                                 

                                 

                                We have several applications that run, lets say they use connection string with oracle user FRED.  If  someone tried to connect through odbc or sqlplus with user FRED it will lock.  The app uses its own verification (select username/password from app_login_table) and would take a lot of redesign to pull it out of it so thats not going to happen  despite the bad practice.  So I have to deal with the current situation.

                                 

                                Through user education its engrained that noone uses FRED on production database so the times its happened it has been in error.

                                So what? Even if everything you just said above is true what difference does that make?

                                 

                                You can just change the password to FRED so the application can use it. Just don't tell that password to the users.

                                 

                                And you still haven't said why that anyone needs to connect directly to FRED. Give that 'someone' their own account and give that account ONLY the privileges that they really need. Even if you want them to have the same privileges as FRED just set up a proxy like I explained above and let them connect that way. Then they do NOT need FRED's password so they can't possibly lock the FRED account.

                                 

                                I still don't see any issue.

                                 

                                In our production, they don't need to connect directly as FRED, its a mistake when someone does.  Some developers do use the FRED account in development,  a mistyped connection string has been the most recent example of this issue occurring.  

                                • 13. Re: Prevent user locking account
                                  EdStevens

                                  964472 wrote:

                                   

                                  rp0428 wrote:

                                   

                                   

                                  We have several applications that run, lets say they use connection string with oracle user FRED.  If  someone tried to connect through odbc or sqlplus with user FRED it will lock.  The app uses its own verification (select username/password from app_login_table) and would take a lot of redesign to pull it out of it so thats not going to happen  despite the bad practice.  So I have to deal with the current situation.

                                   

                                  Through user education its engrained that noone uses FRED on production database so the times its happened it has been in error.

                                  So what? Even if everything you just said above is true what difference does that make?

                                   

                                  You can just change the password to FRED so the application can use it. Just don't tell that password to the users.

                                   

                                  And you still haven't said why that anyone needs to connect directly to FRED. Give that 'someone' their own account and give that account ONLY the privileges that they really need. Even if you want them to have the same privileges as FRED just set up a proxy like I explained above and let them connect that way. Then they do NOT need FRED's password so they can't possibly lock the FRED account.

                                   

                                  I still don't see any issue.

                                   

                                  In our production, they don't need to connect directly as FRED, its a mistake when someone does.  Some developers do use the FRED account in development,  a mistyped connection string has been the most recent example of this issue occurring.  

                                   

                                  they don't need to connect directly as FRED

                                   

                                  So is there ever a legitimate need for any user or any application to connect directly as FRED?  If not, if FRED only exists to be a schema owner, perhaps you should simply revoke CREATE SESSION from FRED.

                                  • 14. Re: Prevent user locking account
                                    oralicious

                                    EdStevens wrote:

                                     

                                     

                                    they don't need to connect directly as FRED

                                     

                                    So is there ever a legitimate need for any user or any application to connect directly as FRED?  If not, if FRED only exists to be a schema owner, perhaps you should simply revoke CREATE SESSION from FRED.

                                     

                                    The application does need to login with it, users shouldn't ever directly, we also have database links set up with some app usernames that would be affected if they didnt have create session.

                                     

                                    I'll say again I know its not a great design that many subsequent different developers and DBAs had a hand in but to pull out from it now would take a lot of design that I've been told while it is on the radar isnt going to happen anytime soon

                                     

                                    The issue of locking a username by mistake needs to be removed through use of a security feature or a workaround as password_life unlimited cant happen. The earlier parameter sec_maxed_failed_logins would have been perfect but that doesn't persist across multiple connections from a client.