This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Inheritance
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A policy on a parent table can be inherited by its child tables, which greatly simplifies the task of defining policies across a schema.
For example, assuming there's a TENANT table with a TENANT_ID primary key, to which a policy is being applied to form a predicate like TENANT.TENANT_ID = 42. Now, with a schema like this:
CREATE TABLE TENANT ( TENANT_ID BIGINT NOT NULL PRIMARY KEY ... ); CREATE TABLE CUSTOMER ( CUSTOMER_ID BIGINT NOT NULL PRIMARY KEY, TENANT_ID BIGINT NOT NULL REFERENCES TENANT, ... ); CREATE TABLE INVOICE ( INVOICE_ID BIGINT NOT NULL PRIMARY KEY, CUSTOMER_ID BIGINT NOT NULL REFERENCES CUSTOMER, ... )
For performance reasons, it is probably useful to denormalise your schema a bit by repeating theTENANT_IDcolumn also on theINVOICEtable and all the other tables, even if it is not necessary from a schema design perspective. This example deliberately avoided this approach to show the feature's capabilities.
Now, naturally, if a user can access only TENANT.TENANT_ID = 42, then all the other tenants' customers and invoices should be inaccessible as well. How easily is this forgotten when implementing row level security manually? Not with this feature! Just declare inheritance on your policies like this:
Configuration configuration = ...;
configuration.set(new DefaultPolicyProvider().append(
// The table on which to apply a policy
TENANT,
// The condition to apply to queries against the table
TENANT.TENANT_ID.eq(42),
// Implicit join paths from a child table to the TENANT table
// All child tables will automatically inherit the policy
CUSTOMER.tenant(),
INVOICE.customer().tenant()
));
This is equivalent to declaring 3 policies:
Configuration configuration = ...;
configuration.set(new DefaultPolicyProvider()
.append(TENANT, TENANT.TENANT_ID.eq(42))
.append(CUSTOMER, CUSTOMER.tenant().TENANT_ID.eq(42))
.append(INVOICE, INVOICE.customer().tenant().TENANT_ID.eq(42))
);
Now, if you query the invoice table like this:
create.select(INVOICE.ID, INVOICE.AMOUNT)
.from(INVOICE)
.fetch();
Then the above policy will add a INVOICE.customer().tenant().TENANT_ID.eq(42) predicate, effectively adding an implicit JOIN to your query:
SELECT INVOICE.ID, INVOICE.AMOUNT
FROM (
INVOICE
JOIN (
CUSTOMER
JOIN (
SELECT *
FROM TENANT
TENANT_ID = 42
) TENANT
ON CUSTOMER.TENANT_ID = TENANT.TENANT_ID
)
ON INVOICE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
)
The same is true for all other DML statements as well. For example, it won't be possible to INSERT, UPDATE, or DELETE an invoice from a different TENANT
Feedback
Do you have any feedback about this page? We'd love to hear it!