View that shows table columns for which user has privileges

[cdutta]

Hi,

I have a user that wants to see a list of all tables and columns
of tables that he has being granted select privs to.
He is accessing the database thru Access dabatase and has no
access to SQL Plus. He wants to see the table and column information. I have logged in as him and
queried dba_col_privs but returns no rows. I read then that at column level the only privs that can be granted are
Update and Insert. How can I create a view that returns
all columns names and table names and schema owner which
could be seen by my user only. Thanks,
Any help would be appreciated..
CD

[sambavan]

You can use the following views:

USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD ( Privileges received on other schemas )


Sam

[cdutta]

I could see the list of tables that my user have been granted
select privs on other schemas all right by
quering

select owner, table_name, grantor
from
user_tab_privs_recd;

in user schema.
How can I see the column name for each table? if I try to
query user_col_privs_recd I get zero rows... Shuldn't return a list of the columns of the tables the user has privs to select from?

[sambavan]

The reason is as follows.

If the user had been given a privilege on the table level, then you would not see the table's columns to be listed under USER_COL_PRIVS_RECD. It would be an extra over head on the dictionary. On the other hand, if the user had received a colum level previlege on any table, instead of the whole table, then you would see that on the USER_COL_PRIVS_RECD and not on the USER_TAB_PRIVS_RECD .

Sam

[cdutta]

All right then, is there a way for my user to see the columns
of tables he has select privs or not?
I just cannot believe that Oracle does not have this feature. Even SQL Server has
sp_columns...

How can I solve this?
Thanks,
CD

[pando]

I dont think you can restrict the select priv on specific columns of a table of an user, only references, update and insert works

you have to create a view for this user

this is an enhancement request to Oracle