Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Not able to query dba_tables view

 
Mahtab Alam
Ranch Hand
Posts: 391
1
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.




 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic