Dbms_Metadata.Get_Ddl Not Working

dbms_metadata.get_ddl not working

From the dbms_metadata documentation:

If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

So unless you're connected as a privileged user, you can't see the DDL for another user's objects. You would need to connect as SYS, or have the SELECT_CATALOG_ROLE role granted to your user to be able to get XT's object definition.

Even with that role:

In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.

If you're calling dbms_metadata from an anonymous PL/SQL block that doesn't matter, but if you're calling it from a procedure you will have to include an AUTHID clause in the procedure declaration, adding AUTHID CURRENT_USER.

DBMS_METADATA.GET_DDL don't work inside a PROCEDURE

Oracle Documentation states:

In stored procedures, functions, and
definers-rights packages, roles are disabled.
Therefore, such a PL/SQL program can
only fetch metadata for objects in its
own schema. If you want to write a
PL/SQL program that fetches metadata
for objects in a different schema
, you must make
the program invokers-rights.

To do this, you must add authid to your procedure.

ORA-00904: DBMS_METADATA.GET_DDL: invalid identifier

Run this command as SYS:

grant execute on sys.dbms_metadata to public;

Granting access to your specific user may have solved your current problem, but that package really needs to be available to the entire system. Many third party programs depend on DBMS_METADATA. By default that package should be granted to PUBLIC.

Some old versions of the STIG (Secure Technical Implementation Guidelines, which almost every auditor uses as the basis for their security scripts), would revoke access from public packages. But that was a stupid idea even 10 years ago, and it's not in the current STIGs anymore anyway.

---

Related Topics