Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Apr 15 '13 at 14:37

This question came from our site for professional and enthusiast programmers.

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

1 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.