Oracle Database: View schema without access to data

Question

In Oracle Database 10g, I need to create a user with these permissions:

• user can access schema to be able to read table names and columns
• user can not select (get) any data from any table

Is this possible or is there a way for a workaround?

Answer 1

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