6 Replies Latest reply: Dec 10, 2012 8:22 AM by 979001 RSS

    Lightswitch-I can’t see tables created bya different restricted Oracle user

    934464
      I am new to Lightrswitch and connecting to Oracle through VS - we usually have a middle tier to do this. We are looking at some rapid CRUD application development so I am looking into lightswitch.

      However I have an issue:

      I have two oracle users. One has full access to the tables and the other has restricted access. When attaching my Lightswitch application to the database if I use the one with full access it works a treat. However if I use the DB user that has restricted access I can not see any of the tables (even though I can see them fine logged in under the same restricted user in toad ?). Upon connection I have gone into the filters tab and selected the unrestricted schema under the "Displayed Schemas" option to no avail.

      My version of Oracle is 9.2.0.7

      I also have Oralce OLEDB provider - ODAC92070.exe

      I have oracle developer tools ODTwithODAC1020221

      Any help would be much appreciated...
        • 1. Re: Lightswitch-I can’t see tables created bya different restricted Oracle user
          hitgon
          generally table is not created inside the granted user but you can find the grant table using the schemaname.tablename from granted schema
          or you can create the local synonym for that other schema object.

          Edited by: hitgon on May 2, 2012 2:24 PM
          • 2. Re: Lightswitch-I can’t see tables created bya different restricted Oracle user
            Alex.Keh .Product.Manager-Oracle
            Interesting that you got ODTwithODAC1020221 to work with LightSwitch. That version of ODAC is several years old and not certified with VS 2010 upon which LightSwitch shares a common code base with. In general, I would recommend using ODAC 11.2 R4 (11.2.0.3), which is certified with VS 2010.

            Officially, Oracle is not certified with LightSwitch, although we're currently investigating this possibility. Let me ask you when you cannot see the tables, do you mean running your LightSwitch application with ODP.NET or do you mean using the LightSwitch IDE tools? I assume you mean the latter. If so, then that particular user has no problem accessing the data when run in regular .NET application, correct?
            • 3. Re: Lightswitch-I can’t see tables created bya different restricted Oracle user
              934464
              Thanks for the reply.

              I will install the version you reccomend and see if it makes a difference.

              Your assumption is correct in that I can't see the tables for selection in the IDE. However after posting the question and a bit of internet searching I found where Lightswitch saves the DB connection string. It is in a server generated file (that is not suppose to be edited as it can be overwritten) ServerGenerated Config.xml. If I change the connection string in here I can access the tables...even though they are not available to view in the IDE. This gets over my issue in that I can create the application under an unrestricted user and change the connection string on deployment.

              Now I have to see if I can call a package to give me permissions under this restricted role !
              • 4. Re: Lightswitch-I can’t see tables created bya different restricted Oracle user
                WGM4321
                I have a similar problem with using ODAC 11.2 R4 (11.2.0.3).

                As a matter of policy, objects are never owned by general users. So when I connect as my user in Lightswitch, no objects are displayed.

                However, if I add a connection to the server explorer, alter the filter to include the owner of the objects, they display in the server explorer.

                If I follow the EXACT same sequence to add the external connection in the Lightswitch dialog, the filter is seemingly ignored and as I don't own any objects, none are shown. Additionally, if I "< Back" to the connection dialog, the filter has been reset to just my objects.

                Seems like the filter mechanism in the Lightswitch dialog is being ignored.

                Any other reports of this behavior, any workarounds or any idea when this problem will be fixed? It is a showstopper for me.
                • 5. Re: Lightswitch-I can’t see tables created bya different restricted Oracle user
                  Christian.Shay -Oracle
                  951672 wrote:
                  I have a similar problem with using ODAC 11.2 R4 (11.2.0.3).

                  As a matter of policy, objects are never owned by general users. So when I connect as my user in Lightswitch, no objects are displayed.

                  However, if I add a connection to the server explorer, alter the filter to include the owner of the objects, they display in the server explorer.

                  If I follow the EXACT same sequence to add the external connection in the Lightswitch dialog, the filter is seemingly ignored and as I don't own any objects, none are shown. Additionally, if I "< Back" to the connection dialog, the filter has been reset to just my objects.

                  Seems like the filter mechanism in the Lightswitch dialog is being ignored.

                  Any other reports of this behavior, any workarounds or any idea when this problem will be fixed? It is a showstopper for me.
                  We are aware of the problem and discussed it at length with Microsoft. They have said that they are looking into fixing it in some future release. Unfortunately, the fix did not make it into the Visual Studio 2012 RTM.

                  If you have a support contract with MS, you could make it known to them that you urgently require a fix and perhaps that could speed things along.

                  I don't know of a workaround, other than to log in as the schema owner (which is probably a non-starter for you).
                  • 6. Re: Lightswitch-I can’t see tables created bya different restricted Oracle user
                    979001
                    Does anybody happen to have an update on this issue? I have tried everything I can think of to no avail. I was able to successfully use WCF RIA connected to my Oracle DB as a Data Source in Lightswitch; however, CRUD isn't entirely possible in my case b/c of limitations on how my Oracle DB Server can be configured w/ respect to Dedicated vs. Shared mode. Essentially ... although I am able to read data and display in my Lightswitch screens, I am unable to create, update, or delete records due to an ORA-24777 error.

                    For tables created in my own schema, all is well when directly adding the Oracle DB as Data Source. All CRUD operations work successfully. Using the required schema's user is a NO-GO.

                    Thanks in advance for any updates or advice provided.