Oracle Database Discussions

Running create or replace to update code can cause any roles granted to the code (CBAC) to be lost.

This happens on 19c ATP Free Tier and the 23c FREE VM.

Workaround: re-run the grant.

Setup as DBA (23c version allow “if exists” clause)

drop role if exists test_cbac_role;
drop user if exists schema2 cascade;
drop user if exists schema1 cascade;

create user schema2 identified by Change0nInstall
  account unlock;
grant create session,create procedure to schema2;

create role test_cbac_role;
grant test_cbac_role to schema2 with delegate option;

TEST (connect as schema2 )

create or replace
procedure p
  authid definer
as
  n int;
begin
  dbms_output.put_line('I am here');
end;
/
grant test_cbac_role to procedure p;
select * from user_code_role_privs where object_name = 'P';
-- sql%rowcount should be 1; actual 1

create or replace
procedure p
  authid definer
as
  n int;
begin
  -- 1 line
  dbms_output.put_line('I am here');
end;
/
select * from user_code_role_privs where object_name = 'P';
-- sql%rowcount should be 1; actual 1

create or replace
procedure p
  authid definer
as
  n int;
begin
  -- 1 line
  -- 2 line
  dbms_output.put_line('I am here');
end;
/
select * from user_code_role_privs where object_name = 'P';
-- sql%rowcount should be 1; actual 0