Re: Errors executing password change procedure

Still getting insufficient privileges and I know the user has "alter user privileges" since I can do it from command line in the database.

On Wed, Nov 28, 2018 at 11:08 AM Sandra Becker <sbecker6925_at_gmail.com> wrote:

> I'll try that, thanks.
>
> On Wed, Nov 28, 2018 at 11:06 AM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
> wrote:
>
>> I don't believe you alter the current user in a procedure. Why not just
>> create the procedure as owned by a privileged user and grant execute of the
>> procedure as required.
>> >>> Sandra Becker <sbecker6925_at_gmail.com> 11/28/18 11:56 AM >>>
>> Oracle Enterprise version 12.1.0.2
>>
>> We have a new requirement to allow users to change their passwords, even
>> if expired and/or account is locked. Per the requirements, I have created
>> the new user (not allowed DBA privs) that will connect through a GUI and
>> execute a password change procedure in another schema that has the
>> necessary privileges. This new user has been granted execute privileges on
>> the procedure. However, I'm getting an "ORA-01031: insufficient privileges"
>> error when I try to execute the procedure as the new user.
>>
>> I'm new to writing PL/SQL, so I found an example and have been trying to
>> tweak it to meet our requirements. Any help would be greatly appreciated. I
>> did grant "alter user" to the new user, but I'm not sure I really need that
>> and it didn't make a difference to the error.
>>
>> CREATE OR REPLACE PROCEDURE haalochangepassword(
>> p_username IN VARCHAR2,
>> p_password IN VARCHAR2) AS
>> BEGIN
>> -- Check for system users here and reject
>> IF UPPER(p_username) IN ('PRIVUSER','SYS','SYSTEM') THEN
>> dbms_output.put_line('Password change not allowed');
>> ELSE
>> EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema = PRIVUSER';
>> EXECUTE IMMEDIATE 'ALTER USER '||p_username||' IDENTIFIED BY '
>> ||p_password||' ACCOUNT UNLOCK';
>> dbms_output.put_line('Password change successful');
>> END IF;
>> EXCEPTION
>> when NO_DATA_FOUND THEN
>> raise_application_error(-20000,'No user found');
>> when others THEN
>> dbms_output.put_line('Password change procedure error: ' || sqlerrm);
>> END;
>> /
>>
>> --
>> Sandy B.
>>
>>
>
> --
> Sandy B.
>
>

-- 
Sandy B.

--
http://www.freelists.org/webpage/oracle-l