Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes Retrieve all table names from a DB for a user Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retrieve all table names from a DB for a user" Watch "Retrieve all table names from a DB for a user" New topic
Author

Retrieve all table names from a DB for a user

Aditya Suryam Adep
Greenhorn

Joined: Nov 25, 2008
Posts: 2
Hi,
Problem here is I can retrieve all the table names from DB using con.getMetadata.getTables(). This returns all the tables in the DB irrespective of wether user(used in connection string) has privileges or not. SO in next step when I am using one of the tables from the fetched result set, I get 'table or view does not exist'.

I tried to get privileges with con.getMetadata.getTablePrivileges(null,null,tablename). This narrows the tables further but still when I do a select in some of this table I face the same issue 'table or view does not exist'.

Am I doing something wrong in second step ?

Is there any other way of doing this in JDBC?

I need to get only those table names for which the user can perform select operation?

any help would be good.

Thanks,
Aditya
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

What database is this? Different databases handle metadata differently. You could perform the query inside of a try/catch block and assume a catch means the table is unaccessible, but I imagine there's a better route.


My Blog: Down Home Country Coding with Scott Selikoff
Aditya Suryam Adep
Greenhorn

Joined: Nov 25, 2008
Posts: 2
Hi Scott,

Thank you for the reply.

I guess thats hard way of doing it.. :-) though I tried but the problem is not solved by that.

One more thing, I face in this is even though from getPrivileges() I filter out some tables for which SELECT is permitted (as they are retunred from teh method), still it says table or view does not exist. Infact it seems to be correct, I couldnt access those tables on DB. So what this method actually gives is still a mistery for me.

For eg : Follwoing is the method..
public static void printMetaData(){
try{
ResultSet rs = con.getMetaData().getTables(null, null, "%",new String[]{"TABLE"});
ResultSet rs2;
String table;
while(rs.next()){
table = rs.getString(3);
rs2 = con.getMetaData().getTablePrivileges(null, null, table);
while(rs2.next()){
if(rs2.getString("PRIVILEGE").equalsIgnoreCase("SELECT"))
System.out.println(table.substring(table.lastIndexOf('$')+1,table.length()) + " : "+rs2.getString("GRANTOR") + " : " + rs2.getString("GRANTEE") +" : " + rs2.getString("PRIVILEGE"));
}
rs2.close();

}
}catch(Exception e){
e.printStackTrace();
}
}

This returns.. sample row :
POLICY_TAB : CTXSYS : PUBLIC : SELECT

Now this table POLICY_TAB is not accessbile by the user I connected with.

Currently I am trying on Oracle. But my requirement is independant of DB.

Regards,
Aditya
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retrieve all table names from a DB for a user
 
Similar Threads
db2 - 703 certification
DB2 700 and 701 - Test Sample
problem connecting to Cloudscape database in WSAD
SQL injection?
Unexpected tables in list