• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Table or view does not exist , when it actually does exist

 
Ranch Hand
Posts: 391
1
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
DBA_TAB_COLUMNS is the view that contains information about columns in tables,
and because of the DBA_ prefix, you know it contains information about tables and columns
that exist anywhere in the database, regardless of owner or privileges granted.


when I login to Oracle xe with user system and query the view dba_tab_columns it works.
But when I login to Oracle xe with a user abc that I have created and have given all privileges to abc , now I try to query dba_tab_columns it says "Table or view does not exist"

Can anybody tell whats the reason

Shukran
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mahtab Alam wrote:and because of the DBA_ prefix, you know it contains information about tables and columns
that exist anywhere in the database, regardless of owner or privileges granted.


Right. And because of the DBA prefix you know that the table is supposed to be accessible to database administrators only too. Which is the default setting and really shouldn't be changed unless absolutely necessary. (Or unless you're just learning about it, of course.)

But when I login to Oracle xe with a user abc that I have created and have given all privileges to abc ,


There's a lot of privileges and roles. Show us what privileges exactly you have granted. I really doubt you've granted them all. Ideally show the grant statements themselves.

now I try to query dba_tab_columns it says "Table or view does not exist"

Can anybody tell whats the reason


The "Table or view does not exist" message is displayed when the table being queried doesn't exist, or when it does exist, but the current user doesn't have privileges to select from it, which is your case. This is for security reasons, so that some user cannot try to guess the names of existing tables and have their existence confirmed by error messages indicating lack of privileges over an existing table.

DBA_TAB_COLUMNS is actually a public synonym for a view in the SYS schema, so the privilege needs to be granted to the underlying table, not to the public synonym.

You might want to read about Data Dictionary views and something about privileges and roles.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic