Implementation
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A policy is implemented while rendering your jOOQ query. It always adds the Policy condition to the query in a meaningful way, which means that rows are filtered out of the query.
There is never any exception thrown by the application of a Policy! A policy only ever filters out rows of queries or DML statements.
With a policy implementing TENANT.TENANT_ID = 42, we might write queries like these:
SELECT
SELECT CUSTOMER.ID, CUSTOMER.NAME FROM CUSTOMER WHERE CUSTOMER.TENANT_ID = 42
create.select(CUSTOMER.ID, CUSTOMER.NAME)
.from(CUSTOMER)
.fetch();
INSERT
The INSERT case is curious, because it is unusual for a single-row INSERT .. VALUES statement not to actually insert anything (producing an update count of 0), but it makes perfect sense with a policy.
INSERT INTO CUSTOMER (ID, NAME, TENANT_ID) SELECT t.ID, t.NAME, t.TENANT_ID FROM ( -- These are the values from the VALUES () clause SELECT 1, 'John Doe', 42 ) t (ID, NAME, TENANT_ID) -- This is the policy condition WHERE t.TENANT_ID = 42
create.insertInto(CUSTOMER)
.columns(
CUSTOMER.ID,
CUSTOMER.NAME,
CUSTOMER.TENANT_ID)
.values(
1,
"John Doe",
42)
.execute();
The example above shows that the inserted CUSTOMER.TENANT_ID value must match the value from the policy condition, otherwise, the row won't be inserted.
UPDATE
Policies are applied to an UPDATE statement in 2 ways. First, via WHERE clause, preventing updates to rows that can't be selected:
UPDATE CUSTOMER SET NAME = 'John Doe' WHERE CUSTOMER.ID = 5 AND CUSTOMER.TENANT_ID = 42
create.update(CUSTOMER)
.set(CUSTOMER.NAME, "John Doe")
.where(CUSTOMER.ID.eq(5))
.execute();
But, if necessary also to the SET clause, preventing an UPDATE to a value that is not allowed, after the update.
UPDATE CUSTOMER SET TENANT_ID = 1 WHERE CUSTOMER.ID = 5 -- Preventing access to some rows AND CUSTOMER.TENANT_ID = 42 -- Preventing updates to certain values AND 1 = 42
create.update(CUSTOMER)
.set(
CUSTOMER.TENANT_ID,
1
)
.where(CUSTOMER.ID.eq(5))
.execute();
The weird looking predicate 1 = 42 is achieved from substituting the SET clause element TENANT_ID = 1 into the policy condition CUSTOMER.TENANT_ID = 42. If the policy allowed CUSTOMER.TENANT_ID IN (1, 42), then the update would be legal again:
UPDATE CUSTOMER SET TENANT_ID = 1 WHERE CUSTOMER.ID = 5 -- Preventing access to some rows AND CUSTOMER.TENANT_ID IN (1, 42) -- Preventing updates to certain values AND 1 IN (1, 42)
create.update(CUSTOMER)
.set(
CUSTOMER.TENANT_ID,
1
)
.where(CUSTOMER.ID.eq(5))
.execute();
DELETE
A policy only affects the WHERE clause of a DELETE statement, similar to a WHERE clause of a SELECT statement.
DELETE FROM CUSTOMER WHERE CUSTOMER.ID = 5 AND CUSTOMER.TENANT_ID = 42
create.deleteFrom(CUSTOMER)
.where(CUSTOMER.ID.eq(5))
.execute();
Feedback
Do you have any feedback about this page? We'd love to hear it!