Re: resetting user passwords

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 08 May 1998 16:56:48 GMT
Message-ID: <35543895.14418092@192.86.155.100>

A copy of this was sent to Rob Joss <_rob_joss_at_lilly.com> (if that email address didn't require changing) On Fri, 08 May 1998 10:25:11 -0500, you wrote:

>Is there a pl/sql package that will execute the statement
>'ALTER user userid identified by newpass;' .
>
>I would like to create a stored procedure that can change the password
>for
>selected user ids and not other user ids and then grant execute on this
>procedure
>to users on our help desk. This procedure is used to reset forgotten
>passwords.
>
>I have tried dbms_sql and it does not seem to work.
>(parsing and then executing.)
>
>Thanks for any help that can be provided.

dbms_sql does work. You probably did not grant the ALTER USER privelege to the owner of the procedure using dbms_sql. Roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence dbms_sql won't be able to do it either.

Here is an example showing that it does work with the correct priveleges in place:

SQL> create or replace procedure exec( stmt in varchar2 )   2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6      dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
  7      rows_processed := dbms_sql.execute(exec_cursor);
  8      dbms_sql.close_cursor( exec_cursor );
  9  exception
 10      when others then
 11        if dbms_sql.is_open(exec_cursor) then
 12          dbms_sql.close_cursor(exec_cursor);
 13        end if;
 14        raise;

 15 end;
 16 /

Procedure created.

SQL> exec exec( 'alter user scott identified by lion' ); begin exec( 'alter user scott identified by lion' ); end;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "TKYTE.EXEC", line 14
ORA-06512: at line 1

SQL> grant alter user to tkyte;

Grant succeeded.

SQL> exec exec( 'alter user scott identified by lion' );

PL/SQL procedure successfully completed.

SQL> connect scott/lion;
Connected.

SQL> connect tkyte/tkyte
Connected.
SQL> exec exec( 'alter user scott identified by tiger' );

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
Connected.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 08 1998 - 11:56:48 CDT