• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Retrieve all table names from a DB for a user

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Aditya Suryam Adep
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
CAUTION! Do not touch the blades on your neck propeller while they are active. Tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic