6 Replies Latest reply on Jul 23, 2017 9:36 PM by uuliver

Unable to connect to oracle database when user having no access to sys.tables

kulbhushanc Jun 12, 2017 8:15 AM

Hi,

I am unable to connect to oracle database when user having no access to sys.tables.

Like sys.tables what all dependencies we need to resolve to connect to the database.

trowing the error

2017-06-12 12:11:24,194 WARN [org.teiid.PLANNER.RESOLVER] (Worker23_async-teiid-threads39) TEIID31070 Empty model;There are no tables, procedures or functions defined in this model TESTDSORACLE20170612121123911

but I ensure that the user has the table access to it.

Note: I am using teiid 9.1.3 admin api and wildfly 10.0.0

Thanks,

Kulbhushan Chaskar.

1. Re: Unable to connect to oracle database when user having no access to sys.tables

shawkins Jun 12, 2017 8:18 AM (in response to kulbhushanc)

What are you importer settings?

You can also turn the org.teiid.CONNECTOR to trace to see more of the import messages.
Actions
2. Re: Unable to connect to oracle database when user having no access to sys.tables

kulbhushanc Jun 14, 2017 1:55 AM (in response to shawkins)

Thanks Steven !!

VDB contents are:-

<?xml version="1.0" ?><vdb name="SvcSourceVdb_oracleKul" version="1"><description>VDB for: SvcSourceVdb_oracleKul, Version: 1</description><connection-type>BY_VERSION</connection-type><model name="oracleKul" type="PHYSICAL" visible="true"><property name="importer.tableTypes" value="TABLE"></property><property name="importer.schemaPattern" value="ABCD"></property><source name="oracleKul" translator-name="oracle" connection-jndi-name="java:/DSORACLE20170324170401448"></source></model><model name="oracleKulNative" type="PHYSICAL" visible="true"><property name="importer.tableTypes" value="TABLE"></property><property name="importer.schemaPattern" value="ABCD"></property><source name="oracleKulNative" translator-name="oracle-native" connection-jndi-name="java:/DSORACLE20170324170401448"></source></model><translator name="oracle-native" type="oracle"><property name="SupportsDirectQueryProcedure" value="true"></property></translator></vdb>

so I am using importer.tableTypes as TABLE. Can you also tell me how to turn the org.teiid.CONNECTOR on, So that I can post logs.

Thanks ,
Kulbhushan Chaskar.
Actions
3. Re: Unable to connect to oracle database when user having no access to sys.tables

shawkins Jun 21, 2017 10:08 AM (in response to kulbhushanc)

Sorry for the delayed reply. More than likely the schemaPattern is filtering too much. You can try relaxing your import properties, such as removing the tableTypes property or using a more general schema filter "A%"

You can update the logging config to adjust a particular log level for a given context: Logging · Teiid Documentation
Actions
4. Re: Unable to connect to oracle database when user having no access to sys.tables

uuliver Jul 20, 2017 10:52 PM (in response to shawkins)

if my filter is "A%" OR "B%" , how to set the tableNamePattern
Actions
5. Re: Unable to connect to oracle database when user having no access to sys.tables

shawkins Jul 21, 2017 9:10 AM (in response to uuliver)

Unfortunately JDBC DatabaseMetaData does not supports only LIKE match strings, which doesn't support or. Instead you would use a regular expression in excludeTables JDBC Translators · Teiid Documentation - (?!(A|B)).*
Actions
6. Re: Unable to connect to oracle database when user having no access to sys.tables

uuliver Jul 23, 2017 9:36 PM (in response to shawkins)

Thank you very much It's ok
Actions

Go to original post