Home » RDBMS Server » Security » Oracle Password Complexity setup (Oracle 11g R2, RHEL 5.9)
Oracle Password Complexity setup [message #670309] Mon, 25 June 2018 23:40 Go to next message
morad_dba
Messages: 93
Registered: June 2008
Member

Dear all,

We have to configure Password Complexity rules that is

" Users shall not be allowed to change their password within one (1) day;"

How to set it in oracle database?

Regards,
Morad Hossen.

Report message to a moderator

Re: Oracle Password Complexity setup [message #670310 is a reply to message #670309] Tue, 26 June 2018 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I don't see the relation between password complexity and your rule.
However this can be done using a password verify function, see https://docs.oracle.com/cd/E11882_01/network.112/e36292/authentication.htm#CACDDFHI.

Report message to a moderator

Re: Oracle Password Complexity setup [message #670349 is a reply to message #670310] Thu, 28 June 2018 07:19 Go to previous messageGo to next message
morad_dba
Messages: 93
Registered: June 2008
Member

Dear All
My intention is to prevent a oracle db user to change his/her password within the same day of setting new password.

How to enforce this type of rule in password profile.

Thanks.

Report message to a moderator

Re: Oracle Password Complexity setup [message #670351 is a reply to message #670349] Thu, 28 June 2018 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Did you check the link I gave you?
What did you try then?

Report message to a moderator

Re: Oracle Password Complexity setup [message #670353 is a reply to message #670351] Thu, 28 June 2018 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Question is - do any of the data dictionary views hold the time the password was last set?
dba_users doesn't.

Report message to a moderator

Re: Oracle Password Complexity setup [message #670354 is a reply to message #670353] Thu, 28 June 2018 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

SYS.USER$.PTIME

[Updated on: Thu, 28 June 2018 08:43]

Report message to a moderator

Re: Oracle Password Complexity setup [message #670357 is a reply to message #670354] Thu, 28 June 2018 09:25 Go to previous messageGo to next message
morad_dba
Messages: 93
Registered: June 2008
Member
Dear All,

I did not get any parameter or mechanism to restrict a db user to change password of the same password reset/create day in the link prescribed by Michel Cadot .

Suppose: SQL> Create user hypo identified by hypo;

and on the same day --- sqlplus hypo/hypo
SQL> ALTER USER hypo identified by hypo1; (This statement should be failed)

Please give us idea how to implement this concept.

[Updated on: Thu, 28 June 2018 09:28]

Report message to a moderator

Re: Oracle Password Complexity setup [message #670359 is a reply to message #670357] Thu, 28 June 2018 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to install a password_verfiy_function and have it throw an error if the password was last changed in the relevant time period.

Report message to a moderator

Re: Oracle Password Complexity setup [message #670361 is a reply to message #670357] Thu, 28 June 2018 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

This is described step by step in the link.
The only thing you have to change is the function default code with your own code as cookiemonster said.

[Updated on: Wed, 04 July 2018 01:32]

Report message to a moderator

Re: Oracle Password Complexity setup [message #670379 is a reply to message #670353] Fri, 29 June 2018 08:40 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
cookiemonster wrote on Thu, 28 June 2018 08:18
Question is - do any of the data dictionary views hold the time the password was last set?
dba_users doesn't.
The db has to keep it somewhere, even if in an undocumented table. How else would it be able to enforce PASSWORD_LIFETIME?

Report message to a moderator

Re: Oracle Password Complexity setup [message #670381 is a reply to message #670379] Fri, 29 June 2018 08:58 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
No it doesn't.
You need one of two dates for password expiry - set date and expiry date.
If you just store set date then expiry date is changed as soon as the lifetime is changed
If you just store expiry then change in lifetime only comes into to play when you reset the password (which is the behaviour in oracle)
dba_users holds expiry_date.
Turns out oracle does hold set date as well but it definitely didn't need to for it's implementation of password lifetime.

Report message to a moderator

Re: Oracle Password Complexity setup [message #670383 is a reply to message #670381] Sat, 30 June 2018 12:24 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
cookiemonster wrote on Fri, 29 June 2018 08:58
No it doesn't.
You need one of two dates for password expiry - set date and expiry date.
If you just store set date then expiry date is changed as soon as the lifetime is changed
If you just store expiry then change in lifetime only comes into to play when you reset the password (which is the behaviour in oracle)
dba_users holds expiry_date.
Turns out oracle does hold set date as well but it definitely didn't need to for it's implementation of password lifetime.
You are correct about PASSWORD_LIFETIME, which is the example I (unfortunately used. But what about PASSWORD_REUSE_TIME It would seem that to enforce that, along with PASSWORD_REUSE_MAX, he has to keep a history of passwords .. actual passwords (their hash value, anyway) AND the dates they were set.

Report message to a moderator

Re: Oracle Password Complexity setup [message #670384 is a reply to message #670383] Sat, 30 June 2018 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

SYS.USER_HISTORY$ but only SYS/SYSDBA can query it.

SYS> desc SYS.USER_HISTORY$
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 USER#                            NOT NULL NUMBER
 PASSWORD                                  VARCHAR2(30)
 PASSWORD_DATE                             DATE

[Updated on: Sat, 30 June 2018 12:33]

Report message to a moderator

Re: Oracle Password Complexity setup [message #670420 is a reply to message #670384] Wed, 04 July 2018 00:47 Go to previous message
sridharmohan
Messages: 1
Registered: July 2018
Junior Member
very useful info shared.

Report message to a moderator

Previous Topic: How to setup Password rotation
Next Topic: execute on dbms_lock
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 02 16:25:18 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.