| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Please help - stored procedure
As the owner sysadm execute the alter user in a procedure, ha must have the 'alter user' privilege by his own and not via a role.
v734> create or replace procedure ExecuteSQL (codeSql varchar2) is
2 cid integer;
3 dummy integer;
4 begin
5 cid := dbms_sql.open_cursor;
6 dbms_sql.parse (cid, codeSql, dbms_sql.native);
7 dummy := dbms_sql.execute (cid);
8 dbms_sql.close_cursor (cid);
9 exception
10 when others then
11 dbms_output.put_line('ExecuteSql : '||codeSql);
12 dbms_output.put_line(dbms_utility.format_error_stack);
13 if dbms_sql.is_open (cid) then
14 dbms_sql.close_cursor (cid);
15 end if;
16 raise;
Procedure created.
v734> create or replace procedure change_password (new_psw varchar2) is
2 username varchar2(30);
3 begin
4 select user into username from dual;
5 executeSQL ('alter user '||username||' identified by '||new_psw);
6 end;
Procedure created.
v734> drop public synonym change_password;
Synonym dropped.
v734> create public synonym change_password for change_password ;
Synonym created.
v734> grant execute on change_password to public;
Grant succeeded.
v734> grant alter user to mma$ep34087; <=== that's the current user
Grant succeeded.
v734> drop user foo;
User dropped.
v734> create user foo identified by foo;
User created.
v734> grant create session to foo;
Grant succeeded.
v734> connect foo/foo_at_refold
Connected.
v734> set serveroutput on size 100000
v734> exec change_password ('new_psw');
PL/SQL procedure successfully completed.
v734> connect foo/new_psw_at_refold;
Connected.
-- Regards Michel CGS <tcgs_at_hotmail.com> a écrit dans le message : tJjN4.1153$Ip.36917_at_cac1.rdr.news.psi.ca...Received on Wed Apr 26 2000 - 00:00:00 CDT
> 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
> > >
> > >
> > >
> > >
> >
> >
>
>
 |
 |