DbTwig - Middle-Tier Adapter

Introduction

TL;DR: AsterionDB’s open-source project, DbTwig, is the framework we utilize to build micro-services at the data-layer! Micro-services at the data-layer give you the best of both worlds, distributed development and a monolithic deployment mechanism.

DbTwig is a middle-tier framework that facilitates the development and deployment of micro-services at the data layer. DbTwig grew out of the development we undertook in building the AsterionDB Converged Computing Platform (AsterionDB). AsterionDB is a software development and integration platform that merges all data (structured & unstructured) and business logic in an Oracle Database.

The natural result of moving assets out of the middle-tier and into a database is an API based service that is implemented at the data-layer. Furthermore, with no local resources, middle-tier compute nodes become cookie cutter devices. They serve as an elastic security isolation layer and are only responsible for the protocol translation between HTTP and SQL.

Your data-layer micro-service can be called by an external HTTP based RESTAPI client or internally by another data-layer micro-service. We used this concept to great effect when building AsterionDB. We have an ICAM module implemented as a data-layer micro-service that is part of DbTwig. AsterionDB calls this micro-service internally through the database to manage a users session.

DbTwig and AsterionDB

DbTwig emerged as a spin-off from the development we engaged in for our namesake product – AsterionDB. AsterionDB merges all data types (e.g. unstructured & structured) and all business logic at the data-layer. DbTwig is the component that makes it easy to create micro-services at the data-layer where all of your business logic is in the database. AsterionDB itself is the technology that makes it seamless and easy to manage unstructured data in the database.

It is important to know how DbTwig and AsterionDB interoperate together to provide a platform that you can use to integrate into existing or build new applications.

Is This All Worth It?

Let’s be frank here – DbTwig and AsterionDB, for that matter, represent a paradigm shift. So, you may be wondering, is this all worth it? Well, to be honest, in absence of the gaps that AsterionDB fills in the data-layer micro-service architecture, probably not. The reasoning is simple: if you have all of your logic in the database, you are going to want all of your data there too. Similarly, if you have all of your data in the database, you are going to want all of your logic there too. As you can see, one goes hand-in-hand with the other. There’s no point in doing this ‘half-way’.

The innovation that we bring to the table is the technology that makes this easy to do! The bottom line up-front is that you have to be able to incorporate all business logic and work with any data type in the database. Here’s how we do it.

AsterionDB’s Framework Components

AsterionDB provides three fundamental components that make it easy manage unstructured data and incorporate all business logic in the database.

DbPlugin Server – Extending Business Logic in the Database

The DbPlugin Server allows us to easily extend the logical capability of the database’s native language – PL/SQL. This is actually a critical component when it comes to integrating all business logic and data-types in the database.

With almost no exception enterprise applications have to integrate to foreign logic. In the world of PL/SQL, foreign logic is anything that can not be coded directly in the database. This ‘external logic’ can be:

External API’s from SaaS providers
Federated programs
Libraries and scripts

The DbPlugin Server is a general-purpose platform that makes it easy to integrate these types of external services into your data-layer micro-service.

In regards to the unstructured data that AsterionDB stores in the database, being able to integrate external logic that understands these data-types is a powerful capability. This allows you to seamlessly work with any data-type directly in the database.

DbObscura – A Posix Compliant File System Gateway

AsterionDB’s file system gateway, DbObscura, allows you to easily integrate file-based programs into your micro-service.

DbObscura provides a POSIX compliant interface that forwards all file-IO operations to AsterionDB. We do this by generating a random filename on-the-fly through our API for your programs to use.

In essence, your program will make a call to the AsterionDB API to generate a filename each time an object is to be accessed within a workflow. The filenames can be regulated and expired along multiple dimensions such as specified date or number of operations (e.g. open/close).

DbStreamer – An HTTP Compliant Streaming Server

DbStreamer is a purpose-built streaming server that transmits unstructured data using the HTTP protocol. DbStreamer is not a web-server. It is specifically built to satisfy the requirement to stream unstructured content directly from your database and AsterionDB.

With DbStreamer you can use a static or dynamically generated URL to access content in the database.

DbTwig – A Converged Middle-Tier Architecture

DbTwig is a crucial technology component that enables the creation of a data-layer micro-service. Its primary role is to make it easy to incorporate all of your logic in the database. DbTwig has three primary components:

