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
add comment

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

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

1 Answer

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.
{"cc_re__cc_re__cc_by_sa_3_0": ["        <li><a href=\"http://area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"http://careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.4.18.1558\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Database Administrators Stack Exchange works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class"], "copyright__copyright__copyright": [" pkg_ddl to <myuser>;\n\nDepending 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\n\n  \u2022 use all_tab_cols / user_tab_cols instead of dba_tab_cols\n  \u2022 add a where clause to the procedure\nshare|improve this answer\nadd comment\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service."]}