Cfrgtkky

I'm using the JDBC Driver to connect to different database type.
My database's users have only view permissions on the Catalog.
It works fine for hive/teradata but not with Oracle.

With Oracle, I'm able to retrieve Schemas but not Tables/Columns.

Oracle privileges :

SELECT_CATALOG_ROLE, CREATE SESSION, CONNECT

Java Code :

DatabaseMetaData databaseMetadata = con.getMetaData();
resTables = databaseMetadata.getTables("Test_Schema", null, null,
null);

But once I give select permission on tables, it works.

Do I miss something ?

SELECT_CATALOG_ROLE allow SELECT privileges on data dictionary views(for exapmle select * from dba_users)
, but not for user tables.
You must explicitly specify the rights to the desired table for example
grant select,update,delete on need_table for user_name;
grant select,update,delete on DEPARTMENTS to test;

or give select right to all tables (bad practic).

[oracle10@aktp ~]$ sqlplus / as sysdba



SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 21 14:39:55 2018



Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.





Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options



SQL> grant select any table to test;



Grant succeeded.

.

Using DatabaseMetaData.getMetaData replies on the underlying views all_objects , all_tab_comments, and all_synonyms. These views take into consideration what is granted for access.

SELECT_CATALOG_ROLE grants access to the catalog which would allow to query dba_objects.

To do what is asked, a mirrored Java API to DatabaseMetaData.getMetaData().getTables(..) can be easily written to use dba_xyz views. Something along the lines of this.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;



import oracle.jdbc.OracleConnection;

import oracle.jdbc.OracleDatabaseMetaData;



public class MyMetaData extends OracleDatabaseMetaData {







    public MyMetaData(Connection conn) {

        super((OracleConnection) conn);

    }



    public synchronized ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types) throws SQLException {

        // catalog isn't used

        // dba view based sql  



        String sql = "SELECT NULL AS table_cat,"+

        "       o.owner AS table_schem,n       "+

        "       o.object_name AS table_name,n  "+

        "       o.object_type AS table_type,n"+

        "       c.comments AS remarksn" +

        "  FROM dba_objects o, dba_tab_comments cn"+

        "  WHERE o.owner LIKE :1 ESCAPE '/'n    "+

        "  AND o.object_name LIKE :2 ESCAPE '/'n"+

        "  AND o.owner = c.owner (+)n    "+

        "  AND o.object_name = c.table_name (+)n";



        // bind params



         PreparedStatement stmt = connection.prepareStatement(sql);

         stmt.setString(1, schemaPattern == null ? "%" : schemaPattern);

         stmt.setString(2, tableNamePattern == null ? "%" : tableNamePattern);



        return stmt.executeQuery();

    }



    public static void main(String args) throws SQLException {



        String conString = "jdbc:oracle:thin:@//localhost:1521/xe";

        Properties props = new Properties();

        props.setProperty("user", "klrice");

        props.setProperty("password", "klrice");

        Connection conn = DriverManager.getConnection(conString, props);

        // use this class

        MyMetaData md = new MyMetaData(conn);



        // test it out

        ResultSet rset = md.getTables(null, "ORDS_METADATA", null, null);

            while (rset.next()) {

                System.out.println(rset.getString(2));

            }    



    }



}