Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: User Password change privileges?

Re: User Password change privileges?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 31 Oct 2000 16:21:35 +0100
Message-ID: <973005703.8518.2.pluto.d4ee154e@news.demon.nl>

The user's *role* has the ALTER USER system privilege assigned to it.

This says it all.

Roles are not considered during compilation of a stored procedure as roles are volatile.

You need to grant alter user privilege to the *owner* of the procedure *directly*.

Hth,

Sybrand Bakker, Oracle DBA

<tariks_at_my-deja.com> wrote in message news:8tmmpq$p8s$1_at_nnrp1.deja.com...
> 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:21:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US