1 Reply Latest reply on Nov 26, 2018 9:28 AM by Peter de Vaal

    How to deploy ORDS objects via DevOps (what grants should it have)

    Alexus67

      Hi Oracle Gurus!

      We have a set of dev - test - UAT - production environments,

      we have created a set of ORDS objects on dev (module, templates, handlers, etc) in HR schema, for example:

       

      BEGIN

        ORDS.define_module(

        p_module_name => 'testmodule2',

        p_base_path => 'testmodule2/',

        p_items_per_page => 0);

       

        ORDS.define_template(

        p_module_name => 'testmodule2',

        p_pattern => 'emp/');

       

        ORDS.define_handler(

        p_module_name => 'testmodule2',

        p_pattern => 'emp/',

        p_method => 'GET',

        p_source_type => ORDS.source_type_collection_feed,

        p_source => 'SELECT * FROM emp',

        p_items_per_page => 0);

       

        COMMIT;

      END;

      /

       

      Now we need to create a script containing all these objects - and run this script under special DevOps oracle database user on test , UAT and prod environments.

       

      The question is - what grants should DevOps database user  have to be able to run the script - and create all that ORDS objects so that they will behave correctly for HR schema?

      Second question is - which schema will that objects belong to:

      a) script executor (i.e. DevOps)

      b) someone else (how to configure that)?

       

      One option could be to create stored procedure under target HR schema containing all ORDS *_Define calls:

      Create procedure HR.ORDS_DEFINES as …

      execute immediate:
      BEGIN
      ORDS.define_module(
      p_module_name => ‘testmodule2’,
      p_base_path => ‘testmodule2/’,
      p_items_per_page => 0);

      ORDS.define_template(
      p_module_name => ‘testmodule2′,
      p_pattern => ’emp/’);

      ORDS.define_handler(
      p_module_name => ‘testmodule2′,
      p_pattern => ’emp/’,
      p_method => ‘GET’,
      p_source_type => ORDS.source_type_collection_feed,
      p_source => ‘SELECT * FROM emp’,
      p_items_per_page => 0);

      COMMIT;
      END;
      /

      – then:
      1) create that procedure under DevOps user – it will be created in target schema HR
      2) run that procedure under DevOps user – it will be run with owner righs as if HR runned it, so we will have ORDS objects created on behalf of HR

      Or is there a better way?

        • 1. Re: How to deploy ORDS objects via DevOps (what grants should it have)
          Peter de Vaal

          Hi Alexus,

           

          I think your question is quite valid. As Jeff states you have to connect as HR to do it, but I think you rather would have an administrative user (DevOps) to do the task.

          It looks as if this is not possible at the moment, so I would suggest you create an enhancement request (add an owner parameter to all ORDS plsql units, and add an ORDS_ADMIN role to grant to an administrative user that allows creation of REST modules on other schemas), because I agree it would be very handy in a DevOps/CI environment.