Confusion about Oracle Profile Parameters
I spend a lot of time with the right configuration of profiles and certainly I consulted the oracle documentation for that. Because not all of the parameters are self-explaining. PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are such candidates, maybe you can guess what they mean by yourself but in the end you need the documentation to understand their real behaviour. So I did. After a few minutes I choose an appropriate strategy for my case by the help of the Oracle Documentation. But when I started to configure these parameters over OEM I got an inconsistent error message.
The documentation suggests that you have to use these parameters in conjunction. PASSWORD_REUSE_TIME specifies a number of days and PASSWORD_REUSE_MAX speciefies a number of password changes. You must set an integer for both parameters if you want to have any affext. If you set an Integer for one and UNLIMITED the other, the can never reuse a password.
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX
These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.
- If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the password the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.
- If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password. (Oracle Documentation)
In my sample PASSWORD_REUSE_TIME should be set to 1 and PASSWORD_REUSE_MAX to 10. What means that the User can reuse the password after one day provided that he has changed the password meanwhile 10 times. For the „normal“ User unlikely, for the audit sufficient and for the familiar administrator a good possibility to switch back to the initial password in case. The documentation provides a simple understandable example here:
For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times. (Oracle Documentation)
Convicted that my choice is good I navigated to the Oracle Enterprise Manager Cloud Control 12c typed my parameters and got the following error message: If the number of passwords to keep is set to an integer valuem the number of days to keep them must be set to UNLIMITED.
(Screenshot: Oracle Enterprise Manager Cloud Control 12c – Database > Security > Profile)
But that goes against Oracle’s own documentation … ? What shall I believe? Maybe a reader of the Blog have an advice?
When I do the same the old-fashined way, directly per SQL-Plus I can set the parameters as expected without any error message. But a bad feeling remains.
Oracle Documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6010.htm
UPDATE: While browsing in “My Oracle Support” I found out that this problem exists for a very … veeeery … veeery looong time, so the bug report was filed in October 2004 … two thousand and four! Apperantly they want to save something into modern times 😉 Doc ID 1937232.1
i found this with oracle 11.2.0.1 for linux , i didn’t know this is an old bug.
thx for sharing
Simple Explanation –
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX
These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.
Source – Oracle Docs.[c[color=black][/color]olor=olive][/color]
Why do repeat the Oracle Doc’s which is already in the text? The problem is OEM related. You should read the whole text. Thank you.
Thanks. I thought I going crazy.
If you want to have the effect of setting a limit on the number of reused passwords but not on how long ago the old one was reused, you can set password_reuse_time to as close to 0 as possible. I find that the smallest is 1/172800, regardless Oracle version. That’s half a second. Obviously, if you or a program wants to cycle through 4 passwords in 0.5 seconds, the restriction imposed by password_reuse_time still takes effect.
If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to UNLIMITED, then Oracle uses neither of these password resources.
* If PASSWORD_REUSE_MAX is set to an integer value, PASSWORD_REUSE_TIME must be set to UNLIMITED.
Hi Johannes,
Thanks for your blog on PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX which is very informative but looks like they don’t check case of password character:
e.g. PASSWORD_REUSE_MAX 4 , PASSWORD_REUSE_TIME 360 and have password verification function to check upper and lower case in password.
SQL> ALTER USER power_user IDENTIFIED BY Hoppyhaliday$2;
User altered.
SQL> ALTER USER power_user IDENTIFIED BY hoppyhaliday$2;
ALTER USER power_user IDENTIFIED BY hoppyhaliday$2
*
ERROR at line 1:
ORA-28007: the password cannot be reused
In above password first char is in lower case which makes both password different so in this case, error should be “Password must contain at least one uppercase letter” instead of “the password cannot be reused”. It shows these parameters do not check upper or lower case in password.