java - Row Level Security implementation in JOOQ

Question

I want to implement oracle row level security kind of feature in Java using JOOQ library

Here is an example JOOQ query code:

Result<Record> result = dslContext.select().from(Employee.EMPLOYEE).fetch();

The code above will generate SQL as below:

select [dbo].[Employee].Id,... from [dbo].[Employee]

I want to add a where clause to filter data specific to user security as below:

select [dbo].[Employee].Id,... from [dbo].[Employee] WHERE [dbo].[Employee].Security IN (1,2)

Answer 1

Explicit predicates

Unless I'm missing some nice SQL Server feature where rows / records contain a pseudo-column called .Security to implement row level security, you should be able to simply write

dslContext.select()
          .from(EMPLOYEE)
          .where(EMPLOYEE.SECURITY.in(1, 2))
          .fetch();

For more info about jOOQ predicate building, see the manual here:

• http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions

And in particular, the IN predicate:

• http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/in-predicate

General solution using jOOQ's ExecuteListener

Given your comments, you're looking for a general way to patch all SQL statements with an additional predicate, no matter what the particular programmer is typing.

You can do this with jOOQ, but beware of the fact that this will just help you enforcing the predicate, not guarantee it, if programmers bypass jOOQ. What you can do is set up an ExecuteListener, intercepting the renderStart() event in order to patch / replace the query being executed. Something along these lines:

@Override
public void renderStart(ExecuteContext ctx) {
    if (ctx.query() instanceof Select) {

        // Operate on jOOQ's internal query model
        SelectQuery<?> select = null;

        // Check if the query was constructed using the "model" API
        if (ctx.query() instanceof SelectQuery) {
            select = (SelectQuery<?>) ctx.query();
        }

        // Check if the query was constructed using the DSL API
        else if (ctx.query() instanceof SelectFinalStep) {
            select = ((SelectFinalStep<?>) ctx.query()).getQuery();
        }

        if (select != null) {
            // Use a more appropriate predicate expression
            // to form more generic predicates which work on all tables
            select.addConditions(EMPLOYEE.SECURITY.in(1, 2));
        }
    }
}

Of course, there's room for improvement to the above. Feel free to discuss use-cases on the user group

General solution using jOOQ's VisitListener

If you're willing to go deep into jOOQ's internals, you an also try to implement a VisitListener and actually transform jOOQ's AST representation of your query. This is documented here:

• http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-sql-transformation/

General solution using views

While the above works, I would personally suggest you use views for this and hide the actual tables from developers. Example:

CREATE VIEW v_employee AS
SELECT a, b, c, ... 
FROM t_employee
WHERE t_employee.security in (1, 2)

With appropriate grants, you can hide the tables from the developers, making sure they will only ever use the views with your desired predicate always in place