Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: User Security question
Hi,
thank you very much for your advice. Probably I'll take
advantage of your procedure. But I guess there is one problem in
this manner. It would be desirable that this certain user (security
officer, i'd say) could use such powerful and comfortable tool as
Oracle Enterprise Manager to administer all users with the exception
of DBA. It seems very likely to me that such problem is rather typical.
Sincerely yours,
Alexander G Bozhik
Thomas Kyte wrote in message <3655dbbc.18744182_at_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 Tue Nov 24 1998 - 02:15:39 CST