Home » RDBMS Server » Server Administration » Password Management
Password Management [message #58047] Mon, 28 July 2003 11:37 Go to next message
C. Amarnath
Messages: 7
Registered: July 2003
Junior Member
HI, We are interested to know how to restrict a user to change his password?
Re: Password Management [message #58050 is a reply to message #58047] Mon, 28 July 2003 17:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10696
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You should make use of PASSWORD_VERIFY_FUNCTION.
login as sys.
create a profile with limit to custom function that uses a PASSWORD_VERIFY_FUNCTION.
assign this profile to the user.
once you assign this,
  the user cannot change the password by alter user
  or
  by executing sqlplus command password.
to break the restriction
you should login as sys and revoke the profile from user.

[Updated on: Thu, 09 March 2006 06:09]

Report message to a moderator

Re: Password Management [message #58064 is a reply to message #58050] Tue, 29 July 2003 06:43 Go to previous messageGo to next message
C. Amarnath
Messages: 7
Registered: July 2003
Junior Member
Hi,
Thanks for the response.
I have doubt in this workaround.
Password_verify_function is to be used for verify complexity of new passwords, but pls le me know how to use this function to restrict any user to change his own password.
Thanks in advance,
Amarnath
Re: Password Management [message #58066 is a reply to message #58064] Tue, 29 July 2003 07:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10696
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
sys@mutation_mutation > show user
USER is "SYS"

sys@mutation_mutation > @restrict_password

Function created.

Profile created.

sys@mutation_mutation > create user sample identified by sample;

User created.

sys@mutation_mutation > alter user sample profile restrict_user;

User altered.

sys@mutation_mutation > grant connect,resource to sample;

Grant succeeded.

sys@mutation_mutation > connect sample/sample
Connected.
sys@mutation_mutation > show user
USER is "SAMPLE"
sys@mutation_mutation > password
Changing password for SAMPLE
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20001: Restricted operation. Contact the Enterprise Services

Password unchanged

----------------------------------------------------------------------

--
-- this is code
--
sys@mutation_mutation > get restrict_password
  1  CREATE OR REPLACE FUNCTION disable_alter_password  (username VARCHAR2,
  2         password VARCHAR2, old_password VARCHAR2) RETURN boolean IS
  3  BEGIN
  4       raise_application_error(-20001, 'Restricted operation. Contact the Enterprise Services') ;
  5  END ;
  6  /
  7* CREATE  PROFILE restrict_user  LIMIT PASSWORD_VERIFY_FUNCTION disable_alter_password;
sys@mutation_mutation >

Re: Password Management [message #58067 is a reply to message #58066] Tue, 29 July 2003 07:40 Go to previous messageGo to next message
C. Amarnath
Messages: 7
Registered: July 2003
Junior Member
Great Help Mahesh,
Thanks a lot....
Regards,
Amarnath
Re: Password Management [message #58068 is a reply to message #58066] Tue, 29 July 2003 08:58 Go to previous messageGo to next message
C. Amarnath
Messages: 7
Registered: July 2003
Junior Member
Mahesh,
I have followed all the steps and now noticed that a strange thing happened here.
Even when i login as user with dba privs also, i am unable to change the password for this user.
Pls help me now.
Amarnath
Re: Password Management [message #58069 is a reply to message #58068] Tue, 29 July 2003 09:07 Go to previous messageGo to next message
C. Amarnath
Messages: 7
Registered: July 2003
Junior Member
Mahesh,
I have followed all the steps and now noticed that a strange thing happened here.
Even when i login as user with dba privs also, i am unable to change the password for this user.
Pls help me now.
Amarnath
Mahesh, very urgent pls...Password Management [message #58070 is a reply to message #58069] Tue, 29 July 2003 09:09 Go to previous messageGo to next message
C. Amarnath
Messages: 7
Registered: July 2003
Junior Member
Mahesh,
I have followed all the steps and now noticed that a strange thing happened here.
Even when i login as user with dba privs also, i am unable to change the password for this user.
Pls help me now.
I have to change the pwd as DBA user.
Amarnath
Re: Mahesh, very urgent pls...Password Management [message #58071 is a reply to message #58070] Tue, 29 July 2003 09:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10696
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you have to create the profile and do all these as SYS only.
NOT AS ANY OTHER USER.
I have specified this in the first posting itself.
to break the restriction,
login as sys,
revoke the profile from the user.
change the password.
grant the profile back to the user( now the password is locked again).

Re: Mahesh, very urgent pls...Password Management [message #58072 is a reply to message #58070] Tue, 29 July 2003 09:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10696
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
and if you reply-post to yourself, I wont get notified.
and I never keep track of postings unless i am notified.
Thanks Mahesh [message #58073 is a reply to message #58071] Tue, 29 July 2003 09:36 Go to previous message
C. Amarnath
Messages: 7
Registered: July 2003
Junior Member
Now, am done. Thanks a lot...
Previous Topic: archive/redologfile
Next Topic: Clone Database with a Twist
Goto Forum:
  


Current Time: Tue Jan 25 08:47:41 CST 2022