Re: User Password change privileges?

The best solution to the problem you have is to give each and every user a username without spaces. Any other solution you can dream up will just cause a nightmare in the future.

Dan Morgan

tariks_at_my-deja.com wrote:

> 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 Mon Dec 11 2000 - 01:36:11 CST