Oracle – Understanding Information_Schema in Oracle

What I ended up doing was giving the DMG schema DBA access right before creating the index then revoking it immediately after. I was afraid that would cause the index to fail to sync but it seems to work just fine. I was able move everything into the DMG schema= and remove the initial grants to the ctxsys schema.

I'm still not clear on what specific permissions I needed to give the DMG schema, but I got around my problem.

I'd create a procedure returning all tables/columns and grant access to that procedure:

  create or replace package pkg_ddl_read as

    procedure get_table_cols(v_cur in out sys_refcursor);

  end;


  create or replace package body pkg_ddl_read as

    procedure get_table_cols(v_cur in out sys_refcursor) is
    begin
      open v_cur for
        select owner, table_name, column_name 
        from dba_tab_cols;
    end;

  end;


  grant execute on pkg_ddl to <myuser>;

Depending on what privileges the creating user (not the user you want to give access to) has and whether you want to allow the reading user to get the DDL for every table in the database or only for a certain subset, you might want to

• use all_tab_cols / user_tab_cols instead of dba_tab_cols
• add a where clause to the procedure