Re: User Security question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 20 Nov 1998 21:15:27 GMT
Message-ID: <3655dbbc.18744182@192.86.155.100>

A copy of this was sent to "Alexander G Bozhik" <sander_at_isd.anet.donetsk.ua> (if that email address didn't require changing) On Fri, 20 Nov 1998 16:07:38 +0200, you wrote:

>Hi all!
>
>I wanna allow one certain user to change passwords of any user excluding
>SYS and SYSTEM. Is it possible somehow in Oracle8 ?
>
>
> Sincerely yours,
> Alexander G Bozhik
>
>
>

Yes, using a stored procedure you can do this. For example, if SYS created the following procedure:

create or replace procedure change_pass( p_username in varchar2,

                                         p_password in varchar2 )
as
    exec_cursor     integer default dbms_sql.open_cursor;

    rows_processed number default 0;
begin

    if ( upper( p_username ) not in ( 'SYS', 'SYSTEM' ) )     then

        dbms_sql.parse(exec_cursor,
                        'alter user ' || p_username ||
                        ' identified by ' || p_password,
                       dbms_sql.native );
        rows_processed := dbms_sql.execute(exec_cursor);
        dbms_sql.close_cursor( exec_cursor );
        dbms_output.put_line( 'Password for ' || p_username || ' Altered');

    end if;
exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/

Then SYS could grant EXECUTE on the procedure to anyone it wanted to and that person would be able to change anyones password execept for SYS or SYSTEM....  

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 Nov 20 1998 - 15:15:27 CST