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

[skdas]

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..?

[Raminder]

DDL commands are not allowed directly into a stored procedure.
You may like to use scripts instead.

[stecal]

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.

[skdas]

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.

[stecal]

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?

[slimdave]

See if ...

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.

[Raminder]

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.

[slimdave]

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?