11 Replies Latest reply: Apr 22, 2014 11:07 AM by EdStevens RSS

    When does utlpwdmg.sql run?

    ponderdj

      Questions:

       

      1. When does utlpwdmg.sql run? (Other than when a DBA manually runs it)

           - When a user changes his own PWD?

           - What about when a DBA changes a users's PWD?

           - When a user with a profile assigned a password verify function signs in?

           - Other times?

       

      2. Can you put multiple password verify functions within utlpwdmg.sql and apply different functions to different profiles? If so, does only that function run when a user tries to change their PWD?

       

      3. What about alter statements placed at the bottom of utlpwdmg.sql to modify the password enforcement parameters in dba_profiles. When do those run?

       

      If there's a manual that describes all of this well, I'll happily read it. I just can't find this information anywhere.

       

      Thanks,

      David

        • 1. Re: When does utlpwdmg.sql run?
          Pavan DBA

          1. other than DBA manually execute it, i don't think it will get executed automatically even in the situations you mentioned

          2. yes, we can put multiple functions for different profile. but it is not a good idea to incorporate those functions in utlpwdmg.sql, rather you can create functions separately.

          3. the alter statements will get executed when you run utlpwdmg.sql for one time.

           

          this doc will help you - Contents

          • 3. Re: When does utlpwdmg.sql run?
            Aman....

            ponderdj wrote:

             

            Questions:

             

            1. When does utlpwdmg.sql run? (Other than when a DBA manually runs it)

                 - When a user changes his own PWD?

                 - What about when a DBA changes a users's PWD?

                 - When a user with a profile assigned a password verify function signs in?

                 - Other times?

             

            2. Can you put multiple password verify functions within utlpwdmg.sql and apply different functions to different profiles? If so, does only that function run when a user tries to change their PWD?

             

            3. What about alter statements placed at the bottom of utlpwdmg.sql to modify the password enforcement parameters in dba_profiles. When do those run?

             

            If there's a manual that describes all of this well, I'll happily read it. I just can't find this information anywhere.

             

            Thanks,

            David

            Did search at http://tahiti.oracle.com ?

             

            Aman....

            • 4. Re: When does utlpwdmg.sql run?
              ponderdj

              Pavan DBA wrote:

               

              1. other than DBA manually execute it, i don't think it will get executed automatically even in the situations you mentioned

              2. yes, we can put multiple functions for different profile. but it is not a good idea to incorporate those functions in utlpwdmg.sql, rather you can create functions separately.

              3. the alter statements will get executed when you run utlpwdmg.sql for one time.

               

              this doc will help you - Contents

               

              Well,

               

              Thanks for the quick replies, guys.

               

              I looked at what Hoek provided (thanks, Hoek!) at Configuring Authentication. I had already read much similar material, which is why I think the password verify function runs automatically sometimes. How else would it enforce the logic it holds to prevent a user from creating a non-compliant password? Please note that password complexity and other logic that can live in that function CANNOT be set in any password related value in dba_profiles.password_verify_function. If so, that logic MUST run, and I think it should at least do so when: a user with a profile whose dba_profiles.password_verify_function value is set to a valid function in utlpwdmg.sql tries to change their password.

               

              So, let's say I have two users, two profiles, and two password_verify_functions:USER1 and USER2, PROFILE1 and PROFILE2, and PVF1 and PVF2. Both PVF1 and PVF2 functions exist in UTLPWDMG.SQL, one after the other. USER1 has PROFILE1, which is set to use PVF1 as it's password_verify_function in dba_profiles.password_verify_function. USER1 has PROFILE1, which is set to use PVF1 as it's password_verify_function in dba_profiles.password_verify_function.

               

              PVF1 one has logic to say that password minimum lengths must be 4 characters and PVF2's logic says 8 characters.

               

              AND let's say there are two ALTER PROFILE statements at the bottom of UTLPWDMG.SQL:

               

              ALTER PROFILE PROFILE1 LIMIT

              PASSWORD_LIFE_TIME 60

              PASSWORD_GRACE_TIME 10

              PASSWORD_REUSE_TIME 1800

              PASSWORD_REUSE_MAX UNLIMITED

              FAILED_LOGIN_ATTEMPTS 3

              PASSWORD_LOCK_TIME 1/1440

              PASSWORD_VERIFY_FUNCTION PVF1;

               

              ALTER PROFILE PROFILE2 LIMIT

              PASSWORD_LIFE_TIME 1200

              PASSWORD_GRACE_TIME 20

              PASSWORD_REUSE_TIME 3600

              PASSWORD_REUSE_MAX UNLIMITED

              FAILED_LOGIN_ATTEMPTS 6

              PASSWORD_LOCK_TIME 1/2880

              PASSWORD_VERIFY_FUNCTION PVF2;

               

              Both of these have run at least once, and the matching values in dba_profiles show as much.

               

              Now, let's say USER1 tries to change his password, and he wants to make it 'PWD'

               

              Well, now that value, 'PWD', is only 3 characters long. Since that user has PROFILE1, which is set to use PVF1 as its password_verify_function, and the minimum length logic lives in the function, and Oracle doesn't set a value in dba_profiles for the minimum length check. So, in order for that to error out, PVF1 MUST RUN when he tries to change his password to enforce the minimum length logic, right? If not, why does that logic live there and where else does it live to enforce minimum length? (Password complexity also lives in the function, but let's stick with minimum length for simplicity's sake in the example.)

               

              Now, if USER2 tries to change their password, instead of PVF1, the PVF2 function in UTLPWDMG.SQL will fire and verify the password is at least 8 characters. If this is the case, then the PVF1

               

              Now assuming upon USER2's password change, PVF2 does run and PVF1 does not and assuming that PVF1 runs upon USER1's password change and PVF2 does not. Do the ALTER statements at the bottom of UTLPWDMG.SQL run in either case? That way a DBA could change the ALTER statement(s) in that file and wait for a user to change their password at which time the ALTER statement would run and make the necessary updates. If not, and they never run unless manually run, why is it common for these ALTER statements to live there at all?



              • 5. Re: When does utlpwdmg.sql run?
                Hemant K Chitale

                >PVF1 MUST RUN when he tries to change his password

                Yes, Oracle will execute the function when the user attempts to change his password.

                 

                Once a password verify function is tied to a profile, it is always executed whenever a user holding that profile attempts a password change.

                 

                The DBA does not have to keep running utlpwdmg.sql or issuing any ALTER statements.

                 

                 

                Hemant K Chitale


                • 6. Re: When does utlpwdmg.sql run?
                  jgarry

                  I think if it ran the sql sitting out on the disk that would be a security hole.

                   

                  Pete Finnigan's Oracle Security Forum - Understanding the utlpwdmg.sql

                  • 7. Re: When does utlpwdmg.sql run?
                    EdStevens

                    ponderdj wrote:

                     

                    Pavan DBA wrote:

                     

                    1. other than DBA manually execute it, i don't think it will get executed automatically even in the situations you mentioned

                    2. yes, we can put multiple functions for different profile. but it is not a good idea to incorporate those functions in utlpwdmg.sql, rather you can create functions separately.

                    3. the alter statements will get executed when you run utlpwdmg.sql for one time.

                     

                    this doc will help you - Contents

                     

                    Well,

                     

                    Thanks for the quick replies, guys.

                     

                    I looked at what Hoek provided (thanks, Hoek!) at Configuring Authentication. I had already read much similar material, which is why I think the password verify function runs automatically sometimes. How else would it enforce the logic it holds to prevent a user from creating a non-compliant password?

                    Why would that be an indication that the verify function "runs automatically sometimes".  The function is invoked - and only needs to be invoked - when a password is created/changed.  If it is invoked at the time I try to create/change a password, thus insuring that the password meets the criteria, why would it need to "automatically" run at any other time?    No, there is no process to check existing passwords if the function were to change.  Such a change would only apply going forward.




                    Please note that password complexity and other logic that can live in that function CANNOT be set in any password related value in dba_profiles.password_verify_function.

                    I'm not sure I understand what you are trying to say here.  The only value in 'dba_profiles.password_verify_function' is the name of the function to be called when a user assigned to that profile tries to change there password. That function may have been defined when utlpwdmg.sql was run.  But there is nothing that forces that.  I can and have written my own password verify functions, stored the ddl for said function in my own library of sql scripts and named by my own naming conventions. 


                    If so, that logic MUST run, and I think it should at least do so when: a user with a profile whose dba_profiles.password_verify_function value is set to a valid function in utlpwdmg.sql tries to change their password.

                     

                    And what if the PASSWORD_VERIFY_FUNCTION of the profile assigned to joesunffy is named STRONGPW and was created from a sql file name STRONGPWFUNC.SQL sitting on my desktop?

                     

                     

                    So, let's say I have two users, two profiles, and two password_verify_functions:USER1 and USER2, PROFILE1 and PROFILE2, and PVF1 and PVF2. Both PVF1 and PVF2 functions exist in UTLPWDMG.SQL, one after the other. USER1 has PROFILE1, which is set to use PVF1 as it's password_verify_function in dba_profiles.password_verify_function. USER1 has PROFILE1, which is set to use PVF1 as it's password_verify_function in dba_profiles.password_verify_function.

                     

                    PVF1 one has logic to say that password minimum lengths must be 4 characters and PVF2's logic says 8 characters.

                     

                    AND let's say there are two ALTER PROFILE statements at the bottom of UTLPWDMG.SQL:

                     

                    ALTER PROFILE PROFILE1 LIMIT

                    PASSWORD_LIFE_TIME 60

                    PASSWORD_GRACE_TIME 10

                    PASSWORD_REUSE_TIME 1800

                    PASSWORD_REUSE_MAX UNLIMITED

                    FAILED_LOGIN_ATTEMPTS 3

                    PASSWORD_LOCK_TIME 1/1440

                    PASSWORD_VERIFY_FUNCTION PVF1;

                     

                    ALTER PROFILE PROFILE2 LIMIT

                    PASSWORD_LIFE_TIME 1200

                    PASSWORD_GRACE_TIME 20

                    PASSWORD_REUSE_TIME 3600

                    PASSWORD_REUSE_MAX UNLIMITED

                    FAILED_LOGIN_ATTEMPTS 6

                    PASSWORD_LOCK_TIME 1/2880

                    PASSWORD_VERIFY_FUNCTION PVF2;

                     

                    Both of these have run at least once, and the matching values in dba_profiles show as much.

                     

                    Now, let's say USER1 tries to change his password, and he wants to make it 'PWD'

                     

                    Well, now that value, 'PWD', is only 3 characters long. Since that user has PROFILE1, which is set to use PVF1 as its password_verify_function, and the minimum length logic lives in the function, and Oracle doesn't set a value in dba_profiles for the minimum length check. So, in order for that to error out, PVF1 MUST RUN when he tries to change his password to enforce the minimum length logic, right?

                    Yes the function PVF1 is run.  Not the ddl that defined that function.  utlpwdmg is the ddl defining the function, not the function itself.

                     

                     

                    If not, why does that logic live there and where else does it live to enforce minimum length? (Password complexity also lives in the function, but let's stick with minimum length for simplicity's sake in the example.)

                    "for simplicity's sake in the example is fundamentally flawed.  You said it yourself - "Password complexity also lives in the function".  Actually, there is no "also" about it.  As far as the database is concerned, that is the only place it lives.

                     

                     

                    Now, if USER2 tries to change their password, instead of PVF1, the PVF2 function in UTLPWDMG.SQL will fire and verify the password is at least 8 characters. If this is the case, then the PVF1

                     

                    Now assuming upon USER2's password change, PVF2 does run and PVF1 does not and assuming that PVF1 runs upon USER1's password change and PVF2 does not. Do the ALTER statements at the bottom of UTLPWDMG.SQL run in either case? That way a DBA could change the ALTER statement(s) in that file and wait for a user to change their password at which time the ALTER statement would run and make the necessary updates. If not, and they never run unless manually run, why is it common for these ALTER statements to live there at all?



                     

                    It appears you mis-understand what utlpwdmg.sql is and what it does.  It is simply an Oracle supplied script for creating a default profile and default password verification function.  When a password is set, that action does NOT invoke utlpwdmg.sql.  Rather it invokes the function that is identified by the profile assigned to the user.  By default, that profile will be the DEFAULT profile, and by default that function will be the function that was create for the default profile by the CREATE OR REPLACE FUNCTION statement found within utlpwdmg.sql.  Have you actually read the text of utlpwdmg.sql?  The only thing in my default copy are:

                     

                    CREATE OR REPLACE FUNCTION verify_function_11G  ....

                    ALTER PROFILE DEFAULT ......

                    CREATE OR REPLACE FUNCTION verify_function ...


                    The fact is you can create any profiles you like, and call them anything you like (within oracle rules for object naming).  Further, you can create any password verify function you like, name it anything you like, and assign it to any profile you like.  If you really think that utlpwdmg.sql itself is getting executed at password creation time, why not just rename it to something else (utlpwdmg.sav) and try changing a password?



                    • 8. Re: When does utlpwdmg.sql run?
                      ponderdj

                      Thanks, EdStevens,

                       

                      It's clear that I don't understand the ins and outs of utlpwdmg.sql. That's why I'm here asking questions. I've read several pieces of material explaining Oracle db password security including those referenced by Hoek and jgarry above as well as some Burleson consulting stuff, but in what I've read I've either missed the information or it omits the details that I'm asking for.

                       

                      A couple quick clarifications:

                       

                      First, it's my understanding that the verify_fuctions enforce things not found in dba_profiles like minimum length and complexity. If that's so, then at least that function must run (like you said) when a user with a profile set to use the verify_function tries to change their password. So, what if there are two verify functions in utlpwdmg.sql, PVF1 and PVF2. PROFILE1 is set to use PVF1 and PROFILE2 set to use PVF2. USER1 has PROFILE1, USER2 has PROFILE2.

                           - If USER2 changes their password, then PVF2 runs (gets invoked) and PVF1 does not, correct?

                           - If so, does Oracle have to look in utlpwdmg.sql to find PVF2 or does PVF2 get copied somewhere else where Oracle can refer to it?

                           - If the function gets copied somewhere and not run from utlpwdmg.sql when does that occur and to where is it copied?

                           - If it gets copied somewhere and I want to change it, how would I go about doing that?

                       

                      Finally, if ALTER PROFILE statements at the bottom of utlpwdmg.sql never run automatically (are invoked by an action), why place them there at all? Why wouldn't I just run those manually from SQLPlus / iSQLPlus / etc. ?

                       

                      Thanks again,

                      David

                      • 9. Re: When does utlpwdmg.sql run?
                        rp0428

                        First, it's my understanding that the verify_fuctions enforce things not found in dba_profiles like minimum length and complexity. If that's so, then at least that function must run (like you said) when a user with a profile set to use the verify_function tries to change their password. So, what if there are two verify functions in utlpwdmg.sql, PVF1 and PVF2. PROFILE1 is set to use PVF1 and PROFILE2 set to use PVF2. USER1 has PROFILE1, USER2 has PROFILE2.

                             - If USER2 changes their password, then PVF2 runs (gets invoked) and PVF1 does not, correct?

                             - If so, does Oracle have to look in utlpwdmg.sql to find PVF2 or does PVF2 get copied somewhere else where Oracle can refer to it?

                             - If the function gets copied somewhere and not run from utlpwdmg.sql when does that occur and to where is it copied?

                             - If it gets copied somewhere and I want to change it, how would I go about doing that?

                         

                        Finally, if ALTER PROFILE statements at the bottom of utlpwdmg.sql never run automatically (are invoked by an action), why place them there at all? Why wouldn't I just run those manually from SQLPlus / iSQLPlus / etc. ?

                        There seems to be some confusion about that script

                         

                        It is a SAMPLE script. It is meant to show you the steps that need to be performed to enable enhanced password verification,.

                         

                        It is NOT meant as a script for a user to just blindly run. That script have sever DIFFERENT sample functions written for DIFFERENT Oracle DB versions. You would only create and implement ONE function for YOUR database version.

                         

                        Even the thread subject 'When does utlpwdmg.sql run?' is not a relevant question.

                         

                        1. The script doesn't 'run' at all.

                        2. You do NOT need a script.

                        3. Oracle does NOT 'look in utlpwdmsg.sql' or even know that it exists.

                        4. YOU are the one that executes DDL to create a password verify function.

                        5. YOU are the one that would keep a copy of YOUR DDL in  a version control system.

                         

                        What you need is to follow the steps illustrated by parts of that script.

                         

                        1. Create YOUR OWN function to perform password verification - use the samples in that scripts as EXAMPLES of what you can do.

                        2. Grant EXECUTE on YOUR function to PUBLIC

                        3. Specify YOUR function name as the PASSWORD_VERIFY_FUNCTION in one or more profiles (as shown in the sample script)

                        ALTER PROFILE DEFAULT LIMIT
                        . . .
                        PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

                        4. If step #3 above is to the DEFAULT profile then ALL users will have Password Management enabled.

                        5. If step #3 above is NOT to the default profile then only users that use the profile you added PASSWORD_VERIFY_FUNCTION statement to will have Password Management enabled.

                        • 10. Re: When does utlpwdmg.sql run?
                          ponderdj

                          Thanks rp0428, and thanks everyone for being so kind and patient to me in answering MY questions despite MY confusion.

                           

                          So, if you'll notice in my last response I don't reference utlpwdmg.sql at all anymore. It's been explained to me that it's just a sample script, and that's all. I'm pretty sure you can customize it and run in to register functions held within, but whatever. I'd like to ask few more questions that haven't been answered, then. I see that the sample function in utlpwdmg.sql begins with "CREATE OR REPLACE function...", so:

                           

                          1. When an already created and assigned password verify function is invoked, where does it run from? (It's obviously not run form within utlpwdmg.sql, as I'd incorrectly thought before) Is the function held in a different file, held in a table somewhere, loaded into a global variable, or held in memory some other way?
                          2. If I inherit a DB from someone else and want to inspect the currently assigned password verify function, how can I obtain it?
                            1. I'm assuming Step 1 will be to inspect dba_profiles.limit WHERE dba_profiles.resource_name LIKE 'password_verify_function'. This will give me the name of the currently enforced function, correct?
                            2. Now that I have the name of the currently enforced function, how can I obtain its contents for inspection?

                           

                          Thanks again,

                          David

                           

                           



                          • 11. Re: When does utlpwdmg.sql run?
                            EdStevens

                            ponderdj wrote:

                             

                            Thanks rp0428, and thanks everyone for being so kind and patient to me in answering MY questions despite MY confusion.

                             

                            So, if you'll notice in my last response I don't reference utlpwdmg.sql at all anymore. It's been explained to me that it's just a sample script, and that's all. I'm pretty sure you can customize it and run in to register functions held within,

                            Since it is an oracle-supplied script, I'd be averse to customizing it.  Instead, I would (and have) create my own.  There is nothing magic about the name 'utlpwdmg.sql'.

                             

                            but whatever. I'd like to ask few more questions that haven't been answered, then. I see that the sample function in utlpwdmg.sql begins with "CREATE OR REPLACE function...", so:

                             

                            1. When an already created and assigned password verify function is invoked, where does it run from? (It's obviously not run form within utlpwdmg.sql, as I'd incorrectly thought before) Is the function held in a different file, held in a table somewhere, loaded into a global variable, or held in memory some other way?

                            It's held the same place any defined function is held.  When the CREATE FUNCTION statement is executed, the function is compiled and the compiled code stored in oracle's internal structures.  When the function is called (in this case, called as part of the code that is changing the password) It is loaded into the shared pool for execution.

                             

                             

                            1. If I inherit a DB from someone else and want to inspect the currently assigned password verify function, how can I obtain it?
                              1. I'm assuming Step 1 will be to inspect dba_profiles.limit WHERE dba_profiles.resource_name LIKE 'password_verify_function'. This will give me the name of the currently enforced function, correct?

                             

                            SQL> select profile, resource_name, limit

                              2  from dba_profiles

                              3  where resource_name='PASSWORD_VERIFY_FUNCTION';

                             

                             

                             

                              1. Now that I have the name of the currently enforced function, how can I obtain its contents for inspection?

                             

                             

                            SELECT DBMS_METADATA.GET_DDL('FUNCTION','MYPWFUNC','SYS') FROM dual;

                             

                            Thanks again,

                            David

                             


                            Another concept you need to keep in mind is that there is nothing inherently specific about these 'password verify functions'.  Notice that in the CREATE FUNCTION statement in the sample, it is simply CREATE FUNCTION.  It is not CREATE PASSWORD VERIFY FUNCTION.  At the point you create the function, it is just a function.  Fundamentally no different that any other function you might create.  Oracle doesn't care what you intend it to be used for.  And the PASSWORD_VERIFY_FUNCTION property of the profile simply names whatever function you have that you wish to be invoked when a password is changed/created.  Ultimately, that function doesn't really have to do anything at all with a password.  Oracle really doesn't care.  Of course, it doesn't make sense for that function to do anything other than verify that a password meets the business rules, but at the level of the database, Oracle really doesn't give a flying fig.


                            Might I suggest you spend some quality time with the Concepts Manual at Contents