Some time ago, we’ve promised to follow up on our Constraints on Views article with a sequel showing how to implement client-side row-level security with jOOQ.
What is row-level security?
Some databases like Oracle or the upcoming PostgreSQL 9.5 provide native support for row-level security, which is awesome – but not every database has this feature.
Row level security essentially means that a given database session can access only some rows in the database, but not others. For instance, this is what John can see in his session:
id account_owner account_name amount -------------------------------------------------- 1 John savings 500.00 2 Jane savings 1300.00 3 John secret poker stash 85193065.00
In the above example, assume that John and Jane are a married couple with access to each others’ bank accounts, except that John wants to hide his secret poker stash from Jane because he’s planning on running away with Jill to the Bahamas living the good life. So, the above would model John’s view on the data set, whereas the below would model Jane’s:
id account_owner account_name amount -------------------------------------------------- 1 John savings 500.00 2 Jane savings 1300.00
The nice thing about row-level security is the fact that the data is completely hidden from a database session, as if it weren’t even there. This includes aggregations or filtering, as can be seen in the below examples of John’s view:
id account_owner account_name amount -------------------------------------------------- 1 John savings 500.00 2 Jane savings 1300.00 3 John secret poker stash 85193065.00 -------------------------------------------------- Total John+Jane 85194865.00
vs. Jane’s view:
id account_owner account_name amount -------------------------------------------------- 1 John savings 500.00 2 Jane savings 1300.00 -------------------------------------------------- Total John+Jane 1800.00
If your database doesn’t support row-level security, you will need to emulate it somehow. One way to do that is by using views, and possibly, temporary tables or some context variables:
CREATE VIEW my_accounts AS
SELECT
a.id,
a.account_owner,
a.account_name,
a.amount
FROM
accounts a
JOIN
account_privileges p
ON
a.id = p.a_id
WHERE
p.privilege_owner = SYS_CONTEXT('banking', 'user');In the above example, I’m using Oracle’s SYS_CONTEXT function, which allows accessing global context from the current session. This context could be initialised every time a JDBC Connection is fetched from the connection pool, for instance.
What if your database doesn’t support these things?
jOOQ to the rescue! Since jOOQ 3.2, a VisitListener Service Provider Interface (SPI) has been introduced for precisely this reason, which allows jOOQ users to perform SQL AST transformations while the SQL statement is being generated.
We’re assuming:
CREATE TABLE accounts (
id BIGINT NOT NULL PRIMARY KEY,
account_owner VARCHAR(20) NOT NULL,
account_name VARCHAR(20) NOT NULL,
amount DECIMAL(18, 2) NOT NULL
);
CREATE TABLE transactions (
id BIGINT NOT NULL PRIMARY KEY,
account_id BIGINT NOT NULL,
amount DECIMAL(18, 2) NOT NULL
);
INSERT INTO accounts (
account_owner, account_name, amount
)
VALUES (1, 'John', 'savings', 500.0),
(2, 'Jane', 'savings', 1300.0),
(3, 'John', 'secret poker stash', 85193065.00);
INSERT INTO transactions (
id, account_id, amount
)
VALUES (1, 1, 200.0),
(2, 1, 300.0),
(3, 2, 300.0),
(4, 2, 800.0),
(5, 2, 200.0),
(6, 3, 85193065.00);Adding a simple WHERE clause
This is the simplest transformation use-case:
-- turn this... SELECT accounts.account_name, accounts.amount FROM accounts -- ... into this SELECT accounts.account_name, accounts.amount FROM accounts WHERE accounts.id IN (?, ?) -- Predicate, for simplicity
Adding an AND clause to an existing WHERE clause
The transformation should still work, if there is already an existing predicate
-- turn this... SELECT accounts.account_name, accounts.amount FROM accounts WHERE accounts.account_owner = 'John' -- ... into this SELECT accounts.account_name, accounts.amount FROM accounts WHERE accounts.account_owner = 'John' AND accounts.id IN (?, ?)
Adding the predicate also for aliased tables
When doing self-joins or for whatever other reason you might have applied an alias
-- turn this... SELECT a.account_name, a.amount FROM accounts a -- ... into this SELECT a.account_name, a.amount FROM accounts a WHERE a.id IN (?, ?)
Adding the predicate also in subqueries / joins / semi-joins
Of course, we shouldn’t restrict ourselves to patching top-level SELECT statements. The following transformation must be applied as well:
-- turn this...
SELECT
t.amount,
t.balance
FROM
transactions t
WHERE
t.account_id IN (
SELECT
a.id
FROM
accounts a
WHERE
a.account_owner = 'John'
)
-- ... into this
SELECT
t.amount,
t.balance
FROM
transactions t
WHERE
t.account_id IN (
SELECT
a.id
FROM
accounts a
WHERE
a.account_owner = 'John'
AND
a.id IN (?, ?)
)Adding the predicate to foreign key references
This might be easily forgotten, but in fact, we also want to add an additional predicate to all foreign key references of accounts.id, namely transactions.account_id, especially when the transactions table is not joined to the accounts table:
-- turn this... SELECT t.amount, t.balance FROM transactions t -- ... into this SELECT t.amount, t.balance FROM transactions t WHERE t.account_id IN (?, ?)
Long story short, we would like to find all queries that refer to the accounts table in some form, and add a simple predicate to it, implementing access control.
DISCLAIMER: As always with security, you should implement security on several layers. SQL AST transformation is not trivial, and the above scenarios are incomplete. Besides, they work only for queries that are built using the jOOQ AST, not for plain SQL queries, or for queries that are run via JDBC directly, via Hibernate, or via stored procedures, views (which in turn refer to the accounts table), or simple table synonyms.
So, read this post as a tutorial showing how to perform AST transformation, not as a complete solution to row-level security
How to do it with jOOQ?
Now comes the interesting part. We’re going to do the whole thing with jOOQ. First off, remember that in order to implement backwards-compatible SPI evolution, we always provide a default implementation for our SPIs. In this case, we’re going to extend DefaultVisitListener instead of implementing VisitListener directly.
The base of our VisitListener will be the following:
public class AccountIDFilter extends DefaultVisitListener {
final Integer[] ids;
public AccountIDFilter(Integer... ids) {
this.ids = ids;
}
}In other words, a filtering listener that filters for a given set of IDs to be put in an IN predicate.
Now, first off, we’ll need a bit of utility methods. The following two utilities push or pop some objects on top of a stack:
void push(VisitContext context) {
conditionStack(context).push(new ArrayList<>());
whereStack(context).push(false);
}
void pop(VisitContext context) {
whereStack(context).pop();
conditionStack(context).pop();
}… and the stack can be seen here:
Deque<List<Condition>> conditionStack(
VisitContext context) {
Deque<List<Condition>> data = (Deque<List<Condition>>)
context.data("conditions");
if (data == null) {
data = new ArrayDeque<>();
context.data("conditions", data);
}
return data;
}
Deque<Boolean> whereStack(VisitContext context) {
Deque<Boolean> data = (Deque<Boolean>)
context.data("predicates");
if (data == null) {
data = new ArrayDeque<>();
context.data("predicates", data);
}
return data;
}In prose, the conditionStack maintains a stack of conditions for each subquery, whereas the whereStack maintains a stack of flags for each subquery. The conditions are the conditions that should be generated in the WHERE clause of the given subquery, whereas the flags indicate whether a WHERE clause is already present (i.e. whether new conditions should be appended using AND, rather than WHERE).
For convenience, we’ll also add the following utilities:
List<Condition> conditions(VisitContext context) {
return conditionStack(context).peek();
}
boolean where(VisitContext context) {
return whereStack(context).peek();
}
void where(VisitContext context, boolean value) {
whereStack(context).pop();
whereStack(context).push(value);
}These utilities allow for accessing both the conditions and flags at the top of the stack (in the current subquery), as well as for replacing the flag at the top of the stack.
Why do we need a stack?
It’s simple. We want to apply the predicate only locally for the current subquery, while jOOQ transforms and generates your SQL statement. Remember, when we were transforming the following:
SELECT
t.amount,
t.balance
FROM
transactions t
WHERE
t.account_id IN (
SELECT
a.id
FROM
accounts a
WHERE
a.account_owner = 'John'
AND
a.id IN (?, ?)
)
AND
t.account_id IN (?, ?)… in the end, we want two additional predicates generated in the above query. One in the subquery selecting from accounts, and another one in the top-level query selecting from transactions, but the two predicates shouldn’t interfere with each other, i.e. when jOOQ generates the subquery, we only want to see objects that are relevant to the subquery (top of the stack).
So, let’s see how and when we push stuff on the stack. First off, we need to listen for start and end events of SQL clauses:
starting a SQL clause
This is straight-forward. Every time we enter a new SQL statement, we want to push a new set of data (conditions, flags) on the stack. In a way, we’re creating a local scope for the subquery:
@Override
public void clauseStart(VisitContext context) {
// Enter a new SELECT clause / nested select
// or DML statement
if (context.clause() == SELECT ||
context.clause() == UPDATE ||
context.clause() == DELETE ||
context.clause() == INSERT) {
push(context);
}
}Of course, this scope has to be cleaned up at the end of the same clause:
@Override
public void clauseEnd(VisitContext context) {
// [ ... more code will follow ... ]
// Leave a SELECT clause / nested select
// or DML statement
if (context.clause() == SELECT ||
context.clause() == UPDATE ||
context.clause() == DELETE ||
context.clause() == INSERT) {
pop(context);
}
}This was the easy part. Now, it gets a bit more interesting. When we end a clause, and that clause is a WHERE clause of a SELECT, UPDATE, or DELETE statement, then we want to render an additional keyword and predicate:
@Override
public void clauseEnd(VisitContext context) {
// Append all collected predicates to the WHERE
// clause if any
if (context.clause() == SELECT_WHERE ||
context.clause() == UPDATE_WHERE ||
context.clause() == DELETE_WHERE) {
List<Condition> conditions =
conditions(context);
if (conditions.size() > 0) {
context.context()
.formatSeparator()
.keyword(where(context)
? "and"
: "where"
)
.sql(' ');
context.context().visit(
DSL.condition(Operator.AND, conditions)
);
}
}
// [ ... code from previous snippet ... ]So, the only thing we still need to do is assemble that List<Condition> on the stack of the current query, such that we can append it to the WHERE clause, as well as the flag that decides between "and" and "where". This can be done by overriding visitEnd(), a method that is invoked at the end of visiting a QueryPart (as opposed to a Clause):
@Override
public void visitEnd(VisitContext context) {
// We'll see what this means in a bit...
pushConditions(context, ACCOUNTS,
ACCOUNTS.ID, ids);
pushConditions(context, TRANSACTIONS,
TRANSACTIONS.ACCOUNT_ID, ids);
// Check if we're rendering any condition within
// the WHERE clause In this case, we can be sure
// that jOOQ will render a WHERE keyword
if (context.queryPart() instanceof Condition) {
List<Clause> clauses = clauses(context);
if (clauses.contains(SELECT_WHERE) ||
clauses.contains(UPDATE_WHERE) ||
clauses.contains(DELETE_WHERE)) {
where(context, true);
}
}
}
List<Clause> clauses(VisitContext context) {
List<Clause> result = asList(context.clauses());
int index = result.lastIndexOf(SELECT);
if (index > 0)
return result.subList(index, result.size() - 1);
else
return result;
}At the end of each query part…
- we’re trying to push the relevant conditions on the stack if applicable
- we’re checking to see if a
WHEREclause is present and set the relevant flag
So, finally, the core logic contained in pushConditions() is the only thing missing, and that’s:
<E> void pushConditions(
VisitContext context,
Table<?> table,
Field<E> field,
E... values) {
// Check if we're visiting the given table
if (context.queryPart() == table) {
List<Clause> clauses = clauses(context);
// ... and if we're in the context of the current
// subselect's FROM clause
if (clauses.contains(SELECT_FROM) ||
clauses.contains(UPDATE_UPDATE) ||
clauses.contains(DELETE_DELETE)) {
// If we're declaring a TABLE_ALIAS...
// (e.g. "ACCOUNTS" as "a")
if (clauses.contains(TABLE_ALIAS)) {
QueryPart[] parts = context.queryParts();
// ... move up the QueryPart visit path to find the
// defining aliased table, and extract the aliased
// field from it. (i.e. the "a" reference)
for (int i = parts.length - 2; i >= 0; i--) {
if (parts[i] instanceof Table) {
field = ((Table<?>) parts[i]).field(field);
break;
}
}
}
// Push a condition for the field of the
// (potentially aliased) table
conditions(context).add(field.in(values));
}
}
}And we’re done! Whew – well, that certainly wasn’t too easy. AST transformation never is. But the above algorithm is robust and can be used to run all of the aforementioned queries.
Testing the above
Configuration configuration = create().configuration();
// This configuration has full access to all rows
DSLContext fullaccess = DSL.using(configuration);
// This configuration has restricted access to IDs 1 and 2
DSLContext restricted = DSL.using(
configuration.derive(
DefaultVisitListenerProvider.providers(
new AccountIDFilter(1, 2)
)
)
);
// Fetching accounts
assertEquals(3, fullaccess.fetch(ACCOUNTS).size());
assertEquals(2, restricted.fetch(ACCOUNTS).size());Generating the following SQL:
select "PUBLIC"."ACCOUNTS"."ID", "PUBLIC"."ACCOUNTS"."ACCOUNT_OWNER", "PUBLIC"."ACCOUNTS"."ACCOUNT_NAME", "PUBLIC"."ACCOUNTS"."AMOUNT" from "PUBLIC"."ACCOUNTS" --------------------------------------- select "PUBLIC"."ACCOUNTS"."ID", "PUBLIC"."ACCOUNTS"."ACCOUNT_OWNER", "PUBLIC"."ACCOUNTS"."ACCOUNT_NAME", "PUBLIC"."ACCOUNTS"."AMOUNT" from "PUBLIC"."ACCOUNTS" where "PUBLIC"."ACCOUNTS"."ID" in ( 1, 2 )
// Fetching transactions assertEquals(6, fullaccess.fetch(TRANSACTIONS).size()); assertEquals(5, restricted.fetch(TRANSACTIONS).size());
Generating the following SQL:
select "PUBLIC"."TRANSACTIONS"."ID", "PUBLIC"."TRANSACTIONS"."ACCOUNT_ID", "PUBLIC"."TRANSACTIONS"."AMOUNT" from "PUBLIC"."TRANSACTIONS" --------------------------------------- select "PUBLIC"."TRANSACTIONS"."ID", "PUBLIC"."TRANSACTIONS"."ACCOUNT_ID", "PUBLIC"."TRANSACTIONS"."AMOUNT" from "PUBLIC"."TRANSACTIONS" where "PUBLIC"."TRANSACTIONS"."ACCOUNT_ID" in ( 1, 2 )
// Fetching John's accounts
assertEquals(asList(1, 3), fullaccess.fetchValues(
select(ACCOUNTS.ID)
.from(ACCOUNTS)
.where(ACCOUNTS.ACCOUNT_OWNER.eq("John"))
.orderBy(1)
));
assertEquals(asList(1 ), restricted.fetchValues(
select(ACCOUNTS.ID)
.from(ACCOUNTS)
.where(ACCOUNTS.ACCOUNT_OWNER.eq("John"))
.orderBy(1)
));Generating the following SQL:
select "PUBLIC"."ACCOUNTS"."ID" from "PUBLIC"."ACCOUNTS" where "PUBLIC"."ACCOUNTS"."ACCOUNT_OWNER" = 'John' order by 1 asc --------------------------------------- select "PUBLIC"."ACCOUNTS"."ID" from "PUBLIC"."ACCOUNTS" where "PUBLIC"."ACCOUNTS"."ACCOUNT_OWNER" = 'John' and "PUBLIC"."ACCOUNTS"."ID" in ( 1, 2 ) order by 1 asc
// Fetching John's accounts via an aliased table
Accounts a = ACCOUNTS.as("a");
assertEquals(asList(1, 3), fullaccess.fetchValues(
select(a.ID)
.from(a)
.where(a.ACCOUNT_OWNER.eq("John"))
.orderBy(1)
));
assertEquals(asList(1 ), restricted.fetchValues(
select(a.ID)
.from(a)
.where(a.ACCOUNT_OWNER.eq("John"))
.orderBy(1)
));Generating the following SQL:
select "a"."ID" from "PUBLIC"."ACCOUNTS" "a" where "a"."ACCOUNT_OWNER" = 'John' order by 1 asc --------------------------------------- select "a"."ID" from "PUBLIC"."ACCOUNTS" "a" where "a"."ACCOUNT_OWNER" = 'John' and "a"."ID" in ( 1, 2 ) order by 1 asc
// Fetching John's transactions
Transactions t = TRANSACTIONS.as("t");
assertEquals(asList(1, 2, 6), fullaccess.fetchValues(
select(t.ID)
.from(t)
.where(t.ACCOUNT_ID.in(
select(a.ID)
.from(a)
.where(a.ACCOUNT_OWNER.eq("John"))
))
.orderBy(1)
));
assertEquals(asList(1, 2 ), restricted.fetchValues(
select(t.ID)
.from(t)
.where(t.ACCOUNT_ID.in(
select(a.ID)
.from(a)
.where(a.ACCOUNT_OWNER.eq("John"))
))
.orderBy(1)
));Generating the following SQL:
select "t"."ID"
from "PUBLIC"."TRANSACTIONS" "t"
where "t"."ACCOUNT_ID" in (
select "a"."ID"
from "PUBLIC"."ACCOUNTS" "a"
where "a"."ACCOUNT_OWNER" = 'John'
)
order by 1 asc
---------------------------------------
select "t"."ID"
from "PUBLIC"."TRANSACTIONS" "t"
where "t"."ACCOUNT_ID" in (
select "a"."ID"
from "PUBLIC"."ACCOUNTS" "a"
where "a"."ACCOUNT_OWNER" = 'John'
and "a"."ID" in (
1, 2
)
)
and "t"."ACCOUNT_ID" in (
1, 2
)
order by 1 ascConclusion
The above examples have shown how row-level security can be implemented relatively easily using jOOQ’s VisitListener, a very powerful Service Provider Interface for client-side SQL AST transformation.
The applications don’t stop there. It is easy to see how you can implement a VisitListener that throws an exception every time you run a DML query that does not have a WHERE clause.
Another application would be to replace a table by a similar table, whenever a certain condition is true.
And the best thing is: With jOOQ, you don’t need to parse SQL in order to transform it (which is extremely hard, depending on the SQL dialect). You already manually build an Abstract Syntax Tree using jOOQ’s fluent API, so you get all these features for free.
So: Happy SQL transformation!
Appendix: Full code
import static java.util.Arrays.asList;
import static org.jooq.Clause.DELETE;
import static org.jooq.Clause.DELETE_DELETE;
import static org.jooq.Clause.DELETE_WHERE;
import static org.jooq.Clause.INSERT;
import static org.jooq.Clause.SELECT;
import static org.jooq.Clause.SELECT_FROM;
import static org.jooq.Clause.SELECT_WHERE;
import static org.jooq.Clause.TABLE_ALIAS;
import static org.jooq.Clause.UPDATE;
import static org.jooq.Clause.UPDATE_UPDATE;
import static org.jooq.Clause.UPDATE_WHERE;
import static org.jooq.test.h2.generatedclasses.Tables.ACCOUNTS;
import static org.jooq.test.h2.generatedclasses.Tables.TRANSACTIONS;
import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Deque;
import java.util.List;
import org.jooq.Clause;
import org.jooq.Condition;
import org.jooq.Field;
import org.jooq.Operator;
import org.jooq.QueryPart;
import org.jooq.Table;
import org.jooq.VisitContext;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultVisitListener;
@SuppressWarnings("unchecked")
public class AccountIDFilter extends DefaultVisitListener {
final Integer[] ids;
public AccountIDFilter(Integer... ids) {
this.ids = ids;
}
void push(VisitContext context) {
conditionStack(context).push(new ArrayList<>());
whereStack(context).push(false);
}
void pop(VisitContext context) {
whereStack(context).pop();
conditionStack(context).pop();
}
Deque<List<Condition>> conditionStack(VisitContext context) {
Deque<List<Condition>> data = (Deque<List<Condition>>) context.data("conditions");
if (data == null) {
data = new ArrayDeque<>();
context.data("conditions", data);
}
return data;
}
Deque<Boolean> whereStack(VisitContext context) {
Deque<Boolean> data = (Deque<Boolean>) context.data("predicates");
if (data == null) {
data = new ArrayDeque<>();
context.data("predicates", data);
}
return data;
}
List<Condition> conditions(VisitContext context) {
return conditionStack(context).peek();
}
boolean where(VisitContext context) {
return whereStack(context).peek();
}
void where(VisitContext context, boolean value) {
whereStack(context).pop();
whereStack(context).push(value);
}
<E> void pushConditions(VisitContext context, Table<?> table, Field<E> field, E... values) {
// Check if we're visiting the given table
if (context.queryPart() == table) {
List<Clause> clauses = clauses(context);
// ... and if we're in the context of the current subselect's
// FROM clause
if (clauses.contains(SELECT_FROM) ||
clauses.contains(UPDATE_UPDATE) ||
clauses.contains(DELETE_DELETE)) {
// If we're declaring a TABLE_ALIAS... (e.g. "T_BOOK" as "b")
if (clauses.contains(TABLE_ALIAS)) {
QueryPart[] parts = context.queryParts();
// ... move up the QueryPart visit path to find the
// defining aliased table, and extract the aliased
// field from it. (i.e. the "b" reference)
for (int i = parts.length - 2; i >= 0; i--) {
if (parts[i] instanceof Table) {
field = ((Table<?>) parts[i]).field(field);
break;
}
}
}
// Push a condition for the field of the (potentially aliased) table
conditions(context).add(field.in(values));
}
}
}
/**
* Retrieve all clauses for the current subselect level, starting with
* the last {@link Clause#SELECT}.
*/
List<Clause> clauses(VisitContext context) {
List<Clause> result = asList(context.clauses());
int index = result.lastIndexOf(SELECT);
if (index > 0)
return result.subList(index, result.size() - 1);
else
return result;
}
@Override
public void clauseStart(VisitContext context) {
// Enter a new SELECT clause / nested select, or DML statement
if (context.clause() == SELECT ||
context.clause() == UPDATE ||
context.clause() == DELETE ||
context.clause() == INSERT) {
push(context);
}
}
@Override
public void clauseEnd(VisitContext context) {
// Append all collected predicates to the WHERE clause if any
if (context.clause() == SELECT_WHERE ||
context.clause() == UPDATE_WHERE ||
context.clause() == DELETE_WHERE) {
List<Condition> conditions = conditions(context);
if (conditions.size() > 0) {
context.context()
.formatSeparator()
.keyword(where(context) ? "and" : "where")
.sql(' ');
context.context().visit(DSL.condition(Operator.AND, conditions));
}
}
// Leave a SELECT clause / nested select, or DML statement
if (context.clause() == SELECT ||
context.clause() == UPDATE ||
context.clause() == DELETE ||
context.clause() == INSERT) {
pop(context);
}
}
@Override
public void visitEnd(VisitContext context) {
pushConditions(context, ACCOUNTS, ACCOUNTS.ID, ids);
pushConditions(context, TRANSACTIONS, TRANSACTIONS.ACCOUNT_ID, ids);
// Check if we're rendering any condition within the WHERE clause
// In this case, we can be sure that jOOQ will render a WHERE keyword
if (context.queryPart() instanceof Condition) {
List<Clause> clauses = clauses(context);
if (clauses.contains(SELECT_WHERE) ||
clauses.contains(UPDATE_WHERE) ||
clauses.contains(DELETE_WHERE)) {
where(context, true);
}
}
}
}| Reference: | Implementing Client-Side Row-Level Security with jOOQ from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
