File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Not able to query dba_tables view Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Not able to query dba_tables view" Watch "Not able to query dba_tables view" New topic
Author

Not able to query dba_tables view

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 249

I connected with database as user system

connect system/aaa

then I created a user
create user neural identified by neural;

I just give it privilege to connect as
grant create session to neural;

then I connected to database as user neural

connect neural/neural;

Now i executed this command
select owner,table_name
from all_tables;

I was able to see the tables and their owners.
Then I tried to execute this query

select table_name
from dba_tables;
//It says no such table or view exists.

Why ,Is it because I don`t have privilege to select from dba_tables view.
But I was able to select from all_tables.Is it because I get implicit select privilege on all_tables because I haven`t gave it privilege to select from all_tables.






Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Mahtab Alam wrote:Why ,Is it because I don`t have privilege to select from dba_tables view.
But I was able to select from all_tables.Is it because I get implicit select privilege on all_tables because I haven`t gave it privilege to select from all_tables.

That's correct.

Everyone can query ALL_TABLES, because this view contains information about tables the user was granted some permission on. Similarly, everyone can query USER_TABLES, because it contains information about tables created by current user. Lots of tools do need this kind of information, and - since the permissions to access these tables was consciously given at some point - there isn't any inherent security risk.

DBA_TABLES is very different. It contains information about all tables in the database, even if the user doesn't have any privileges granted on them. It would be a huge security risk to allow every user to list all tables that exist in the database (it would make SQL injection attacks far easier, for example). So, only users with specific privileges (in short, DBAs) can query the DBA_xxx views.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Not able to query dba_tables view