A middle-tier listener that receives HTTP requests and converts them into an SQL request
A package in the database that receives the incoming SQL request
Tables in the database that are used by DbTwig to broker an API call to a specific micro-service

Here is a diagram of the DbTwig Architecture:

By moving all logic to the database, DbTwig is able to present what we call a single-point API to its clients. Every other API design that you will encounter provides multiple entry points for a client to call. In contrast, AsterionDB presents only one! Referring to the diagram above, here’s how it works!

DbTwig’s middle-tier receives an incoming request from a client. The middle-tier adds a few additional parameters and transforms the request into an SQL transaction that will be received by the database.
The middle-tier makes a call to the call_restapi function in the database. This function accepts a JSON string and returns a JSON string.
The call_restapi function is merely a front-end to a corresponding function in the db_twig package. All it does is delegate the call to the call_restapi function in the db_twig package. This little trick allows us to hide the details of the db_twig package and underlying micro-services from the middle-tier connection.
The db_twig package consults its service-map table to determine which micro-service is being called.
The db_twig then verifies that the requested entry point is valid by checking the micro-service’s entry-point table.
The db_twig package will execute the micro-service’s session validation procedure.
The db_twig package will execute the requested function or procedure that maps to the micro-service’s entry-point
If the entry-point maps to a function, it will return a set of data in JSON format.
If the entry-point encounters an error, it will return a PL/SQL exception.
The db_twig package returns the result of the call to the micro-service’s entry-point to the client application.

This architecture has a significant impact on security. What it effectively means is that we only have to expose a single function for the middle-tier to call. Here is the function’s signature:

				
					create or replace function call_restapi
(
  p_json_parameters                   clob
)
return clob...

Here is an example of how we call the entry-point to dbTwig:

				
					declare json_data clob := null; begin json_data := call_restapi(:jsonParameters); :jsonData := json_data; end;

With our interface between the middle-tier and the database reduced to a single function call, we are now able to greatly restrict what the middle-tier connection can do. In fact, all it can do is call the call_restapi function. It can not see any tables, packages or any other schema element contained within DbTwig.

Furthermore, it can not see any of the underlying schema elements that make up your micro-service! We have achieved a primary goal in security – we are hiding the implementation details!

User & Schema Provisioning

AsterionDB utilizes a minimal user and schema provisioning strategy that greatly enhances security. We do this by tightly controlling the ability to connect to the database. This allows us to isolate a micro-service’s data and logic from the middle-tier connection used to connect to the database.

When AsterionDB is installed, we create users for DbTwig and the micro-services that make up AsterionDB. These users are not granted connect privileges. Without connect privileges, these users serve as organizational constructs that allow us to isolate our data and logic.

We also create a ‘runtime‘ user that can connect to the database. The runtime user is what we use for the connection from the middle-tier. This runtime user is only granted permission to call our single-point API function. The runtime user can not directly select from tables, alter data or create any objects. All it can do is call the single-point API.

You can examine the AsterionDB and DbTwig installation scripts to see how we are leveraging the DBA role to install AsterionDB. The script is designed to be run by the DBA and it handles the creation of all schemas and objects.

For your micro-service, you will create a user to store your service’s schema objects. How that user is created on a non-development (i.e. production, test, eval) system differs from how the user is created in a development system.

The best strategy for designing your installation scripts for production and development systems is to have them create a ‘production’ user first. Remember, your production users are not supposed to be able to connect to the database.

But, when you are developing, you need full access to the database to create objects, write your logic and so forth. Therefore, you can execute a ‘developer’ script that will grant connect privileges and any other necessary permissions (e.g. create procedure) that you will need for development.

Creating Users For A Non-Development System

The code snippet that follows demonstrates how we create our user and schema for DbTwig. This is essentially the same process you would follow when creating a user and schema for your micro-service.

Notice how we are using the DBA account to create the DbTwig user and establish a default quota on a tablespace.

We then alter the session’s current schema. This allows us to create objects directly in DbTwig’s schema.

				
					connect &dba_user/"&dba_password"@"&database_name";
 
declare
    l_sql_text                        clob;
    l_default_tablespace              database_properties.property_value%type;
begin
    select  property_value
      into  l_default_tablespace
      from  database_properties 
     where  property_name = 'DEFAULT_PERMANENT_TABLESPACE';
    l_sql_text := 'create user &dbtwig_user';
    execute immediate l_sql_text;
    l_sql_text := 'alter user &dbtwig_user quota 50M on '||l_default_tablespace;
    execute immediate l_sql_text;
 
end;
.
/
alter session set current_schema = &dbtwig_user;
create sequence id_seq minvalue 1 maxvalue 999999999999 cycle;
...
...

Creating Users for a Development System

The process for creating a user in your development system

				
					connect &dba_user/"&dba_password"@"&database_name";
 
declare
 
    l_sql_text                        clob;
    l_default_tablespace              database_properties.property_value%type;
 
begin
 
    select  property_value
      into  l_default_tablespace
      from  database_properties 
     where  property_name = 'DEFAULT_PERMANENT_TABLESPACE';
 
    l_sql_text := 'create user &&tutorials_user identified by "&&tutorials_password"';
    execute immediate l_sql_text;
 
    l_sql_text := 'alter user &&tutorials_user quota 50M on '||l_default_tablespace;
    execute immediate l_sql_text;
 
end;
.
/
 
rem
rem  Setup the DbTwig Example user so that it can create references and make calls to the AsterionDB dgBunker 
rem  service
rem
 
grant execute on &&vault_user..dgbunker_service to &&tutorials_user;
create or replace synonym &&tutorials_user..dgbunker_service for &&vault_user..dgbunker_service;
 
grant read, references(object_id) on &&vault_user..vault_objects to &&tutorials_user;
create or replace synonym dbtwig_example.vault_objects for asteriondb_dgbunker.vault_objects;
 
rem
rem  Setup the DbTwig Example user so that it can make calls to the DbTwig API
rem
 
grant execute on &&dbtwig_user..db_twig to &&tutorials_user;
create synonym &&tutorials_user..db_twig for &&dbtwig_user..db_twig;
 
alter session set current_schema = &&tutorials_user;
 
create sequence id_seq minvalue 1 maxvalue 999999999999 cycle;

User Creation For a Micro-Service – Development System

Creating a user for a micro-service in a development instance follows the same pattern as when creating a user for a non-development system with the addition of granting the ‘connect’ and any other needed privileges to the user (e.g. create table). For example:

grant create session, create table, create procedure to &&tutorials_user

Best Practice – Restrict Connect Privilege

This is very important!!! We can not emphasize this enough. Restricting connect privileges to your database is central to implementing the advanced DbTwig/AsterionDB security model.

As you can see from the examples above, the ability to connect to the database and create a session is highly restricted. In a properly configured system the only database users that can connect are the DBA and our ‘runtime’ users.

What this effectively means is that there is no way to directly get to the data without going through the API – or being the DBA. This alone represents a huge increase in security.

Remember that the DBA is the only user account that can change the business logic. Therefore, by monitoring DBA access to the database, you can be assured that:

Your business logic will not be altered
All data access will go through audited business logic

Building a Data-Layer Micro-Service

What we hope to accomplish in this section is to lay the conceptual groundwork for you to understand how DbTwig and data-layer micro-services work. We recommend that you read through this section first and then take a look at the DbTwig Demonstration Application. You will see all of these concepts in-action when you examine the demo application.

Move SELECT Statements to the Database

For years, programmers have used stored logic in the database to access and modify information. However, it is difficult to return a ‘set’ of information from a function in the database. That is why we typically execute our SELECT statements from the middle-tier.

But, by using JSON, we are able to rewrite our SELECT statements as functions that return a JSON string. In addition, we can use a JSON string for all of our input parameters. Now our function can accept a JSON string and return a JSON string as CLOB variables.

Use JSON for Parameters and Returned Data

You will see in the following example how we are extracting parameter values from the incoming JSON string. We are using JSON’s self-describing capability to encapsulate our set of data so that it can be easily returned as a CLOB by the function.

It is important to note how we are extracting the JSON parameters into database typed variables when they are declared. Good programmers always verify incoming parameter types and value lengths as soon as possible.

Here’s an example:

				
					function get_maintenance_manual_detail
(
  p_json_parameters                 json_object_t
)
return clob
 
as
 
  l_manual_id                       maintenance_manuals.manual_id%type := db_twig.get_number_parameter(p_json_parameters, 'manualId');
  l_clob                            clob;
 
begin
 
  select  json_object(
            'manufacturer' is manufacturer,
            'inServiceFrom' is to_char(in_service_from, 'dd-MON-yyyy'),
            'maintenanceDivision' is maintenance_division,
            'revisionNumber' is revision_number,
            'maintenanceManualLink' is maintenance_manual_filename,
            'spreadsheetId' is spreadsheet_id,
            'assemblyPhotos' is get_major_assembly_photos(l_manual_id) format json
            returning clob)
    into  l_clob
    from  maintenance_manuals
   where  manual_id = l_manual_id;
 
  return l_clob;
 
end get_maintenance_manual_detail;

Pro Tip: You are not limited to creating JSON with a SELECT statement. The requirement is that your function returns a valid JSON string. How you go about constructing that JSON string is up to you!

Similarly, our procedures accept a JSON string of parameters. Procedures do not return any values, they either succeed or fail. Upon failure you can interface with DbTwig’s Error Logging micro-service or just allow normal exception processing to take place by calling raise_application_error.

An example of a procedure follows:

				
					procedure save_tech_note
(
  p_json_parameters                 json_object_t
)
 
is
 
  l_tech_note                       technician_notes.tech_note%type := db_twig.get_string_parameter(p_json_parameters, 'techNote');
  l_manual_id                       maintenance_manuals.manual_id%type := db_twig.get_number_parameter(p_json_parameters, 'manualId');
 
begin
 
  if l_tech_note is null then
   
    raise_application_error(-20000, 'Note can not be null');
     
  end if;
   
  insert into technician_notes
    (manual_id, tech_note)
  values
    (l_manual_id, l_tech_note);
 
end save_tech_note;

Your Data-Layer Micro-Service RESTAPI

So, with generic entry points for our logic, we can build an API for a data-layer micro-service that will be stored as a package in the database. The functions and procedures in this package will be called by the DbTwig middle-tier.

Using the code examples above, we can put them into a package:

				
					create or replace package restapi as
 
  function get_maintenance_manual_detail
  (
    p_json_parameters                 json_object_t
  )
  return clob;
 
  procedure save_tech_note
  (
    p_json_parameters                 json_object_t
  );
 
end restapi;

Here’s where the flexibility of JSON comes into play. The specification says, “give me a JSON string, I’ll give you back a JSON string”. That means DbTwig only has to figure out if it’s calling a function or a procedure and then parse the SQL statement accordingly.

That’s the start of your data-layer micro-service – a package in the database that you can expose to DbTwig and other schemas through synonyms and grants. It is important to remember that the API interface between your micro-service and the middle-tier (i.e. DbTwig) uses JSON strings for parameters and returned values.

Error Handling In Your Micro-Service

Your micro-service does not have to do anything other than throw an error when necessary to take advantage of DbTwig’s centralized error logging. As detailed below, error logging and is provided by DbTwig’s Error Logging micro-service.

The Middle-Tier Map Table

Each micro-service exposed to the outside world by DbTwig will have its own copy of the DbTwig middle_tier_map table. Entries in this table allow us to map external entry-points to the internal functions and procedures that satisfy an API request.

Each row in the table has columns for:

entry_point – the external entry point
object_type – the object type – function or procedure
object_name – the name of the object
object_group – the group that the object is a member of (for dev/ops use)
required_authorization_level – authorization required to call the entry point (none, guest, user, administrator)
allow_blocked_access – allow a blocked session to call the entry point

Following along with the example, two insert statements will be executed to map entry-points for get_maintenance_manual_detail and save_tech_note:

insert into middle_tier_map values (‘getMaintenanceManualDetail’, ‘function’, ‘restapi.get_maintenance_manual_detail’, ‘dbTwigExample’, ‘none’, ‘N’);
insert into middle_tier_map values (‘saveTechNote’, ‘procedure’, ‘restapi.save_tech_note’, ‘dbTwigExample’, ‘none’, ‘N’);

There are two scripts located in /home/asterion/asterion/oracle/dbTwig/dba that you will incorporate into your development tooling:

middleTierMap.sql – creates the middle_tier_map table
extractDbTwigData.sql – creates a script that you can use in your installation and update scripts to reset your middle-tier-map table

DbTwig’s Functional Components

Architecturally, DbTwig sits on top of your micro-service. It’s job is to expose your micro-service to the outside world.

DbTwig has three components:

An HTTP Listener, running on a compute-node, that listens for and processes HTTP based API calls
A database-side broker that gets called by the HTTP listener
Tables in the database to support the DbTwig database-side broker

HTTP Listener

The HTTP Listener’s job is to handle API requests. You can think of the HTTP listener as nothing more than a protocol transformation device converting incoming client requests to SQL. All it does is forward the client’s request, with a few decorations added, to the database.

The listener is a NodeJS based JavaScript application that you can modify to support external functionality such as interfacing to an OAUTH server, uploading files or providing middle-tier operational and support information.

The listener configured as part of a default installation of AsterionDB is specific to OracleLinux v8.x but you can run the basic components upon any NodeJS enabled architecture that can connect to an Oracle Database.

Specifying The Bearer Token

The HTTP listener looks for an Authorization bearer token in the headers submitted by the client. Here’s an example of how the Authorization header value can be set in your client application:

config.headers.Authorization = ‘Bearer ‘ + this.context.userSession.getSessionId();

Database-Side Broker

The database-side broker receives the incoming request from the middle-tier. It will go through a specific set of steps when calling a micro-service:

Validate that the service is registered with DbTwig
Validate that the session has the permission to call the micro-service entry-point
Check for and log any errors
Return values to the caller

The database-side broker is comprised of these major components:

A function that is called by the HTTP listener. The function delegates the work to an equivalent function in the db_twig package. This allows us to minimize the schema elements that are exposed to the HTTP listener.
The db_twig package
The db_twig_profile table
The db_twig_services table

The db_twig Package

The db_twig package contains all of the logic for DbTwig. Within this package you will find several useful function and procedures that you can incorporate into your micro-service.

DbTwig Tables

The DbTwig Profile Table

A profile of your DbTwig installation is kept in the db_twig_profile table. The columns in the table are:

production_mode – a flag (‘Y’, ‘N’) that determines if the DbTwig database-side broker is to run in production or development mode. When DbTwig is running in production mode, error information returned to the client is censored to remove sensitive information (e.g. a stack trace) but still allows for full error logging.
api_error_handler – a fully qualified function name (e.g. schema.package.function, schema.function) that will handle API errors generated by a micro-service

The DbTwig Services Table

The db_twig_services table contains an entry for each registered micro-service. The columns in the table are:

service_owner – the name of the database user that owns the micro-service schema
service_name – the name of the service
service_enabled – a flag (‘Y’, ‘N’) that signifies if the service is enabled
log_all_requests – a flag (‘Y’, ‘N’) that signifies if all requests will be logged to the logged_requests table
production_mode – a flag (‘Y’, ‘N’) that signifies if the service is running in production mode
session_validation_procedure – this value specifies the package.procedure (e.g. restapi.validate_session) to be called in the service owner’s schema to validate sessions permission before calling a micro-service entry-point

DbTwig Architecture Diagram

Here is a diagram of the DbTwig Architecture:

Application Session Validation

DbTwig’s single-point, drop-through approach allows us to easily enforce application session validation on every API call. As detailed above, the services table has a column that denotes the procedure to be called that will validate each API call.

This procedure can conveniently reside in your RESTAPI package since you will be granting DbTwig access to this package anyways. If you chose to put this code in another package, be certain to grant DbTwig execute access to it.

Your micro-service can have its own validation process or it can rely upon DbTwig’s ICAM micro-service. See the documentation for the DbTwig ICAM micro-service and its package header for more information.

Here is a sample declaration of a session validation procedure:

				
					/*
 
  procedure validate_session
 
  API Entry Point: validateSession
 
  This procedure is used by DbTwig to check session validation for every API call.
 
  This procedure is not to be called by a client applications.
 
*/
 
  procedure validate_session
  (
    p_json_parameters                 json_object_t,
    p_required_authorization_level    middle_tier_map.required_authorization_level%type,
    p_allow_blocked_session           middle_tier_map.allow_blocked_session%type
  );

If you are using DbTwig’s built-in ICAM micro-service, you can delegate session validation by calling the validate_session procedure as shown here:

				
					  procedure validate_session
  (
    p_json_parameters                 json_object_t,
    p_required_authorization_level    middle_tier_map.required_authorization_level%type,
    p_allow_blocked_session           middle_tier_map.allow_blocked_session%type
  )
 
  is
 
  begin
 
    icam.validate_session(p_json_parameters, p_required_authorization_level, p_allow_blocked_session);
 
  end validate_session;

Package Layout In Your Micro-Service

Pushing all of your business logic to the data-layer requires that you efficiently organize the packages in your micro-service.

Referring to the diagram above, you will notice that we use a ‘RESTAPI Package’. This is the package that we expose to DbTwig via synonyms and grants. While you can expose more than one package to DbTwig, and there are instances where this makes sense, in general you will want to expose one.

Your RESTAPI package should, for the most part, be comprised of the functions and procedures that are mapped in the middle_tier_map table and your session validation procedure. The mapped functions and procedures will unpack the JSON parameters and then call the appropriate functions and procedures in the underlying packages. This approach allows for easy code re-use in your underlying business logic and compartmentalizes the interface between the middle-tier and your business logic.

A simple micro-service may only need one package but as your service gets more complex you will want to break things down into functional units by using multiple packages.

Integrating Your Micro-Service with DbTwig

We use synonyms and grants in the database to great effect to isolate and manage the exposure of services. This ability greatly enhances the security and management of not only your micro-services but also the data that they operate upon.

You can see these techniques in use by examining the installation scripts for the DbTwig Demonstration Application.

Establish Access to the DB_TWIG Package

The DB_TWIG package contains useful functions and procedures that can be utilized by your micro-service. Access to this package is established by creating a synonym in your micro-service’s schema for the DB_TWIG package and then granting execute access to your schema owner. Here is an example:

create synonym demo.db_twig for dbtwig.db_twig;
grant execute on dbtwig.db_twig to demo;

Grant DbTwig Access To Your Micro-Service

DbTwig needs to be able to access your micro-service’s middle_tier_map table and RESTAPI package. Here’s an example of the grant statements:

grant select on demo.middle_tier_map to dbtwig;
grant execute on demo.restapi to dbtwig;

Populate Your Middle Tier Map Table

DbTwig can not map RESTAPI calls unless you have created the appropriate rows in the middle_tier_map table.

Communications Between Micro-Services

Frequently, micro-services need to talk to one another. The DbTwig architecture, while not directly involved in service-to-service communications, supports this capability. Here’s how it works.

Recall that we have a RESTAPI package that serves as the entry-point to your micro-service. Also remember that the RESTAPI package uses a generic function/procedure signature that accepts/returns a JSON string.

If we put a SERVICE package underneath the RESTAPI package we can then grant another micro-service access to our SERVICE package. To maintain re-usability, each micro-service’s RESTAPI package should unpack the JSON parameters and make the appropriate pass-through calls to the SERVICE package.

Taking this approach allows you to compartmentalize access to your micro-service two ways:

The outside world goes through DbTwig and your RESTAPI
Other micro-services can call your SERVICE package

While another micro-service could pack up parameters in a JSON string and call the RESTAPI package, thus using a loose typing approach, it is advantageous to have functions and procedures that accept specified parameters in order to take advantage of the strict typing available in PL/SQL. In most cases you will want to opt for a hard-typed approach and use the loose typing that JSON provides in edge cases.

Finally, all transactions should start from an API call to DbTwig. For example, your micro-service may field an API request via DbTwig and interact with an adjacent micro-service to complete a given task. In this scenario, the transaction began with a call to the API via DbTwig and included a call to another micro-service from the target micro-service. Observing this requirement allows you centralize session management and error-logging.

Of course there’s always edge cases and you may find it useful to have some entry-points within your SERVICE package that the DBA can use to perform specialized tasks and so forth.

Here’s a diagram of two micro-services communicating with one another:

Dev/Ops Considerations

The converged nature of a data-layer micro-service greatly simplifies your dev/ops tasks. Your dev/ops activity will mainly center around building scripts to accomplish the following tasks. There are also integrated tools that will examine your schema and build appropriate these scripts for you.

Extract Schema Objects

From your development instance, you will extract your business logic elements (e.g. packages, functions, triggers, types) from your schema. This can be accomplished with simple scripts that you can integrate into your dev/ops process.

Install Your Micro-Service

As noted previously, you will create an installation script to be run by the DBA. This script will install your micro-service by creating appropriate schema objects and populating tables with initial values.

Update Your Micro-Service

Your update scripts should be written in an idempotent style which means it can be re-run without adverse effects. This allows the DBA to resolve any issues that may arise during the update process and then re-run the script.

The update script will make any schema and data changes necessary, as well as reload the business logic elements (i.e. packages, triggers, views etc).

The Security Implications of DbTwig

Moving all of your business logic to the data-layer as a micro-service has a transformative impact on security. It can certainly be said that DbTwig is built with a secure-by-design and secure-by-default approach. This posture naturally extends to the micro-services you implement with DbTwig.

Here are some of the security implications:

Secure Business Logic

Recall that the DBA user/role is used to install and update DbTwig and your micro-services. This means that there is no way to change the logic of your system unless you are the DBA! So, if you monitor and properly regulate DBA access to your database, you can be reasonably assured that the logic of your micro-services will not be altered by a threat actor.

There have been many instances of cybersecurity events that involved the alteration of an application’s business logic. Consider the case of the Solarwinds hack from 2021. In that instance, the attackers knew the build system that produced the targeted DLL. This knowledge allowed them to alter the build system’s logic so that it would produce the malicious DLL and rename the good for the bad one.

This type of attack would be extremely difficult to execute against DbTwig’s data-layer micro-service architecture.

Single-Point API Eliminates Schema Visibility

Wanna know a secret? Keeping logic in the middle-tier introduces a fatal security flaw that very few are aware of – schema visibility to the middle-tier!!! Let us state our case.

Programmers are very familiar with executing SQL statements from the middle-tier; select, insert, update statements and so-forth. One of the requirements to execute a statement is the parsing of a statement and the binding of variables. And, therein lies the fatal flaw.

In order parse and bind you have to reveal all of the schema elements to the database connection that is making the SQL call. All of those tables, all of those columns. Even worse, you have to grant access to the database connection so that it can insert, update and delete data!

Now, consider the statement that must be parsed and bound in the DbTwig micro-service architecture; our single-point-API. For that statement, all we have to expose to the database connection is the schema elements (i.e. a function in the database) that satisfies the statement that calls the single-point-API. Furthermore, the only privilege we have to grant to the database connection (our DbTwig runtime user) is execute on the single-point-API function.

This means that if a threat-actor is able to connect to the database using the DbTwig runtime user’s credentials, they will only see the single-point-API and the only thing they can do is call that function. If they try to hack their way into the API by generating a bogus JSON packet to see what they could glean from an error message, that activity will only serve to reveal their presence by generating a logged error.

Eliminate Ad-Hoc Access to Data

As you can see from the previous discussion, eliminating schema visibility to the middle-tier effectively cuts off the ability for a threat-actor to execute ad-hoc SQL statements against the database. This further enhances security by allowing you to know that your data can only be modified by logic that is audited and secure.

Enforce Session Validation

As noted in the section on Application Session Validation, the drop-through approach to delegated logic allows us to enforce session validation by default. This fact, combined with the single-point-API, ensures that each and every transaction executed against your micro-service will have its session checked for validity. This capability directly aligns with modern Zero-Trust principles.

Disable a Micro-Service

The converged nature of DbTwig allows us to easily disable a micro-service by setting the service_enabled flag in the db_twig_services table. This allows you to apply Runtime Application Self-Protection concepts to your micro-service. Currently, this flag can only be set by the DBA user/role ‘by hand’. It can not be directly manipulated by the DbTwig API.

Centralize Logging

The converged, drop-through nature of DbTwig assists once again in the case of request and error logging.

Error Logging

Error logging is enabled by default and can not be disabled. The value of db_twig_profile.api_error_handler specifies the function that will be delegated to for error logging.

DbTwig includes an integrated error logging micro-service. Consult the package header for further details.

Request Logging

The logging of RESTAPI requests for each micro-service can be enabled by the DBA by manipulating the value of the db_twig_services.log_all_requests flag. There is no way to directly manipulate this flag through the DbTwig API.

An Elastic, Middle-Tier Isolation Layer

Building a data-layer micro-service in conjunction with AsterionDB, where all unstructured data is kept in the database, may allow you to remove all user-resources from the middle-tier.

With no resources, the middle-tier is now an elastic security isolation layer that sits between the user and your micro-service and data. It becomes a cookie-cutter device, easily replicated and replaced.

The AsterionDB Converged Computing Platform