Security considerations
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
While policies are a great way to implement row level security, please beware that there are some significant limitations to this feature being implemented at the client side, instead of the server side. If you can, please consider using also (additionally), any vendor specific policy implementation, such as e.g.:
- Oracle's Virtual Private Database feature
- PostgreSQL's
POLICYfeature - Any custom implementation based on views and context variables
Some specific limitations of jOOQ's implementation include:
- Policies only work on jOOQ expression trees. They do not work on plain SQL templates, which are "opaque" to jOOQ. You can prevent most accidental plain SQL template usage by using a compile time checker, but these can be circumvented easily as well. This is particularly true also for views and stored functions, to which policies do not apply automatically.
- Anyone with access to your JDBC driver can run queries against JDBC directly, and jOOQ will not be aware of those.
- Anyone with access to your Configuration can remove the
org.jooq.PolicyProviderand run an unrestricted query against your schema again. - Policies do not prevent executing statement types, including DDL statements. Use (server side) privileges for that.
- While policies may be able to access expressions from column DEFAULTs or computed column expressions, policies cannot access trigger logic or any other logic that will interact with your DML statements, and as such, it would be possible for a trigger to produce a value that wouldn't be allowed by the policy, for example. The same is true for IDENTITY values or any other values generated from an expression with side effects, which cannot be predicted before an
INSERTstatement. - While jOOQ will prevent access to data outside of the scope of a policy, there are always means of deducing data from secondary effects, such as time-based searches that check for the presence or absence of data based on the execution time of certain predicates.
As the above list illustrates, while jOOQ's POLICY feature does provide a lot of value, it is unwise to expect it to provide a very high level of security to your application. Security best practices should be applied on every layer of your application, including within the database, at the query layer, service layer, and in the UI.
See also related limitations of other code generation features here.
Feedback
Do you have any feedback about this page? We'd love to hear it!