Grant Role Via Procedure Fails When Connected As Proxy User
(Doc ID 2315613.1)
Last updated on AUGUST 09, 2018
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Information in this document applies to any platform.
A grant role in a stored procedure fails when the owner of the procedure is connected as a proxy user. When the owner is connected via own credentials, the call succeeds. This fails only on 220.127.116.11, but not on 18.104.22.168 or 22.214.171.124.
create role role1;
create user a identified by a;
create user b identified by b;
create user c identified by c;
grant create session to a,b;
alter user a grant connect through b;
grant role1 to a,b with admin option;
conn / as sysdba
create or replace procedure a.p1(P_Name varchar2) is
execute immediate 'grant role1 to "' || upper(P_Name) || '"';
-- works as expected
When this procedure is called using proxy authentication ORA-01924 error is generated.
ERROR at line 1:
ORA-01924: role 'ROLE1' not granted or does not exist
ORA-06512: at "A.P1", line 3
ORA-06512: at line 1
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document