Multiple Views, One Schema: Simple RBAC with Duality Views

In this article, we’ll explore how JSON Relational Duality Views can be used to decompose a schema into subdomains – allowing role-based isolation of data to relevant services.

If you’d like to follow along with this article, I recommend using Oracle Database Free.

Unfamiliar with JSON Relational Duality Views? Read 7 Reasons to try out JSON Relational Duality Views, with samples for a primer.

Schemas with multiple access patterns

Imagine a website that takes product orders for various customers. You’d need products, orders, and customers tables something like this:

An ERD (Entity-Relationship Diagram) illustrating the structure of Orders, Customers, and Products tables, showcasing relationships and data types.

To facilitate proper isolation and security, you’d also want different APIs for managing products, customer profiles, and customer orders.

Now, you could divide up these tables into separate schemas, create responsible services, and generally adhere to microservices patterns. This is potentially a lot of work for your average developer.

But what if there was a simpler way to keep service isolation, while retaining a single database schema?

Enter JSON Relational Duality Views

Duality views expose JSON documents across one or more related tables. For example, a CustomerOrder service that writes customer orders only needs write access to the orders table, but read access to customers and products.

We can easily model this with a JSON Relational Duality View, using the GraphQL syntax:

CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW orders_dv
 AS orders @insert {
    _id : order_id
    quantity
    customers {
        customerId : customer_id
    }
    products  {
        productId : product_id
    }
};

CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW orders_dv
 AS orders @insert {
    _id : order_id
    quantity
    customers {
        customerId : customer_id
    }
    products  {
        productId : product_id
    }
};

Inserting a record via this duality view allows referencing customer and product IDs. The database then creates the corresponding order row automatically:

insert into orders_dv (data) values ('{
  "quantity": 2,
  "customers": {
    "customerId": 1
  },
  "products": {
    "productId": 1
  }
}');

insert into orders_dv (data) values ('{
  "quantity": 2,
  "customers": {
    "customerId": 1
  },
  "products": {
    "productId": 1
  }
}');

We can easily imagine other views on this schema – For example, a customer view that allows customers read-only access to their orders, or an admin view that allows write access to products in the store.

-- read only access for a customer to view orders
CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW customer_orders
AS
customers {
    _id : customer_id
    orders {
        orderId : order_id
        quantity
        products {
            productId   : product_id
        }
    }
};

-- admin read/write access to products
CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW product_admin
AS products @insert @update @delete {
  _id: product_id
  productName : product_name
  price
};

-- read only access for a customer to view orders
CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW customer_orders
AS
customers {
    _id : customer_id
    orders {
        orderId : order_id
        quantity
        products {
            productId   : product_id
        }
    }
};

-- admin read/write access to products
CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW product_admin
AS products @insert @update @delete {
  _id: product_id
  productName : product_name
  price
};

Creating roles to access each view

Each view can have its own role, separating responsibilities out to different services:

create user customer_orders_service identified by Welcome12345;
grant select, insert on orders_dv to orders_service;

create user orders_service identified by Welcome12345;
grant select on customer_orders to customer_orders_service;

create user product_admin_user identified by Welcome12345;
grant select, insert, update, delete on product_admin to product_admin_user;

create user customer_orders_service identified by Welcome12345;
grant select, insert on orders_dv to orders_service;

create user orders_service identified by Welcome12345;
grant select on customer_orders to customer_orders_service;

create user product_admin_user identified by Welcome12345;
grant select, insert, update, delete on product_admin to product_admin_user;

Using these roles, we can implement separate services for managing customer orders, general orders, and product administration.

I’ll leave this as an exercise to the reader, but it could be done as custom CRUD web services, or quickly bootstrapped using Oracle REST Data Services.

References

Schema

This is the schema I used, repeated for your use:

-- Customers
CREATE TABLE customers (
    customer_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL
);

-- Products
CREATE TABLE products (
    product_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_name  VARCHAR2(100) NOT NULL,
    price         NUMBER(10,2) NOT NULL
);

-- Orders (one per customer transaction)
CREATE TABLE orders (
    order_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id  NUMBER NOT NULL,
    product_id   NUMBER NOT NULL,
    quantity     NUMBER NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id),
    CONSTRAINT fk_orders_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
);


CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW orders_dv
 AS orders @insert {
    _id : order_id
    quantity
    customers {
        customerId : customer_id
    }
    products  {
        productId : product_id
    }
};


-- Customers
CREATE TABLE customers (
    customer_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL
);

-- Products
CREATE TABLE products (
    product_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_name  VARCHAR2(100) NOT NULL,
    price         NUMBER(10,2) NOT NULL
);

-- Orders (one per customer transaction)
CREATE TABLE orders (
    order_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id  NUMBER NOT NULL,
    product_id   NUMBER NOT NULL,
    quantity     NUMBER NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id),
    CONSTRAINT fk_orders_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
);


CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW orders_dv
 AS orders @insert {
    _id : order_id
    quantity
    customers {
        customerId : customer_id
    }
    products  {
        productId : product_id
    }
};