Hi,
Based on this thread, APEX to change DB user Passwords I've created a change password page for the end users which Authentication Scheme is Database Accounts.
When I test via the application I get "ORA-01031 insufficient privileges".
here is the procedure in my package pkapx_connexion :
-- Package pkapx_connexion
PROCEDURE change_password (
p\_username IN VARCHAR2,
p\_password IN VARCHAR2
) AS
cursor\_handle INTEGER;
return\_value INTEGER;
BEGIN
cursor_handle := dbms_sql.open_cursor;dbms_sql.parse ( c => cursor_handle,
statement => 'ALTER USER '
|| p\_username
|| ' IDENTIFIED BY '
|| p\_password,
language\_flag => dbms\_sql.native);
return\_value := dbms\_sql.execute(cursor\_handle);
dbms\_sql.close\_cursor(cursor\_handle);
END;
The user "jyb" have the grant execute for this package.
When I run via SqlDeveloper with the "jyb" account I get the same error :
DECLARE
p\_username VARCHAR2(200);
p\_password VARCHAR2(200);
BEGIN
p\_username := 'jyb';
p\_password := 'pwdx';
pkapx\_connexion.change\_password(p\_username => p\_username, p\_password => p\_password);
END;
Rapport d'erreur -
ORA-01031: privilèges insuffisants
ORA-06512: à "SYS.DBMS_SQL", ligne 1134
ORA-06512: à "OTECH.PKAPX_CONNEXION", ligne 123
ORA-06512: à ligne 7
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
But when i call directly dbms_sql via SqlDeveloper with the "jyb" account it works .. !
DECLARE
cursor\_handle INTEGER;
return\_value INTEGER;
p\_username VARCHAR2(100) := 'jyb';
p\_password VARCHAR2(100) := 'pwdz';
BEGIN
cursor\_handle := dbms\_sql.open\_cursor;
dbms\_sql.parse(cursor\_handle, 'ALTER USER '
|| p\_username
|| ' IDENTIFIED BY '
|| p\_password, dbms\_sql.native);
return\_value := dbms\_sql.execute(cursor\_handle);
dbms\_sql.close\_cursor(cursor\_handle);
END;
Procédure PL/SQL terminée.
What did I miss to make it work ?
JeanYves