Re: rights on data dictionary views

Markus Boehmer wrote:

> Hi guys and gals,
>
> I have a little problem.
>
> I have two users, FOO and BAR.
> User FOO has a table named zugriffe.
>
> User BAR has only the right to connect to the databse and
> the right to select from user FOO's user_tab_columns.
>
> Here is what I have done:
> connect FOO/***
> create table zugriffe (
> leckmich date
> );
> grant select on user_tab_columns to BAR;
>
> connect BAR/***
> select * from FOO.user_tab_columns;
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
> Now I thought I could try to fool oracle and make a view,
> table zugriffe still exists:
> connect FOO/***
> CREATE VIEW TABELLENNAMEN AS
> (SELECT DISTINCT TABLE_NAME FROM USER_TAB_COLUMNS);
> grant select on tabellennamen to BAR;
>
> connect BAR/***
> select * from FOO.tabellennamen;
>
> no rows selected
>
> connect FOO/***
> select * from FOO.tabellennamen;
> TABLE_NAME
> ------------------------------
> TABELLENNAMEN
> ZUGRIFFE
>
> Why doesn't get the user BAR the data?
>
> I hope someone has a ssuggestion.
> I have to find out how to make a user with nearly no rights to get
> information about the table names and structures of some other users.
>
> Greetings
> Markus

Because USER views are information for the connected user, in your case FOO. Look at all_tab_columns where owner = 'BAR' and you will find what you seek.

BTW1: You can not beat Oracle security doing what you are doing so don't try to be smarter than the software: Work with it.

BTW2: It isn't FOOBAR ... it is FUBAR. And UBAR stands for 'up beyond all recognition'. No doubt you can figure out the unexplained letter. It is the Vietnam era update of the WWII acronym SNAFU.

Daniel Morgan Received on Wed Jan 08 2003 - 10:27:01 CST