Permissions needed for dbms_metadata.get_ddl in a function

Posted on

Question :

Oracle Enterprise 11.2.0.3

I’m receiving:

ORA-31603: object "string of type "string" not found in schema "string"

when executing a function that calls dbms_metadata.get_ddl.

I know that procedures/functions need have to have the permissions granted explicitly instead of through a role: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

I also know that the permissions needed to execute dbms_metadata.get_ddl is in the SELECT_CATALOG_ROLE role: https://sshailesh.wordpress.com/2010/05/08/dbms_metadata-get_ddl-avoid-ora-31603-error-by-using-select_catalog_role/

However, the SELECT_CATALOG_ROLE role contains over 2400 permissions. So, what are the explicit permissions needed to execute dbms_metadata.get_ddl so that I can call it from a function?

Test Case:

create or replace function getddl (p_type varchar2, p_object varchar2, p_owner varchar2)
return varchar2 as
begin
  return dbms_metadata.get_ddl(p_type, p_object, p_owner);
end getddl;

create table utilities.mytable (mycol varchar2(1));

select dbms_metadata.get_ddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
<DDL>

select getddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
ORA-31603: object "MYTABLE" of type TABLE not found in schema "UTILITIES"

EDIT

So far, I have explicitly granted the following to the user that is owns getddl():

select any table
select any dictionary

Answer :

Based on Raj’s comment, the following works:

create or replace function utilities.getddl (p_type varchar2, p_object varchar2, p_owner varchar2)
authid current_user
return varchar2 as
begin
  return dbms_metadata.get_ddl(p_type, p_object, p_owner);
end getddl;

select getddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
<DDL>

Please check docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66868, especially part about invokers rights (added as an answer).

Leave a Reply

Your email address will not be published. Required fields are marked *