Db2Session

Gem Version

A Rails 5 & Rails 6 plugin for managing queries by sessions of authenticated db2 (i-series) multi-users on a remote db2 server.

Installation

Add this line to your application's Gemfile:

gem 'db2_session'

And then execute:

$ bundle

Or install it yourself as:

$ gem install db2_session

Usage

This plugin highly depends on Db2Query. Where Db2Query is responsible to manage database queries and Db2Session is only responsible to manage user sessions in the controllers of each query.

Note: Please do the Db2Query Installation & Initialization steps before move to the next section.

Rules:

  1. Queries have to extend ApplicationQuery, an abstract query, which inherit all attributes and methods required from Db2Session::ApplicationQuery
  2. Controllers have to extend Db2Controller, an abstract controller, which inherit all attributes and methods required from Db2Session::ApplicationController

Mount Engine Authentication Path

To be able to use the authentication path, mount the engine at your application config/routes.rb

Rails.application.routes.draw do
  mount Db2Session::Engine => "/db2_session"
  ...
end

The db2_session/login and db2_session/logout path is now available at rails routes

$ rails routes
     Prefix Verb URI Pattern           Controller#Action
db2_session      /db2_session          Db2Session::Engine
...
Routes for Db2Session::Engine:
      login POST /login(.:format)      db2_session/sessions#new
     logout GET  /logout(.:format)     db2_session/sessions#delete
...

Query By User Session

Db2Query use only one user which is hardcoded at config/db2query.yml. Here, at Db2Session query, multi-user can make requests and queries by using their own db2 credential that is securely attached at connection client instance. The plugin will assign the connection to the related user on each request based on an attached token that was created during the login process.

Create an abstract query, here we use ApplicationQuery that extend Db2Session::ApplicationQuery. All of your query class have to extend this class. 

class ApplicationQuery < Db2Session::ApplicationQuery
  def self.inherited(subclass)
    subclass.define_query_definitions
  end
end

Then create a Db2ConnectionQuery that inherit from ApplicationQUery

$ rails g query connection --defines status

# app/queries/db2_connection_query.rb
class Db2ConnectionQuery < ApplicationQuery
  def status_sql
    "SELECT 1 AS CONNECTED FROM SYSIBM.SYSDUMMY1"
  end
end

Update the definitions:

# app/queries/definitions/db2_connection_query_definitions.rb
module Definitions
  class Db2ConnectionQueryDefinitions < Db2Query::Definitions
    def describe
      query_definition :status do |c|
        c.connected :boolean
      end
    end
  end
end

Session Controller

Next, create a Db2Controller as a base controller and Db2ConnectionController that extend Db2Controller where we can render Db2ConnectionQuery.status.

# app/controllers/db2_controller.rb
class Db2Controller < Db2Session::ApplicationController
end

# app/controllers/db2_connection_controller.rb
class Db2ConnectionController < Db2Controller
  def index
    status = Db2ConnectionQuery.status
    render json: {
      data: {
        user: Db2ConnectionQuery.connection.userid,
        trx_time: Db2ConnectionQuery.connection.trx_time,
        connected: status.connected
      }
    }
  end
end

Add the route at config/routes:

Rails.application.routes.draw do
  mount Db2Session::Engine => "/db2_session"
  get "/db2_connection", to: "db2_connection#index"
end

Then check whether the db2_connection_path already listed at application routes. 

$ rails routes
         Prefix Verb URI Pattern               Controller#Action
    db2_session      /db2_session              Db2Session::Engine
 db2_connection GET  /db2_connection(.:format) db2_connection#index

Routes for Db2Session::Engine:
          login POST /login(.:format)          db2_session/sessions#new
         logout GET  /logout(.:format)         db2_session/sessions#delete

Authenticate a Request

First, run your development server

$ rails s

Post a login request with Db2 userid and password data to get a token.

$ curl -XPOST -i -H "Content-Type: application/json" -d '{ "userid": "YOHANES", "password": "XXXXXX" }' http://localhost:3000/db2_session/login

The installation success if you see response as follow:

HTTP/1.1 200 OK 
X-Frame-Options: SAMEORIGIN
X-Xss-Protection: 1; mode=block
X-Content-Type-Options: nosniff
X-Download-Options: noopen
X-Permitted-Cross-Domain-Policies: none
Referrer-Policy: strict-origin-when-cross-origin
Authorization: Bearer eyJhbGciOiJIUzI1NiJ9.eyJkYXRhIjp7InNlc3Npb25fa2V5Ijo0NzM0Mzg2MTIzNTE2MH19.KW5NZo43WT47QiKrXvVyRd2kovY1Y53fSabU2BIx5nc
Content-Type: application/json; charset=utf-8
Vary: Accept
Etag: W/"883b4f34583e448cb88bf7c2146dc445"
Cache-Control: max-age=0, private, must-revalidate
X-Request-Id: 9411f926-404d-4b6c-9b4e-349b02560cfa
X-Runtime: 0.259895
Server: WEBrick/1.4.2 (Ruby/2.6.4/2019-08-28)
Date: Fri, 13 Aug 2021 04:58:15 GMT
Content-Length: 35
Connection: Keep-Alive

{"message":"YOHANES are logged in."}

Copy the Authorization Bearer token, and use it to check connection status.

$ curl -XGET -H "Authorization: Bearer eyJhbGciOiJIUzI1NiJ9.eyJkYXRhIjp7InNlc3Npb25fa2V5Ijo0NzM0Mzg2MTIzNTE2MH19.KW5NZo43WT47QiKrXvVyRd2kovY1Y53fSabU2BIx5nc" -H "Content-Type: application/json" http://localhost:3000/db2_connection

Note: Replace the token with your own token that generated during login process.

Then the response will be as follow:

 {"data":{"user":"YOHANES","trx_time":14556.481167844,"connected":true}}

Now the token can be use on each request to your application.

License

The gem is available as open source under the terms of the MIT License.