privileges oracle jdbc getMetaData

Question

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 ?

Answer 1

Thanks @Lotar Schin, your solution is so simple and really works. After read the source code of the Oracle JDBC driver, I had a more complete list of synonyms:

create user metadata_collector identified by "password";
grant connect to metadata_collector;
grant select any dictionary to metadata_collector;
create or replace synonym metadata_collector.all_objects for dba_objects;
create or replace synonym metadata_collector.all_tables for dba_tables;
create or replace synonym metadata_collector.all_tab_comments for dba_tab_comments;
create or replace synonym metadata_collector.all_tab_columns for dba_tab_columns;
create or replace synonym metadata_collector.all_col_comments for dba_col_comments;
create or replace synonym metadata_collector.all_users for dba_users;
create or replace synonym metadata_collector.all_constraints for dba_constraints;
create or replace synonym metadata_collector.all_cons_columns for dba_cons_columns;
create or replace synonym metadata_collector.all_arguments for dba_arguments;
create or replace synonym metadata_collector.all_synonyms for dba_synonyms;
create or replace synonym metadata_collector.all_col_privs for dba_col_privs;
create or replace synonym metadata_collector.all_tab_privs for dba_tab_privs;
create or replace synonym metadata_collector.all_ind_columns for dba_ind_columns;
create or replace synonym metadata_collector.all_trigger_cols for dba_trigger_cols;
create or replace synonym metadata_collector.all_indexes for dba_indexes;
create or replace synonym metadata_collector.all_types for dba_types;

Answer 2

I had the same issue with jdbc. My solution is that simple.

create or replace synonym youruser.all_objects for dba_objects;
create or replace synonym youruser.all_tables for dba_tables;
create or replace synonym youruser.all_tab_columns for dba_tab_columns;
create or replace synonym youruser.all_users for dba_users;
create or replace synonym youruser.all_constraints for dba_constraints;
create or replace synonym youruser.all_cons_columns for dba_cons_columns;

That works well to me.

Answer 3

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 remarks\n" +
        "  FROM dba_objects o, dba_tab_comments c\n"+
        "  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));
            }    

    }

}

Answer 4

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.

.