3

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?

3
  • 2
    Creating a user with only the SELECT_CATALOG_ROLE (and the CONNECT privilege) should do the job.
    – user1822
    Apr 15, 2013 at 14:19
  • 1
    @a_horse_with_no_name You meant CONNECT role which contains only CREATE SESSION privilege in it (: Apr 15, 2013 at 14:27
  • @YasirArsanukaev: yes, correct. I actually only meant CREATE SESSION (bad habits...)
    – user1822
    Apr 15, 2013 at 14:41

1 Answer 1

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.