Re: Errors executing password change procedure

From: Tim Hall <tim_at_oracle-base.com>
Date: Wed, 28 Nov 2018 18:34:39 +0000
Message-ID: <CAP=5zEjgAZoZL0C3zrG+FqmNHG9CQX4=X5fzjtYw6gCzhdk4yw_at_mail.gmail.com>

I think this demonstrates what I mean.

conn / as sysdba
alter session set container=pdb1;

• Create users. create user test1 identified by test1; grant create session, create procedure, alter user to test1;

create user test2 identified by test2;
grant create session to test2;

create user test3 identified by test3;
grant create session to test3;

• Test basic password change. conn test1/test1_at_pdb1 alter user test2 identified by test3;

conn test2/test3_at_pdb1
--Works!

• Create a procedure to do it. conn test1/test1_at_pdb1 CREATE PROCEDURE change_passwd AS BEGIN EXECUTE IMMEDIATE 'alter user test2 identified by test4'; END; /

grant execute on change_passwd to test3;

• Test it. conn test3/test3_at_pdb1 exec test1.change_passwd;

conn test2/test4_at_pdb1
-- It works!

• Cleanup conn / as sysdba alter session set container=pdb1;

drop user test1 cascade;
drop user test2 cascade;
drop user test3 cascade;

On Wed, Nov 28, 2018 at 6:17 PM Tim Hall <tim_at_oracle-base.com> wrote:
>
> Remember that roles are handled differently by stored procedures,
> compared to anonymous blocks and SQL. If your procedure users definer
> rights (the default), it can't use privs via a role.
> On Wed, Nov 28, 2018 at 6:12 PM Sandra Becker <sbecker6925_at_gmail.com> wrote:
> >
> > 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

Received on Wed Nov 28 2018 - 19:34:39 CET