Home » RDBMS Server » Security » Is is possible to restrict user's password reset by SELF
Is is possible to restrict user's password reset by SELF [message #605701] Mon, 13 January 2014 13:32 Go to next message
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

SQL> alter user user1 password expire;

User altered.


--Below action must be restricted

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



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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.


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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
ernisarg
Messages: 1
Registered: January 2011
Location: Fort Wayne
Junior Member
You can set
password_grace_time to 0
password_life_time to 90
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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. "


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 Go to previous message
John Watson
Messages: 8922
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:
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:
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>


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


Current Time: Fri Mar 29 10:37:28 CDT 2024