Home » RDBMS Server » Server Administration » code for changing pw
code for changing pw [message #55589] Mon, 03 February 2003 03:03 Go to next message
shareef
Messages: 72
Registered: November 2002
Member
Hi

i want to desgn a form to change passwrod by the user in the application, while changing i want to check their validity by their old password, so can any body let me know how can i write a code in pl/sql form for the item.

if possible i want to make a log file for storing the users list those how have changes the password.

any help or comments are appricated.

thanks advance
Re: code for changing pw [message #55594 is a reply to message #55589] Mon, 03 February 2003 07:40 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1. YOU cannot compare the passwords becuase it is hashed.
2. [i]if possible i want to make a log file for storing the users list those how have changes the password.[/i]
   for this you make use of triggers 
3. by default, 
   any regular NON dba user can change his own passowrd.

--
-- this procedure when executed by a dba , can change password for any user.
--

SQL> ED
Wrote file afiedt.buf

  1   CREATE OR REPLACE PROCEDURE CH_PASSWORD ( NAME IN VARCHAR2,
  2                                        PSWD IN VARCHAR2)
  3   AS
  4   BEGIN
  5  EXECUTE IMMEDIATE ('ALTER USER '||NAME ||' IDENTIFIED BY '||PSWD);
  6*  END;
SQL> /

Procedure created.

SQL> SHOW USER
USER is "SYS"
SQL> exec ch_password('MAG','MAG');

PL/SQL procedure successfully completed.

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

--
-- the same script, when compiled in the non DBA users schema 
-- will allow the user to change only his password 
-- NOTE: if SYS grant the exec priv on procedure ( in sys schema) then he can change any password.
--
SQL> CONNECT SCOTT/TIGER;
Connected.
SQL>   CREATE OR REPLACE PROCEDURE CH_PASSWORD ( NAME IN VARCHAR2,
  2                                         PSWD IN VARCHAR2)
  3    AS
  4    BEGIN
  5   EXECUTE IMMEDIATE ('ALTER USER '||NAME ||' IDENTIFIED BY '||PSWD);
  6    END;
  7  /

Procedure created.

SQL> exec ch_password('MAG','MAG');
BEGIN ch_password('MAG','MAG'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.CH_PASSWORD", line 5
ORA-06512: at line 1

SQL> EXEC ch_password('SCOTT','SCOTT');

PL/SQL procedure successfully completed.

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

SQL> ed
--
-- this is a small variation.
-- this procedure all ways changes the password of the CURRENT USER ONLY. even if you grant exec privs to other users, the procedure will be executed (or changes the password Of owner) as owner
Wrote file afiedt.buf

  1  create or replace PROCEDURE CH_PASSWORD ( PSWD IN VARCHAR2)
  2  AS
  3  BEGIN
  4  EXECUTE IMMEDIATE ('ALTER USER '||sys_context('userenv','current_user') ||' IDENTIFIED BY '||PS
  5* END;
SQL> /

Procedure created.

SQL> exec ch_password('something');

PL/SQL procedure successfully completed.

SQL> connect scott/something
Connected.
SQL> 

Previous Topic: how to track change of PWD/Tablespaces
Next Topic: space to run oracle
Goto Forum:
  


Current Time: Fri Sep 20 07:33:27 CDT 2024