Oracle Row Security Level in Multi-Tenant Applications / Defaults for New Entries

Question

Task

Upgrade your existing application to use the multi-tenant approach. You must create tenants, and each user session must reference only one active tenant. Each tenant should be able to see and update their section of the database schema.

An approach

• Create an Oracle application context containing tenant ID
• Add the tenant identifier column to any table that needs to be copied.
• Create a predicate function that returns "tenant_id = sys_context (" tenant_context "," tenant_id ")" for SELECT, INSERT, UPDATE and delete
• Add a suitable policy via dbms_rls to register the predicate function

This works like a charm without touching an existing application for SELECT, UPDATE and DELETE

Question

When you insert a column, tenant_id is not set, and a security exception is thrown. Is there any way as smooth as a predicate function to always set security-related fields? I would prefer not to add triggers to more than 300 tables.

Answer 1

Sometimes a question asks a question. I did not know that you could use mutable expressions in the column defaults, therefore

alter table XXX
add column tenant_id default sys_context('tenant_context', 'tenant_id');

actually solves my problem.