Security is important, especially on the data access layer. Most commercial databasese allow for fine-grained privilege control using database access grants. For instance, you would be restricting access from a user to a certain set of tables (or even better: views), via GRANT statements:
GRANT SELECT ON table TO user;
With this fine-grained access control, write operations on certain database objects can be prevented directly in the database.
What if that’s not possible?
Not all databases ship with sophisticated access privilege implementations, or perhaps, your application cannot profit from those features for operational reasons. In that case, you should at least be able to implement security on the client, e.g. by using jOOQ’s
ExecuteListener
(for coarse grained access control), or by using jOOQ’s
VisitListener
(for fine grained access control).
An example using an
ExecuteListener
might look like this:
class ReadOnlyListener extends DefaultExecuteListener {
@Override
public void executeStart(ExecuteContext ctx) {
if (ctx.type() != READ)
throw new DataAccessException("No privilege to execute " + ctx.sql());
}
}
If you hook this listener into your jOOQ Configuration, you will no longer be able to execute any write operations on that Configuration. It’s that easy!
For more fine-grained control (e.g. a per-table ACL), a
VisitListener
will do the trick. An (very much simplified) example implementation that shows what can be done can be seen here:
static class ACLListener extends DefaultVisitListener {
@Override
public void visitStart(VisitContext context) {
if (context.queryPart() instanceof Table
&& Arrays.asList(context.clauses()).contains(INSERT_INSERT_INTO)
&& ((Table<?>) context.queryPart()).getName().equals("AUTHOR"))
throw new DataAccessException("No privilege to insert into AUTHOR");
}
}
Essentially, this check prevents a client session from running insert statements into the AUTHOR table. A future version of jOOQ will ship with this kind of ACL VisitListener out of the box, when
https://github.com/jOOQ/jOOQ/issues/5197 is implemented.
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
This can also be done by setting the read-only attribute at the [JDBC Connection-level](http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#setReadOnly(boolean)).
Does this really enforce the “read only mode”? The Javadoc reads:
So… “a hint”