Sunday, July 16, 2017

Set up your MCS (development) environment: database creation policies

As mentioned in a previous post (Setup your MCS development environment: MCS custom code test tools, MCS is a cloud native platform that offers several platform APIs. One of these APIs is the database API.

It consists of two parts: 
  1. Database access. This is used by mobile applications and can only be executed from within custom code
  2. Database management. This allows you to create tables, remove tables etc. 
The first question you might ask is 'why on earth would I want to create tables in MCS, don't we have database cloud service and other sources for that?!?' 

Let me start by saying I agree with that. However, in this project we are starting with a simple API and we want to make them available as quickly as possible. The data will move (eventually) to the proper back-end system and in MCS we will use a connector to access this data that will be exposed on the Oracle Service Bus. However, at the moment the system does not contain the data and the Oracle Service Bus is not exposing services for this particular system yet. 

To save cost, minimize complexity and maximize time to market, we decided to use the database platform API. 

You can create database tables on the fly, using the Database_CreateTablesPolicy environment setting. This will cause a table or a column to be added or resized when you insert a row using custom code if it does not exist yet.

According to the documentation the following values can be used: 
  • allow: enables calls from custom code that perform implicit operations; 
  • explicitOnly: disables implicit calls from custom code;
  • implicitOnly: only implicit creation of database tables using custom code is allowed, the database management api can't be used;
  • none: curtails implicit calls from custom code.
This documentation is a bit unclear so let me elaborate on that:

ValueUse API in custom codeUse implicit calls in custom code 
allowyesyes
implicitOnlynoyes
explicitOnlyyesno
nonenono

These values are used to control the privileges for custom code, it does not control calling the database management API from outside of MCS (postman, curl etc).

There are several disadvantages to this approach:
  1. You can accidentally end up with multiple columns because of spelling errors ('address' and 'adress' for example);
  2. When unit testing custom code with 'faulty' data, instead of failing with the error you would get into your production environment (which is recommended by Oracle, to switch it off in production) you create new columns and the test fails with a different error (if it fails at all);
  3. We use the environment that we are working as a production environment. 
We decided to use "explicitOnly" and use the REST APIs to create, update and remove tables with Postman. However when I used one of the APIs I got the following response:


{
    "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1",
    "status": 403,
    "title": "Forbidden",
    "detail": "403 - Forbidden",
    "o:ecid": "005L4J3QBg63j4C_nDWByZ0000x^0000yX, 0:3",
    "o:errorCode": "MOBILE-15229",
    "o:errorPath": "/mobile/system/databaseManagement/tables"
}

When creating a database table from outside the custom code (using the REST API and postman for example), you  need to call it with a user that has the role Mobile_DbMgmt. Unfortunately, there is no easy way to check your role from inside MCS, as can be seen in this picture

No easy navigation to inspect or change your role from within MCS
So, I opened a new tab and navigated to cloud.oracle.com and signed in again. This brought me to my service dashboard, and offers the opportunity to manage users, using the "users" button in the upper right hand corner.
Click users and find yourself. Check your roles and add [environment name] Mobile Database management to the roles.

Now I was able to create the table 😊.

NB: According to the documentation the default value is 'allow'. In our instance the value was set to 'explicitOnly', so make sure you check the value when you use the database platform API. 

  1. Login to MCS
  2. Click on "Administration"
  3. Scroll to the bottom of the page
  4. Click Export and save the policy file.
  5. Edit the policy (if needed)
  6. Upload policies.properties
The setting is applied instantly.

No comments:

Post a Comment