Very Computer

I'm trying to put together a package that will allow me to expire user
passwords at predefined intervals.  Part of the package is supposed to
change the users password for him using the DBMS_SQL package routines.
However, I get "insufficient privileges" errors when I run it, even though
the owner of the procedure has full system privileges and can issue the
command successfully from an interactive prompt.  Is DBMS_SQL simply not
capable of handling this SQL comnmand?

For the record, I'm running Oracle 7.1.5 on Alpha/OpenVMS.  The relevant
portion of the stored procedure follows.

    vSQL := 'alter user ' || vUserName || ' identified by '
            || upper(NewPassword);
    dbms_output.put_line('vSQL = "' || vSQL || '"');
    sqlcursor := dbms_sql.open_cursor;
    dbms_sql.parse(sqlcursor, vSQL, dbms_sql.NATIVE);
    sqlreturn := dbms_sql.execute(sqlcursor);

--
========================================================================
"Villains, I say to you now:       |   Mike Carmack
 KNOCK OFF ALL THAT EVIL!"         |   Vulcan Dragon -==(UDIC)==-

roles are never enabled during the executoin of a procedure.

Try this:

SQL> set role none;
SQL> alter user SOMEONE identified by SOMETHING;

If you can do it in plus with no roles you can do it in a procedure.  If you
can't, you must have the privelege from a role and hence dbms_sql won't be able
to do it either.

You probably have the "alter any user" privelege from the DBA role.  Grant the
privelege directly to the owner of the procedure and it'll work.

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation