| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Can DBMS_SQL Not Be Used to Alter Users?
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.
On 7 May 1997 16:18:56 -0400, mcarmack_at_freenet.columbus.oh.us (Michael Carmack) wrote:
>
>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)==-
> S P O O N !!!! - The Tick | mcarmack_at_freenet.columbus.oh.us
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |