4 Replies Latest reply on Dec 11, 2019 1:44 PM by Leandroide

    Assign schema roles to ORDS Client


      We have organized our DB in different schemas, something like CARS, HOUSES, OWNERS, etc.


      Now, we need that our oAuth Clients, could perform actions by using our REST services, in one or more SCHEMAS.


      I ve already assign role to client, but it is not working...



           oauth.grant_client_role (

                p_client_name => 'INSURANCECLIENT',

                p_role_name  => 'oracle.dbtools.role.autorest.CARS.BRANDS'





      Where CARS is the schema, and BRANDS the AutoREST enabled table.


      Then if I execute query:




      the CARS.BRANDS role, is not being shown.

        • 1. Re: Assign schema roles to ORDS Client

          The privs have to be local to the schema.


          So the best you could do is create a VIEW in the same schema, that queries the table, then auto-enable that view, and add the priv to your cilent.

          • 2. Re: Assign schema roles to ORDS Client

            Hi Jeff, thanks for your answer. We made a Synonym because we need to update, delete and insert but it is not being listed in APEX to be enabled as an autoRest resource. How should I grant it? As which user should I create it?


            Coming back to schemas and privileges, what could I do to give security to my endpoints in other schemas?


            My schemas CAR, HOUSES, are exposed through the following Uris: /ords/car(schema)/module/template, in a way that we could segment our resources properly.


            I know I can centralize everything in only one schema, but in that way, I am losing the different endpoints + schema combinations, and the auto rest feature.


            Thanks in advance

            • 3. Re: Assign schema roles to ORDS Client

              you need an application schema, with all of your endpoints based there, then you can have a single oauth client with the appropriate privileges assigned


              otherwise you need one oauth client per schema

              • 4. Re: Assign schema roles to ORDS Client

                Are you going to think about making OAuth clients cross-schema in the future? Cause many of our Clients will consume information from different schemas, and I can not make them request another token when changing from one schema to another. It is complex to maintain in the near future and not too agile regarding the operational point of view.


                I think that could be a great advantage considering big enterprise companies like this one that runs their different applications in 1 huge DB.


                Thank you very much for your help