Can DBMS_SQL Not Be Used to Alter Users?

Can DBMS_SQL Not Be Used to Alter Users?

Post by Michael Carma » Thu, 08 May 1997 04:00:00



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)==-

 
 
 

Can DBMS_SQL Not Be Used to Alter Users?

Post by Thomas Ky » Thu, 08 May 1997 04:00:00


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.



>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)==-


Thomas Kyte

Oracle Government
Bethesda MD

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

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

 
 
 

1. alter user via dbms_sql

The following procedure, which changes a user password,
gives an ORA-01031 (insufficient privileges) error. This
even though the user executing the procedure has the
ALTER USER privilege granted directly (not via a role).

Any help as to the cause of  this error would be much
appreciated.

Thanks in advance.

Kailash.

----------------------------------------------------------------------------
-------------------------------------------
CREATE OR REPLACE PROCEDURE
NGE_PASSWORD(
     P_USER_ID   IN VARCHAR2,
     P_PASSWORD   IN varchar2)

IS

CURSOR_PASS INTEGER;
CURSOR_INT INTEGER;
v_string varchar2(100);

BEGIN
        CURSOR_PASS := dbms_sql.open_cursor;
        v_string := 'alter user ' || p_user_id || ' identified by ' || p_password;
       dbms_sql.parse(cursor_PASS,v_string,dbms_sql.v7);
       CURSOR_INT := dbms_sql.execute(CURSOR_PASS);
       dbms_sql.close_cursor(cursor_PASS);
END;

------------------------------------------------------------------------------------------------------------------------------------

2. Use a DBF (Visual Fox Pro) database with Visual Basic 6

3. Not all values are fetched using dbms_sql.define_array

4. Progress Opportunities in beautiful Upstate New York

5. Select not returning records based on Date I am using

6. Advice pleased. Local and Remote connection to the same database

7. I am getting this message when i am tring to export or import anything using

8. Rcordsource

9. Alter SQL Server User password using MFC App

10. Syntax for using ALTER...ALTER COLUMN...

11. alter user to change user's password returns pg_shadow: Permission