Implementing Client-Side Row-Level Security with jOOQ

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 WHERE clause 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 asc

Conclusion

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.

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!

jOOQ: The best way to write SQL in Java

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);
            }
        }
    }
}

4 thoughts on “Implementing Client-Side Row-Level Security with jOOQ

  1. Hi, I just used this to implement row-level security in my application. I refactored the code a little bit to make it more compatible with our standards and moved the ‘condtions’ and ‘wheres’ stacks to private fields in the filter. It doesn’t seem to break anything but the stacks are not put in the VisitContext anymore. It is ok to do that or will it have a sneaky side effect that I don’t see ?

    • Thanks for the feedback! Hmm, hard to say, I’d have to see code or more explanations. Essentially, the question is whether you will ensure those private fields are accessed in a thread-safe manner. That’s the advantage of the VisitContext, which is guaranteed not to be shared among threads, or even among consecutive queries. But of course, there are other means of implementing similar functionality, e.g. we’ve observed people using ThreadLocal for that…

  2. I am using a new DefautDSLContext for every queries and and then set the a new VisitListener in the context :

    context.configuration().set(DefaultVisitListenerProvider.providers(new AccountIdFilter(accountName)));

    I would think that every query has it’s own instance of AccountIdFilter.

    Anyways, thank you for the quick anwser. I will add some tests to make sure that those private fields are accessed in a thread-safe manner.

    • I am using a new DefautDSLContext for every queries and and then set the a new VisitListener in the context […] I would think that every query has it’s own instance of AccountIdFilter.

      If you do this every single time, then yes. Do note, however, that Configuration has been designed to be shared among threads. Ideally, you would be implementing a VisitListenerProvider that produces a new AccountIdFilter. It will be called every time for a new query, regardless if you share the Configuration or not. Just to stay on the safe side. You know… https://blog.jooq.org/2015/08/11/top-10-useful-yet-paranoid-java-programming-techniques 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s