9 Replies Latest reply: Jun 14, 2014 10:52 AM by Mike Kutz RSS

    Accessing data via REST in more than one schema in ORDS

    keywee

      Hi All,

       

      ORDS 2.0.7

      Centos 6

      Oracle 11gr2 XE (one instance)

      APEX 4.2.5

      Tomcat

      Apache

       

      I'm not sure if I am just missing something or whether this is expected behaviour, but I am trying to create a RESTful service that accesses a table in a schema that is not the original one that I provisioned with my APEX workspace, and I keep getting a 500 Internal Server Error with the following detail:


      Error during evaluation of resource template: GET er/alerty/, SQL Error Code: 942, SQL Error Message: ORA-00942: table or view does not exist

      (er/alerty is the specification for my REST handler)


      The hr example service works perfectly, and if I amend my new RESTful service (er/alerty) to select from the emp or dept tables, then that also works.

      (I have also tried prefixing my query with the schema name but I get the same error).


      I have added the schema to the workspace through apex_admin, and I can run an APEX application that accesses the tables that I'm trying to expose through REST.


      I also granted the alter user <schema owner> grant connect through apex_rest_public_user; but it made no difference.

      I noticed that EMP and DEPT (for the HR example) have both been created in the originally provisioned schema, and if I create a table in that schema then I can also access it with my RESTful service.


      So is this expected behaviour and can I only access one schema with REST? (and if so where is that specified?)

      I presume I could create a completely separate workspace with the new schema but I'd rather not.

       

      Does anyone know the answer to this?

       

      cheers,

      Keywee

       




        • 1. Re: Accessing data via REST in more than one schema in ORDS
          Mike Kutz

          I believe REST services accesses data as the user APEX_REST_PUBLIC_USER (or whatever the ORDS is configured for)

          Try granting SELECT privilages to that user.

           

          MK

          • 2. Re: Accessing data via REST in more than one schema in ORDS
            keywee

            Hi Mike,

             

            Thanks for your reply.

             

            I had thought that this was the purpose of the following statement, however after reading some more on the web I'm not so sure anymore:

            alter user <schema owner> grant connect through apex_rest_public_user;


            Obviously I can use a workaround to access the tables in any schema other than the one that seems to be accessible to REST although this would be a nuisance  as I would have to do it on an individual object basis (which I believe is what you are suggesting?).


            I am obviously missing something here.


            cheers,

            keywee

            • 3. Re: Accessing data via REST in more than one schema in ORDS
              Mike Kutz

              Did this work?

              GRANT SELECT ON <schema>.<table> TO APEX_REST_PUBLIC_USER;

               

              MK

              • 4. Re: Accessing data via REST in more than one schema in ORDS
                keywee

                Hi Mike,

                 

                No that does not work.

                 

                I am currently using a similar workaround:

                create public synonym table1 for schema1.table1;

                grant select on schema1.table1 to <the only schema that REST can currently see>;

                 

                Then when I do "select * from table1" in my REST Resource Handler it all works.

                 

                The issue seems to be centred around this one schema that REST is able to see, but I cannot understand where this relationship is maintained, unless it is somehow configured through the "alter user grant connect" statement, which I have already tried to apply to schema1 but with no luck.

                 

                cheers,

                keywee

                • 5. Re: Accessing data via REST in more than one schema in ORDS
                  Mike Kutz

                  Code

                  hmm....you shouldn't need the public synonym

                   

                  After giving the appropriate grant, this should work:

                  select * from schema1.table1

                   

                  (Some people, including myself, consider it "bad programming" if you leave out the schema name.)

                   

                  Security

                  It sounds like you have configured your REST Services to run as <the only schema that REST can currently see>.

                   

                  From a security standpoint, you want that user-schema to have very limited access to the database and the data within it.

                  As such, the schema that owns the tables and <the only schema that REST can currently see> needs to be two different schemas.

                  This is nothing more than  basic "good security practices".

                   

                  Yes.  That means you'll need to manually GRANT SELECT ON each table TO <the only schema that REST can currently see>

                  However, this is just one of the required steps for building a secure application.

                  hint:  use a ROLE...

                   

                  MK

                  • 6. Re: Accessing data via REST in more than one schema in ORDS
                    keywee

                    Hi Mike,

                     

                    Thanks for your response.

                    This is currently a test environment and not a production system so lets leave aside the concept of good and bad practices for the moment.

                     

                    I am not looking for a quick solution, I am trying to establish how this is supposed to be working.

                     

                    So when you say:

                    "After giving the appropriate grant, this should work:

                    select * from schema1.table1"

                     

                    Where does it say that in the Oracle documentation?  Is that how REST is supposed to function when dealing with multiple database schema's?

                    I haven't come across anything that says "Grant specific tables to the APEX_REST_PUBLIC_USER" to make them accessible to REST.  Nor have I seen anything that says you should have a minimal schema that points to other schema's.  Surely this is the functionality that is intended to be provided by APEX_REST_PUBLIC_USER and the other APEX/REST Accounts?  After all they are just schema's too.

                     

                    And when you say

                    "It sounds like you have configured your REST Services to run as <the only schema that REST can currently see>."

                    Do you know how this has occurred and where there are any other options for seeing it or changing it?

                     

                    If REST can only see one schema directly for a given APEX workspace (without adding grants to other db schema's) then fine - if that's how it works then so be it, but I would like to know where this particular configuration is stored, and I'd like to definitively understand that this is the expected functionality.

                     

                    cheers,

                    keywee

                    • 7. Re: Accessing data via REST in more than one schema in ORDS
                      Mike Kutz

                      keywee wrote:


                      So when you say:

                      "After giving the appropriate grant, this should work:

                      select * from schema1.table1"

                       

                      Where does it say that in the Oracle documentation?

                       

                      It says so in This Document

                      If you haven't read it, you need to.

                       

                      MK

                      • 8. Re: Accessing data via REST in more than one schema in ORDS
                        keywee

                        Mike,


                        I'm well aware of how grants and roles work.  That is not what this thread is about.

                        This is a specific question about ORDS and how it works.

                         

                         

                        To keep it very simple:

                        Does RESTful services use a single schema for a given APEX workspace that is the only schema from which data can be selected without requiring any further qualification.  E.g. select * from emp; ?  And if so where is this defined?

                         

                         

                        Can anyone who has actually implemented the same or similar environment that I have please answer this?

                         

                         

                        cheers,

                        keywee

                        • 9. Re: Accessing data via REST in more than one schema in ORDS
                          Mike Kutz

                          Background Information

                          Here is a breakdown of all schemas involved when running APEX and ORDS:

                          APEX is nothing more than a set of PL/SQL and other database objects that live in a locked schema with a name in the format of APEX_{version}

                          ORDS uses Proxy Users to make/cache/keep multiple connections to a database (for fast response times).  ORDS does the equivalent of "su - {other schema}" before it runs any SQL.

                          APEX Applications will run SQL code as its configured "Parsing Schema".

                          APEX Workspace can be completely different than what the Application(s) run as.  (I'm assuming the two schemas are the same for you)

                          RESTful Services will run SQL code as APEX_REST_PUBLIC_USER (by default).  I believe this is defined by APEX installation, not Workspace.

                          Security conscious developers will have all data in a completely different schema than any of the above.  Others will store it in the Application's Parsing Schema.

                           

                          It seem like you can change which user RESTful Services will run as by adding/configuring "URL Mappings" for ORDS.

                          This configuration can only be viewed/changed via Oracle SQL Developer. (separate, but free, download)

                           

                          Does RESTful services use a single schema...

                          By default:  Yes, all RESTful Services will run as the same schema.

                          But, I believe you can change which schema it uses (based on URL) by configuring ORDS.

                           

                          ..for a given APEX workspace ..

                          As far as I can tell, the default RESTful Services account is linked to the APEX Installation, not APEX Workspace.

                          Also, see URL Mappings.

                           

                          ..that is the only schema from which data can be selected without requiring any further qualification.

                          When running RESTful Service, you are connected as user REST_PARSER while your data is in schema WORKSPACE.

                          As such, all normal Oracle Security rules apply.

                          Therefore, in order to use WORKSPACE.Table1 in a RESTful Service,you must

                          GRANT SELECT ON WORKSPACE.Table1 TO REST_PARSER
                          

                           

                          How you access that table within the SQL for a RESTful Service is up to you and your coding style.

                          (i.e. via synonym or via fully qualified name)

                           

                          personal opinion on coding standards

                          Synonyms just muck things up in the long run.  You need to try to keep their creation to a minimum.

                          As such, it becomes important that you always fully qualify your database objects.

                           

                          MK