OraFAQ Forum: Security » Is is possible to restrict user's password reset by SELF

Home » RDBMS Server » Security » Is is possible to restrict user's password reset by SELF

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is is possible to restrict user's password reset by SELF [message #605701]

Mon, 13 January 2014 13:32

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Hi Team,

Please confirm whether we have any alternate to restrict the password reset by SELF.
for e.g. in below case, the db user USER1 is resetting his password. I need to restrict this action. Whenever end user ( DB user ) needs to reset this password ( say, password is expired as per his profile setting ), then he should not be able to change his password.

Once password expired, then it will prompt new password when user tries to connect thro sqlplus. Can it be restricted ?

---making the password expire by force as SYS user

Code: [Select all] [Show/ hide]

SQL> alter user user1 password expire;

User altered.

--Below action must be restricted

Code: [Select all] [Show/ hide]

C:\Users\testuser>sqlplus user1/mypwd123#@testdb

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 13 14:30:19 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-28001: the password has expired


Changing password for user1
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show user
USER is "USER1"
SQL>

-----case2: below also must be restricted

Code: [Select all] [Show/ hide]

C:\Users\testuser>sqlplus user1/pwdjan13#@testdb

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 13 14:21:53 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show user
USER is "USER1"
SQL>
SQL>
SQL>
SQL> alter user user1 identified by mypwd123#;

User altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

C:\Users\testuser>sqlplus user1/mypwd123#@testdb

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 13 14:22:18 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show user
USER is "USER1"
SQL>
SQL>

Please confirm.

Thank you
Kesavan

Report message to a moderator

Re: Is is possible to restrict user's password reset by SELF [message #605704 is a reply to message #605701]

Mon, 13 January 2014 13:37

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you don't want the user connects then lock the account.
Otherwise why don't you want a user change his password? It is the opposite, a user MUST change his password from time to time.

[Updated on: Mon, 13 January 2014 13:50]

Report message to a moderator

Re: Is is possible to restrict user's password reset by SELF [message #605706 is a reply to message #605704]

Mon, 13 January 2014 13:45

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Hi Michel,

My project requirement is to set PASSWORD_LIFE_TIME = 90. Once password expired after 90 days, then DBA needs to reset the new password for that user account. As you said, let me set up PASSWORD_LIFE_TIME = UNLIMITED, so that once DBA only can UNLOCK this user account.

But I need to lock this user account after 90 days. Can we achieve this ?

Please guide me

Thank you
kesavan

Report message to a moderator

Re: Is is possible to restrict user's password reset by SELF [message #605707 is a reply to message #605706]

Mon, 13 January 2014 13:52

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you want to lock the account after some days (why?) then you have to create a job.
But once again, why don't you let the user change his password himself?
Why the DBA has to change the password and send it to the user?
Why the DBA needs to know the user password? It is a bad policy, the DBA has not to know the user password.

Report message to a moderator

Re: Is is possible to restrict user's password reset by SELF [message #605708 is a reply to message #605707]

Mon, 13 January 2014 14:01

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Yes Michel, you are absolutely correct.

But I am working on Amazon Oracle RDS instance. User management requirement expects that the TEAM LEADER ( TL ) or APPLICATION OWNER user is responsible and he needs to take care of his team member's password reset.

I should think about job to perform auto LOCK once password is expired.

Thank you very much
kesavan

Report message to a moderator

Re: Is is possible to restrict user's password reset by SELF [message #606990 is a reply to message #605708]

Fri, 31 January 2014 14:04

ernisarg
Messages: 1
Registered: January 2011
Location: Fort Wayne

Junior Member

You can set
password_grace_time to 0
password_life_time to 90

Report message to a moderator

Re: Is is possible to restrict user's password reset by SELF [message #606991 is a reply to message #606990]

Fri, 31 January 2014 14:14

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This does not prevent the user from changing his password.
I remind the question:
"the db user USER1 is resetting his password. I need to restrict this action."
"Whenever end user ( DB user ) needs to reset this password ( say, password is expired as per his profile setting ), then he should not be able to change his password. "

Report message to a moderator

Re: Is is possible to restrict user's password reset by SELF [message #606992 is a reply to message #605701]

Fri, 31 January 2014 14:37

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

The question was two weeks ago, so I don't suppose it matters any more, but using this as a password verify function works:

Code: [Select all] [Show/ hide]

CREATE OR replace FUNCTION Block_self_change (username     VARCHAR2,
                                              password     VARCHAR2,
                                              old_password VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
    IF username = USER() THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END;

/

see here,after creating and assigning the profile:

Code: [Select all] [Show/ hide]

orclz>
orclz> conn jw/jw
Connected.
orclz> password
Changing password for JW
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed


Password unchanged
orclz>
orclz> conn / as sysdba
Connected.
orclz> alter user jw identified by jw;

User altered.

orclz>

Report message to a moderator

Previous Topic:	How to clean up older records in AUD$ table
Next Topic:	NOAUDIT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 03:27:33 CDT 2025