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@xxxxxxxxx> 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@xxxxxxxxx> wrote:
I'll try that, thanks.
On Wed, Nov 28, 2018 at 11:06 AM Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
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.
Oracle Enterprise version 12.1.0.2Sandra Becker <sbecker6925@xxxxxxxxx> 11/28/18 11:56 AM >>>
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.
