Re: Please help - stored procedure

The owner of the procedure must be granted an additional privilege that will allow the procedure to change the password of any user... the privilege is named something like ALTER ANY USER (or it may actually be the CREATE USER privilege that the owner of the procedure needs...)

"CGS" <tcgs_at_hotmail.com> wrote in message news:tJjN4.1153$Ip.36917_at_cac1.rdr.news.psi.ca...
> Hi Michel,
>
> Thanks for the prompt response with sample code.
>
> We are using Oracle 8.0.5 and guess I can not use "execute
 immediate".
> I tried the second method using DBMS package. It works if the
 'owner'
> of the stored proc runs its. If a different user tries, I get
 the following
> message.
>
> SQL> execute sysadm.change_password('winter')
>
> begin sysadm.change_password('winter'); end;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYSADM.EXECUTESQL", line 15
> ORA-06512: at "SYSADM.CHANGE_PASSWORD", line 5
> ORA-06512: at line 1
>
> Please note that I have given 'execute' permission on
> executeSQL, change_password, DBMS_sql, DBMS_utility,
 DBMS_output to the user
> (and public)
> who is trying to change the password. Again, the idea is to
 keep this stored
> proc in one of the
> repository schema and then give permission to others to use
 it. What else
> is required?
>
> Any help is appreciated.
>
> CGS
> tcgs_at_hotmail.com
>
>
> Michel Cadot <micadot_at_netcourrier.com> wrote in message
> news:8e499j$275b$1_at_news4.isdnet.net...
> > You can't do that in a procedure, you have to use
> > dynamic SQL. That either 'execute immediate'
> > statement (if you are in Oracle 8i) or dbms_sql
> > package.
> >
> > For example:
> > create procedure change_password (new_psw varchar2) is
> > username varchar2(30;
> > begin
> > select user into username from dual;
> > execute immediate 'alter user '||username||' identified
 by '||new_psw;
> > end;
> > /
> >
> > or with Oracle7, you create a procedure executeSQL:
> > create or replace procedure ExecuteSQL (codeSql varchar2) is
> > cid integer;
> > dummy integer;
> > begin
> > cid := dbms_sql.open_cursor;
> > dbms_sql.parse (cid, codeSql, dbms_sql.native);
> > dummy := dbms_sql.execute (cid);
> > dbms_sql.close_cursor (cid);
> > exception
> > when others then
> > dbms_output.put_line(dbms_utility.format_error_stack);
> > if dbms_sql.is_open (cid) then
> > dbms_sql.close_cursor (cid);
> > end if;
> > raise;
> > end ExecuteSQL;
> > /
> >
> > and then:
> >
> > create procedure change_password (new_psw varchar2) is
> > username varchar2(30;
> > begin
> > select user into username from dual;
> > executeSQL ('alter user '||username||' identified by
 '||new_psw);
> > end;
> > /
> >
> > --
> > Have a nice day
> > Michel
> >
> >
> > CGS <tcgs_at_hotmail.com> a écrit dans le message :
 _shN4.1147$Ip.36081_at_cac1.rdr.news.psi.ca...
> > > Hi Experts,
> > >
> > > I am new to Oracle & trying to create a following stored
 procedure.
> > >
> > > create procedure change_password (userid in varchar2,
 passwd in
 varchar2)
> > > as begin
> > > alter user userid identified by passwd;
> > > end;
> > >
> > > The idea is to create a stored proc and allow users to
 change their
 password
> > > from a
> > > front-end application. I get the following message when I
 try to save
 the
> > > procedure.
> > >
> > > PLS-00103: Encountered the symbol "ALTER" when expecting
 one of the
> > > following:
> > > begin declare exit for goto if loop mod null pragma raise
> > > return select update while <an identifier>
> > > <a double-quoted delimited-identifier> <a bind variable>
 <<
> > > close current delete fetch lock insert open rollback
> > > savepoint set sql commit <a single-quoted SQL string>
> > >
> > > Can anyone tell me what is wrong with this code? If there
 is a better
 way to
> > > do it, please show me
> > >
> > > Thanks
> > >
> > > CGS
> > > tcgs_at_hotmail.com
> > >
> > >
> > >
> > >
> >
> >
>
>
>
Received on Fri Apr 28 2000 - 00:00:00 CDT