Loading
View RSS Feed

Harsh Chowdhary's Blog

How To Change Oracle User Passwords With PL/SQL Procedures (in 11g)?

Rating: 3 votes, 3.67 average.
What do you do when you want to change the password of a Database user? The answers is "Alter User" statement. But in some cases it is important, that users can change their password interactively like changing your password in Windows or UNIX. A small procedure can be written for the purpose.

Code:
--you must connect as sysdba like connect "/ as sysdba"

CREATE OR REPLACE PROCEDURE change_pass(old_pass IN varchar2,
                                        new_pass IN varchar2,
                                        ver_pass IN varchar2) IS
      cur_user        varchar2(30);
      pass_value      varchar2(20);
      pass_value_new  varchar2(20);
      stmt            varchar2(200);
BEGIN
      -- verification of password
      if new_pass = ver_pass then
         -- get active user
         select user into cur_user from dual;

         -- get encrypted password from active user
         select password into pass_value
         from sys.user$
         where name=cur_user;

         -- check password
         stmt := 'alter user "'||cur_user||'" identified by "'||old_pass||'"';
         EXECUTE IMMEDIATE stmt;

         -- get encrypted 'new' password
         select password into pass_value_new
         from sys.user$
         where name=cur_user;

        -- change password back to what it was
        stmt := 'alter user "'||cur_user|| '" identified by values '||chr(39)||pass_value||chr(39);

        EXECUTE IMMEDIATE stmt;

        -- check if  given password same as input password
        if pass_value=pass_value_new  then
           stmt := 'alter user "'||cur_user||'" identified by "'||new_pass||'"';
           EXECUTE IMMEDIATE stmt;

           dbms_output.put_line('Changed Password');
        else
           dbms_output.put_line('Old Password incorrect');
        end if;
      else
        dbms_output.put_line('Verify new password');
     end if;
end change_pass;
/

show err
Explanation: A sys table user$ contains user password with other information of a database user. The procedure change_pass takes three parameters old password of a user, New password of the user and verification of new password. First it is checked whether New password of the user and verification of new password are same. If same, then first active user is checked. Then, the encrypted password of the user is retrieved from the user$ table. The user entered old password is set for the user and retrieved again in encrypted form. If both of the retrieved encrypted strings are same then the old password is correct and new password is set.

Grant execute on the procedure to all users.

Code:
grant execute on change_pass to public;
Example Output:
~~~~~~~~~~~~~~~

with user Scott:

$sqlplus scott/tiger;

Code:
  SQL> set serveroutput on;
  SQL> exec change_pass('TIGER','TIGER2','TIGER2');
       Changed Password
Code:
  SQL> exec change_pass('TIGER2','TIGER','TIGER');

       Changed Password
Code:
  SQL> exec change_pass('TIGER','TIGER2','TIGER');

       Verify new password
Code:
  SQL> exec change_pass('TIGER2','TIGER','TIGER');

       Old Password incorrect

Hence, This procedure allows you to change your password interactively.

Submit "How To Change Oracle User Passwords With PL/SQL Procedures (in 11g)?" to Digg Submit "How To Change Oracle User Passwords With PL/SQL Procedures (in 11g)?" to del.icio.us Submit "How To Change Oracle User Passwords With PL/SQL Procedures (in 11g)?" to StumbleUpon Submit "How To Change Oracle User Passwords With PL/SQL Procedures (in 11g)?" to Google

Categories
Oracle

Comments




Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.