DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Change Password in a stored procedure

  1. #1
    Join Date
    Dec 2000
    Posts
    87
    Hi all,

    Is it possible to change a user's password in a procedure?
    If yes, how would the code look like?

    Also, what is the syntax of using 'execute immediate' in a procedure?

    Thanks a lot.

  2. #2
    Join Date
    Feb 2001
    Posts
    66
    CREATE OR REPLACE PROCEDURE alter_user is
    cSQL_statement varchar2(200);

    BEGIN
    cSQL_statement:= 'ALTER USER SCOTT IDENTIFIED BY SCOTT';

    EXECUTE IMMEDIATE cSQL_statement;

    End alter_user;


    The owner of this proc must have ALTER USER rigth granted explicitely, not only granted the dba role;

  3. #3
    Join Date
    Dec 2000
    Posts
    87
    Thanks.
    But what I should I do if I'd like to catch the password as an input parameter and pass to a variable that replace SCOTT.

    Is it like
    cSQL_statement:='Alter user scott identified by new_password';

    will that do? Thanks.

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    CREATE OR REPLACE PROCEDURE alter_user
    (newpassword VARCHAR2)
    IS
    cSQL_statement varchar2(200);

    BEGIN
    cSQL_statement := 'ALTER USER SCOTT IDENTIFIED BY ' || newpassword;

    EXECUTE IMMEDIATE cSQL_statement;

    END alter_user;



  5. #5
    Join Date
    Dec 2000
    Posts
    87
    Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width