Table user_tab_columns in oracle

Greeting,

I have a user account in oracle with DBA privilege, but I don't see the same records as ROOT account on user_tab_columns table.

Any ideas?

Thanks in advance.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
It all depends on who you log into the database as.

The USER_ level views only show objects the user you are logged in as actually owns.

ALL_ level views who the objects the user has access to and DBA_ level views shows everything.
0
mrongAuthor Commented:
so user_tab_columns is a view? who is the owner of it?
thanks.
0
slightwv (䄆 Netminder) Commented:
It is a view.  It is built into the data dictionary so SYS owns it.

BUT, who owns the view doesn't matter.  It is who SELECTS from it that matters.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mrongAuthor Commented:
what kind of privilege do I need to same the same records as root account?
thx
0
slightwv (䄆 Netminder) Commented:
root is an operating system user not a database user.

What database user and you using to connect to the database?

Post how you are connecting to the database.
0
mrongAuthor Commented:
what kind of privilege do I need to see the same records as root account does in user_tab_columns?
thx
0
slightwv (䄆 Netminder) Commented:
Same questions as above: http:#a39556528
0
mrongAuthor Commented:
The DBA created an acct for me and I use SQL plus worksheet/SQL plus to connect to database.
0
slightwv (䄆 Netminder) Commented:
But what database user?

You should be providing a username and password for sqlplus.
0
mrongAuthor Commented:
oracle database
0
slightwv (䄆 Netminder) Commented:
That doesn't give me what I'm looking for.

Post the sqlplus command you are using.
0
mrongAuthor Commented:
Open the Oracle SQL plus and put in username and password then login.
0
slightwv (䄆 Netminder) Commented:
Then any other user needs the same grants that your DBA granted to the username you log into with sqlplus.

If you log in using:
sqlplus scott/tiger

And you have a new database user named bob, the DBA needs to grant bob the same permissions as scott.

The OS user has nothing to do with database access unless you are using OS authentication.  Since you provide a database username and password, you are not.
0
YZlatCommented:
mrong, I think what slightwv is asking is which username are you entering when loggin in to Oracle SQL Plus
0
slightwv (䄆 Netminder) Commented:
>>I think what slightwv is asking is which username...

I was but I got what I needed.  They are not using OS authentication.

Actually now that I think about it, the answer to the question asked is:
create the exact same tables the user has for the other user.

Again, user_tab_columns shows what the database user you are logged into owns.



mrong,

Say you have two database users:  userA and userB.

userA creates a table:
create table tab1(col1 char(1));

when userA issues: select table_name, column_name from user_tab_columns;

They will see TAB1, COL1.

For userB to see the same thing with the same select, they have to create the exact same table.

My guess is you are after ALL_TAB_COLUMNS not USER_TAB_COLUMNS.  As I mentioned above:  ALL_ level views is everything the user has access to.

If userA granted select to userB on tab1 then userB could select table_name, column_name from ALL_TAB_COLUMNS and see TAB1, COL1 from the table userA owns.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
The dictionary is rather large, and the comments not always very useful, but one useful example is:

ALL_TABLES                   Description of relational tables accessible to the user
USER_TABLES                  Description of the user's own relational tables *

*Note' the possessive in that comment;
the contents displayed from USER_TAB_COLUMNS
will depend on which db user is logged in because it will list "user's own"
as opposed to ALL_TAB_COLUMNS
which will list "accessible to"

but noting that the dictionary comments aren't always that useful, try this:
SELECT
        max(case when TABLE_NAME like 'ALL%'  then 'ALL_'  else '' end)  as ALL_PREFIX
      , max(case when TABLE_NAME like 'USER%' then 'USER_' else '' end)  as USER_PREFIX
      , replace(replace(table_name,'USER_',''),'ALL_','')                as TOPIC
      , max(case when TABLE_NAME like 'ALL%'  then comments else '' end) as ALL_COMMENTS
      , max(case when TABLE_NAME like 'USER%' then comments else '' end) as USER_COMMENTS
FROM DICTIONARY
WHERE ( TABLE_NAME LIKE 'USER_TAB%' OR TABLE_NAME LIKE 'ALL_TAB%' )
GROUP BY
        replace(replace(table_name,'USER_',''),'ALL_','')
ORDER BY 
        replace(replace(table_name,'USER_',''),'ALL_','')
;

Open in new window

{+ edit} yes I know _ is a wildcard, please know I did this quickly and yes it could be improved
0
mrongAuthor Commented:
slightwv,

I was granted select to  ALL_TAB_COLUMNS with DBA role but still can't see all records in  ALL_TAB_COLUMNS.
0
slightwv (䄆 Netminder) Commented:
Getting select on ALL_TAB_COLUMNs doesn't do anything.

Were you granted direct access to the objects you are wanting to see?

ALL_TAB_COLUMNS shows you objects you have been directly granted access to.

From above:  If userA creates the tab1 table and you are logged in as UserB, UserB needs to have been granted select on UserA.Tab1 before userB can see the table in ALL_TAB_COLUMNS.
0
PortletPaulfreelancerCommented:
:) security is security

let's say table "secret" exists - but you don't have that level of access

when you select from all_tab_columns guess what?
>> you will not find any rows that refer to table "secret"

all_tab_columns allows you to see those things which "are accessible" to you as determined by your permissions

(and: "you" is determined by how you login to the db)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.