ORA-00942 with read only account

ORA-00942 with read only account

Postby krwyth » Mon 10 Jan 2011 22:48

We are using dotconnect for oracle and had to move to a read only Oracle user for our application per company policy.

Even though the account has permissions to read all the tables and views (the only thing it needs to do for this app) we are getting ORA-00942: table or view does not exist.

Snippet from our edmx:


Snipped from web.config:


Any ideas on this?
krwyth
 
Posts: 6
Joined: Mon 10 Jan 2011 22:40

Postby krwyth » Mon 10 Jan 2011 23:30

I should note that we are moving into another environment also for testing.

Could this be a licensing issue?
krwyth
 
Posts: 6
Joined: Mon 10 Jan 2011 22:40

Postby AndreyR » Tue 11 Jan 2011 11:10

The reason of the problem seems to be the Schema name Schema="SCHEMA_OWNER" />
Remove the Schema element and everything should work correctly.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby krwyth » Tue 11 Jan 2011 15:38

I still get an error after removeing the 'Schema=' property. Although this time it is a:

System.ServiceModel.DomainServices.Client.DomainServiceFault

Pardon my lack of oracle expertise but doesn't the application need to know the name of the schema that it is querying from?

Is there another part of the edmx or connect string that this is specified?
krwyth
 
Posts: 6
Joined: Mon 10 Jan 2011 22:40

Postby AndreyR » Tue 11 Jan 2011 16:34

If Entity Set does not have the Schema attribute specified, the current schema is used.
As for the DomainServiceFault class, there should be more information concerning this error, this is a general class just wrapping the communication error.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby krwyth » Tue 11 Jan 2011 17:02

It turns out there were some hardcoded non-EF queries which didn't specify the schema, so they failed when the user was different than the schema owner.

The Schema="SCHEMA_OWNER" is needed for our EF queries. Thanks
krwyth
 
Posts: 6
Joined: Mon 10 Jan 2011 22:40

Postby krwyth » Tue 11 Jan 2011 21:40

Well I thought this was resolved it works with the new account locally, but when I move to the test environment, I get this now:

error 0175: The specified store provider cannot be found in the configuration, or is not valid.. ---> System.Data.MetadataException: Schema specified is not valid. Errors: OracleEntityModel.ssdl(2,88) : error 0175: The specified store provider cannot be found in the configuration, or is not valid.
krwyth
 
Posts: 6
Joined: Mon 10 Jan 2011 22:40

Postby krwyth » Tue 11 Jan 2011 23:01

The app works after I installed dotconnect on the test server. However, we probably can't do this in production.

Pardon my ignorance but what's the best way to include our needed dotconnect binaries in our application deploy without installing the full package on the server?
krwyth
 
Posts: 6
Joined: Mon 10 Jan 2011 22:40

Postby AndreyR » Wed 12 Jan 2011 10:03

You should put the Devart.Data, Devart.Data.Oracle, and Devart.Data.Oracle.Entity assemblies in the Bin folder of your application (or place these assemblies in Global Assembly Cache).
Make sure that your application is licensed correctly (take a look at the Licensing article for more information).
Register the dotConnect for Oracle provider as well:
Code: Select all

 
   
         description="Devart dotConnect for Oracle"
     type="Devart.Data.Oracle.OracleProviderFactory, Devart.Data.Oracle,
       Version=6.0.70.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
 


This information is available in the Deployment article.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bartdk » Fri 25 Feb 2011 10:14

Even after removing the Schema=[schema owner] we still get the error telling us the table does not exists :

Code: Select all
Devart.Data.Oracle.OracleException: ORA-00942: table or view does not exist


We are currently unable to re-use an entity framework project on multiple databases, because of the hardcoded schema.
bartdk
 
Posts: 2
Joined: Fri 15 Jan 2010 12:55

Postby AndreyR » Fri 25 Feb 2011 11:38

Please check that your model does not contain Defining Queries, as schema can be hard-coded in these elements as well.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle