Select dbms_metadata.get_ddl without select_catalog_role

Question

I am trying to execute the query

SELECT dbms_metadata.get_ddl('TABLE','PRODUCT','INVENTORY') FROM DUAL;

which the giving me error -

ORA-31603: object "PRODUCT" of type TABLE not found in schema "INVENTORY"
ORA-06512: at "SYS.DBMS_METADATA", line 6781

Use of select_catalog_role and select any dictionary is not allowed by DBA policy on service account.
So I must provide granular role/previliage to service account so I can execute the select metadata query.

You can't - you will have to connect as the owner. If the user you are connected as now can see the table then you shouldn't get that error, [but you can only retrieve the grants](https://stackoverflow.com/a/26026495/266304). Why does another account need to have this access? You could potentially have a function in the inventory schema that performs the query and returns the result - would that be allowed? Or you could expdp as inventory and generate the DDL with impdp's sqlfile option, as more of a one-off process. — Alex Poole, Feb 17 '23 at 09:29
Hi @AlexPoole, We are implementing CDC with debezium tool. And debezium connector user need to execute this query to generate a CREATE TABLE statement for the schema history. I am trying to find way if there is option I can grant on few package/table/roles which will allow execution of this select statement without select_catalog_role grant. — javadev, Feb 17 '23 at 10:00
You would need to have a function in the inventory schema that returns the DDL then, and give execute privs to your connector user. — Alex Poole, Feb 17 '23 at 10:25
Thank Alex ! But There is no mechanism to configure Debezium tool to query with wrapper function to get DDL of schema table. It by default use dbms_metadata.get_ddl package internally, so for me only option to make it working with some other grant/reveliage. — javadev, Feb 17 '23 at 12:52
It is an unreasonable restriction that an account with a legitimate need to extract metadata from other schemas cannot be given "select any dictionary". I would push back on your DBAs on this point, and submit a request for an official exception. — Paul W, Feb 17 '23 at 13:49
@javadev - the only privileges that would let this work are the ones you aren't allowed. Looking briefly at the Debezium docs it needs a lot of privileges. If you are required to use this tool then the connector user will require [all the privileges it shows](https://debezium.io/documentation/reference/stable/connectors/oracle.html#creating-users-for-the-connector). So this is a political discussion to resolve the conflict between whoever is telling you to use this and the DBAs - who may be persuaded to treat this as an exception/concession to policy, *if* it is justified. But we can't help. — Alex Poole, Feb 17 '23 at 15:34
@philipxy Although there's no literal question mark in the post, I think there is certainly a valid programming question here. While these privilege issues are ideally handled by internal discussions with the security or DBA team, in practice these issues often require workarounds by developers. — Jon Heller, Feb 19 '23 at 17:48

Jon Heller · Answer 1 · 2023-02-20T16:59:08.307

Create a fake DBMS_METADATA on the DEBEZIUM schema that calls a definer's rights function on INVENTORY that calls the real DBMS_METADATA. This workaround assumes that Debezium calls the package DBMS_METADATA instead of using the fully-qualified name SYS.DBMS_METADATA. Oracle uses schema objects before public synonyms, so this custom package let's you hijack some calls to the real SYS.DBMS_METADATA.

Yes, this is an overly complicated mess that should only be used as a last resort. Perhaps instead of actually using this code, you can just show it to the DBA to convince them that you need the right privilege.

As INVENTORY, create a package that mimics part of SYS.DBMS_METADTATA, and grant it to DEBEZIUM:

create or replace package inventory.dbms_metadata_without_privs authid definer is
/*
  This package exists because DEBEZIUM cannot be granted SELECT_CATALOG_ROLE because
  [enter reason here]

  Note that this package is "DEFINER" instead of the "CURRENT_USER" of the real DBMS_METADATA.
*/

  FUNCTION get_ddl (
                object_type     IN  VARCHAR2,
                name            IN  VARCHAR2,
                schema          IN  VARCHAR2 DEFAULT NULL,
                version         IN  VARCHAR2 DEFAULT 'COMPATIBLE',
                model           IN  VARCHAR2 DEFAULT 'ORACLE',
                transform       IN  VARCHAR2 DEFAULT 'DDL')
        RETURN CLOB;

  PROCEDURE set_transform_param (
                transform_handle        IN  NUMBER,
                name                    IN  VARCHAR2,
                value                   IN  BOOLEAN DEFAULT TRUE,
                object_type             IN  VARCHAR2 DEFAULT NULL,
                flags                   IN  NUMBER DEFAULT 0);

end dbms_metadata_without_privs;
/


create or replace package body inventory.dbms_metadata_without_privs is
  FUNCTION get_ddl (
                object_type     IN  VARCHAR2,
                name            IN  VARCHAR2,
                schema          IN  VARCHAR2 DEFAULT NULL,
                version         IN  VARCHAR2 DEFAULT 'COMPATIBLE',
                model           IN  VARCHAR2 DEFAULT 'ORACLE',
                transform       IN  VARCHAR2 DEFAULT 'DDL')
        RETURN CLOB IS
  BEGIN
    RETURN SYS.DBMS_METADATA.get_ddl(object_type => object_type,
                                     name        => name,
                                     schema      => schema,
                                     version     => version,
                                     model       => model,
                                     transform   => transform);
  END;

  PROCEDURE set_transform_param (
                transform_handle        IN  NUMBER,
                name                    IN  VARCHAR2,
                value                   IN  BOOLEAN DEFAULT TRUE,
                object_type             IN  VARCHAR2 DEFAULT NULL,
                flags                   IN  NUMBER DEFAULT 0) IS
  BEGIN
    SYS.DBMS_METADATA.set_transform_param(transform_handle => transform_handle,
                                          name             => name,
                                          value            => value,
                                          object_type      => object_type,
                                          flags            => flags);
  END;

end dbms_metadata_without_privs;
/

grant execute on inventory.dbms_metadata_without_privs to debezium;

As DEBEZIUM, create a fake DBMS_METADATA that simply calls the package in INVENTORY:

CREATE OR REPLACE NONEDITIONABLE PACKAGE DEBEZIUM.dbms_metadata AUTHID CURRENT_USER AS
/*
  This fake DBMS_METADATA exists because DEBEZIUM cannot be granted SELECT_CATALOG_ROLE because
  [enter reason here]

  Yes, this is a stupid idea for many reasons...
*/

--------------------
--  PUBLIC CONSTANTS
--
  SESSION_TRANSFORM     CONSTANT BINARY_INTEGER := -1;

  FUNCTION get_ddl (
                object_type     IN  VARCHAR2,
                name            IN  VARCHAR2,
                schema          IN  VARCHAR2 DEFAULT NULL,
                version         IN  VARCHAR2 DEFAULT 'COMPATIBLE',
                model           IN  VARCHAR2 DEFAULT 'ORACLE',
                transform       IN  VARCHAR2 DEFAULT 'DDL')
        RETURN CLOB;

  PROCEDURE set_transform_param (
                transform_handle        IN  NUMBER,
                name                    IN  VARCHAR2,
                value                   IN  BOOLEAN DEFAULT TRUE,
                object_type             IN  VARCHAR2 DEFAULT NULL,
                flags                   IN  NUMBER DEFAULT 0);
END DBMS_METADATA;
/

CREATE OR REPLACE NONEDITIONABLE PACKAGE BODY DEBEZIUM.dbms_metadata AS

  FUNCTION get_ddl (
                object_type     IN  VARCHAR2,
                name            IN  VARCHAR2,
                schema          IN  VARCHAR2 DEFAULT NULL,
                version         IN  VARCHAR2 DEFAULT 'COMPATIBLE',
                model           IN  VARCHAR2 DEFAULT 'ORACLE',
                transform       IN  VARCHAR2 DEFAULT 'DDL')
        RETURN CLOB
  IS
  BEGIN
    RETURN inventory.dbms_metadata_without_privs.get_ddl(object_type => object_type,
                                                         name        => name,
                                                         schema      => schema,
                                                         version     => version,
                                                         model       => model,
                                                         transform   => transform);
  END;

  PROCEDURE set_transform_param (
                transform_handle        IN  NUMBER,
                name                    IN  VARCHAR2,
                value                   IN  BOOLEAN DEFAULT TRUE,
                object_type             IN  VARCHAR2 DEFAULT NULL,
                flags                   IN  NUMBER DEFAULT 0)
  IS
  BEGIN
    inventory.dbms_metadata_without_privs.set_transform_param(transform_handle => transform_handle,
                                                              name             => name,
                                                              value            => value,
                                                              object_type      => object_type,
                                                              flags            => flags);
  END;
END DBMS_METADATA;
/

As DEBEZIUM, you can now run these statements:

select dbms_metadata.get_ddl('TABLE', 'PRODUCT', 'INVENTORY') from dual;

begin dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT'); end;
/

Most of the above code was taken straight from the DBMS_METADATA package. If you need to implement more functionality from that package, you can use the below query to copy and paste the procedure definitions. (Oddly, DBMS_METADATA does not work correctly on DBMS_METADATA.)

select *
from all_source
where owner = 'SYS'
  and name = 'DBMS_METADATA'
  and type = 'PACKAGE'
order by line;

Hi @Jon Heller, With the above I'm able to execure seect dbms_metadata.get_ddl. Thanks. Now I got, begin dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'DEFAULT'); end; / Caused by: Error : 6550, Position : 54, Sql = begin dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT'); end;, OriginalSql = begin dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT'); end;, Error Msg = ORA-06550: line 1, column 55: PLS-00302: component 'SESSION_TRANSFORM' must be declared ORA-06550: line 1, column 7: — javadev, Feb 20 '23 at 08:20
@javadev See my update for the `SESSION_TRANSFORM` procedure, and some tips for how to add more procedures if necessary. But `GET_DDL` and `SESSION_TRANSFORM` are the most popular parts of the `DBMS_METADATA` package, so hopefully that's the last piece you'll need. — Jon Heller, Feb 20 '23 at 17:00

Select dbms_metadata.get_ddl without select_catalog_role

1 Answers1