Very Computer

The following procedure, which changes a user password,
gives an ORA-01031 (insufficient privileges) error. This
even though the user executing the procedure has the
ALTER USER privilege granted directly (not via a role).

Any help as to the cause of  this error would be much
appreciated.

Thanks in advance.

Kailash.

----------------------------------------------------------------------------
-------------------------------------------
CREATE OR REPLACE PROCEDURE
NGE_PASSWORD(
     P_USER_ID   IN VARCHAR2,
     P_PASSWORD   IN varchar2)

IS

CURSOR_PASS INTEGER;
CURSOR_INT INTEGER;
v_string varchar2(100);

BEGIN
        CURSOR_PASS := dbms_sql.open_cursor;
        v_string := 'alter user ' || p_user_id || ' identified by ' || p_password;
       dbms_sql.parse(cursor_PASS,v_string,dbms_sql.v7);
       CURSOR_INT := dbms_sql.execute(CURSOR_PASS);
       dbms_sql.close_cursor(cursor_PASS);
END;

------------------------------------------------------------------------------------------------------------------------------------

The following procedure, which changes a user password,
gives an ORA-01031 (insufficient privileges) error. This
even though the user executing the procedure has the
ALTER USER privilege granted directly (not via a role).

Any help as to the cause of  this error would be much
appreciated.

Thanks in advance.

Kailash.

----------------------------------------------------------------------------
-------------------------------------------
CREATE OR REPLACE PROCEDURE
NGE_PASSWORD(
     P_USER_ID   IN VARCHAR2,
     P_PASSWORD   IN varchar2)

IS

CURSOR_PASS INTEGER;
CURSOR_INT INTEGER;
v_string varchar2(100);

BEGIN
        CURSOR_PASS := dbms_sql.open_cursor;
        v_string := 'alter user ' || p_user_id || ' identified by ' || p_password;
       dbms_sql.parse(cursor_PASS,v_string,dbms_sql.v7);
       CURSOR_INT := dbms_sql.execute(CURSOR_PASS);
       dbms_sql.close_cursor(cursor_PASS);
END;

------------------------------------------------------------------------------------------------------------------------------------

Did you GRANT EXECUTE on the procedure to the user who needs to run it?

/*
Changing Passwords for Oracle Users
*/
/*
 The stored procedure script below allows you to change/set password for Oracle
users.

 Features of this script:

 This stored procedure can be called from various client environments like
 PowerBuilder, Visual Basic, Microsoft Excel, Oracle Forms, Oracle Reports or
SQL*Plus.

 It makes use of the DBMS_SQL package.
 The stored procedure needs to be owned by the SYS account
 but can be accessed by all users using a public synonym.

 It could be particularly useful when users accessing the instance are on
 different client environments and don't necessarily have access to SQL*Plus.
*/

REM ------------------------------------------------------------
REM This stored procedure sets/changes password for Oracle users
REM Accepts username and new password.
REM Compares the username with the 'signed-on' username.
REM Usage
REM For changing the password for USER user1 to password1,
REM       chng_pwd(user1,password1)
REM This script needs to be run from SYS account
REM ------------------------------------------------------------

create or replace procedure chng_pwd(uname varchar2, new_pwd varchar2)
as
  cursor1    integer;
  rows_processed integer;
begin
  if UPPER(user) = UPPER(uname) then
    cursor1 := dbms_sql.open_cursor;
    dbms_sql.parse(cursor1,'alter user '||uname||
    ' identified by '||new_pwd,dbms_sql.v7);
    rows_processed := dbms_sql.execute(cursor1);
    dbms_sql.close_cursor(cursor1);
  end if;
exception
  when others then
   dbms_sql.close_cursor(cursor1);
end chng_pwd;
/
create public synonym chng_pwd for sys.chng_pwd;
grant execute on chng_pwd to public;
exit;

Paul in VT

Yes.

I found out, that Oracle 7.3.4 has problems with rights granted over a role
to a user.
System priviliges (like 'create table') have to be granted directly to the
user,
who wants to  use it within a procedure.

Try 'grant alter user to <user>' before running the dbms_sql-command within
a procedure

Regards, Stephan
--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin

---------------------------------------------------------------

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !