Home » RDBMS Server » Security » User Password change (10.2.0.4.0 ,windows)
User Password change [message #437611] Tue, 05 January 2010 03:57 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Hi all,

Can somebody tell me how frequently user's password can be changed in database?

I mean is there any limitation that user cannot change his password
after certain times/periods?



Regards,
Re: User Password change [message #437613 is a reply to message #437611] Tue, 05 January 2010 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Quote:
how frequently user's password can be changed in database?

Every second if he wants.

2/ Quote:
is there any limitation that user cannot change his password after certain times/periods?

If you mean cannot change it before a certain time, no. (Nevertheless you can use a database trigger to enforce this.)
If you mean must change if after a certain time, yes, use profile.

Regards
Michel
Re: User Password change [message #437615 is a reply to message #437613] Tue, 05 January 2010 04:18 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
"If you mean must change if after a certain time, yes, use profile."


I want to know how many times a user password can be changed and how to check it from database?


Regards,

Re: User Password change [message #437616 is a reply to message #437615] Tue, 05 January 2010 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A user can change his/her password when he wants.
If you want to know how many times then you have to activate audit or write a database trigger.

Regards
Michel
Re: User Password change [message #437617 is a reply to message #437616] Tue, 05 January 2010 04:31 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Just to add some more with Michel`s post.

SQL> conn scott / as sysdba
Enter password:
Connected.
SQL> select * from sys.user_history$;

no rows selected

SQL> select * from user_history$;

no rows selected

SQL> alter user scott identified by tejajun20;

User altered.

SQL> select * from user_history$;

no rows selected

SQL> CREATE PROFILE my_profile LIMIT
  2    FAILED_LOGIN_ATTEMPTS 3  -- Account locked after 3 failed logins.
  3    PASSWORD_LOCK_TIME 5     -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
  4    PASSWORD_LIFE_TIME 30    -- Password expires after 90 days.
  5    PASSWORD_GRACE_TIME 3    -- Grace period for password expiration.
  6    PASSWORD_REUSE_TIME 120  -- Number of days until a specific password can be reused. UNLIMITED means never.
  7    PASSWORD_REUSE_MAX 10    -- The number of changes required before a password can be reused. UNLIMITED means never.
  8  /

Profile created.

SQL> ALTER USER scott PROFILE my_profile;

User altered.

SQL> select * from sys.user_history$;

no rows selected

SQL> alter user scott identified by tejajun20;

User altered.

SQL> select * from user_history$;

     USER# PASSWORD                       PASSWORD_
---------- ------------------------------ ---------
        54 624AA21960E83872               05-JAN-10

SQL> alter user scott identified by TEJAJUN20;
alter user scott identified by TEJAJUN20
*
ERROR at line 1:
ORA-28007: the password cannot be reused


SQL> alter user scott identified by tiger;

User altered.

SQL> select * from user_history$;

     USER# PASSWORD                       PASSWORD_
---------- ------------------------------ ---------
        54 624AA21960E83872               05-JAN-10
        54 F894844C34402B67               05-JAN-10

SQL>
Re: User Password change [message #437645 is a reply to message #437617] Tue, 05 January 2010 06:10 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
What i understood from your answers is that we can change our password as many as we can .

But how to find out if triggers/profiles are already created for this password change ?


Regards,
Re: User Password change [message #437646 is a reply to message #437645] Tue, 05 January 2010 06:14 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> desc dba_users
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

SQL> desc dba_profiles
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE                                   NOT NULL VARCHAR2(30)
 RESOURCE_NAME                             NOT NULL VARCHAR2(32)
 RESOURCE_TYPE                                      VARCHAR2(8)
 LIMIT                                              VARCHAR2(40)

SQL>


sriram Smile
Re: User Password change [message #437648 is a reply to message #437646] Tue, 05 January 2010 06:21 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Thank you .well understood.I dont see parameter under my profile which would
end using my password permanently.
Re: User Password change [message #437650 is a reply to message #437648] Tue, 05 January 2010 06:28 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i didn't get you...By
Quote:
I dont see parameter under my profile which would
end using my password permanently.


sriram Smile
Re: User Password change [message #437652 is a reply to message #437648] Tue, 05 January 2010 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I dont see parameter under my profile which would
end using my password permanently.

If you set PASSWORD_LIFE_TIME to UNLIMITED then your password will be always valid.
But you still can change the password any time you want.

Regards
Michel
Re: User Password change [message #437659 is a reply to message #437652] Tue, 05 January 2010 07:19 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Thank you....
Re: User Password change [message #437905 is a reply to message #437611] Wed, 06 January 2010 08:44 Go to previous messageGo to next message
samg4ug
Messages: 33
Registered: July 2007
Location: India
Member
Hi,

Alternative way is to set profile to DEFAULT. It will also do the same.

But remember other parameters will be set to default value also.

alter user <user_name> profile default;

Regards,
Sam G
Re: User Password change [message #437916 is a reply to message #437905] Wed, 06 January 2010 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Until Oracle changes the attributes of DEFAULT profile.
But wait, it did in 11gR2.

Never rely on default options.

Regards
Michel
Re: User Password change [message #448054 is a reply to message #437617] Fri, 19 March 2010 04:40 Go to previous messageGo to next message
sarajohnson
Messages: 1
Registered: March 2010
Junior Member
thanks for the code
ramoradba
Re: User Password change [message #448056 is a reply to message #448054] Fri, 19 March 2010 04:44 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
You can find more code/scripts at Oracle documents...
So first read Orafaq Forum Guide and Oracle documents.

sriram Smile
Previous Topic: VPD fine grain access requirement
Next Topic: Changing the LDAP Password
Goto Forum:
  


Current Time: Fri Mar 29 10:11:05 CDT 2024