code for changing pw [message #55589] |
Mon, 03 February 2003 03:03 |
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 |
|
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>
|
|
|