User Password change privileges?

Hello

Can anybody please explain what's going on here:

I want to allow the user to change his password from the client program.

Unfortunately the users have been created using spaces in their user names. This means that in order to change the password the SQL that the user runs must be in the format:

ALTER USER "USER NAME" IDENTIFIED BY password;

where the user name must be in upper case.

When executed in SQL*Plus this works fine. This format unfortunately does not work over ODBC - I think the number of double quotes is causing confusion - so I can't execute a dynamically constructed SQL statement from the client program.

So I wrote a stored procedure to execute the statement using dynamic PL/SQL. Unfortunately when the user executes it I get:

SQL> exec :r := fnchange_password(:p)
BEGIN :r := fnchange_password(:p); END;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "ECOADMIN.FNCHANGE_PASSWORD", line 29
ORA-06512: at line 1

SQL> where the offending lines in the function read:

/* Get the current user's user name... */
SELECT USER
INTO v_username
FROM sys.dual;

/* and make it compatible with the ALTER USER statement */
IF INSTR(v_username, ' ') > 0 THEN

        v_username := CHR(34) || UPPER(v_username) || CHR(34); END IF;
/* Open the Cursor */

v_cursor := DBMS_SQL.OPEN_CURSOR;

/* And construct the statement */

v_changepwd := 'ALTER USER ' || v_username || ' IDENTIFIED BY ' || p_pwd;

/* Parse the ALTER USER statement which should also execute it: */
DBMS_SQL.PARSE(v_cursor, v_changepwd, DBMS_SQL.V7);

The user's role has the ALTER USER system privilege assigned to it, and the EXECUTE privilege assigned for both the DBMS_SQL package and the FNCHANGE_PASSWORD function.

What do I need to give to this role to get the right privileges to execute it?

Thanks in advance.
Tarik

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 31 2000 - 09:00:16 CST