How to change Oracle user password through Stored procedure..?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to change Oracle user password through Stored procedure..?

Hybrid View

  1. #1
    Join Date
    Jul 2002
    Posts
    205

    How to change Oracle user password through Stored procedure..?

    I want to change the Oracle user password inside a stored procedure.

    I want to use like this.

    alter user u1 identified by p1;

    This I want inside a stored procedure. Is it possible..?

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    DDL commands are not allowed directly into a stored procedure.
    You may like to use scripts instead.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    Yes, but you introduce a security problem by encoding the password in a file - which is no big deal if you require the user to change password on first login, but may be a big deal if everyone knows what the initial password is.

  4. #4
    Join Date
    Jul 2002
    Posts
    205
    The requirement is like this

    1. The front end application will call a stored procedure.

    2. Inside the stored procedure, I have to change the password.

    How to get it, done. What is the script you have mentioned. Can you please explain a little bit more.

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by Raminder
    DDL commands are not allowed directly into a stored procedure.
    Really? Do you mean not at all, or only if you use something like execute_immediate, dbms_sql, or something along those lines?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    See if ...
    Code:
    create or replace procedure change_password (p_new_password varchar2)
    is
    begin
    execute immediate 'alter user '||user||' identified by '||p_new_password;
    end;
    /
    .. works for you.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Originally posted by stecal:
    quote:
    --------------------------------------------------------------------------------
    Originally posted by Raminder
    DDL commands are not allowed directly into a stored procedure.
    --------------------------------------------------------------------------------



    Really? Do you mean not at all, or only if you use something like execute_immediate, dbms_sql, or something along those lines?
    What I meant was direct DDL sql. You can use dynamic sql, though.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by Raminder
    Originally posted by stecal:

    What I meant was direct DDL sql. You can use dynamic sql, though.
    So why suggest scripts then? Why not suggest dynamic sql?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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