| 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
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 #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
|
|
|
|
|
|
| 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
|
|
|
|
|
|
|
|
| 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: 8964
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
