We have a joined-subclass relationship for some entities within our hibernate mapping. When delete operation is invoked on the related entities, bulk operations are getting triggered within hibernate-core. And bulk operations attempt to create temporary tables.
But the oracle user is not having any privilege to create tables. The oracle user which is used in the jdbc connection, only has permission to 'insert, update, delete, alter on' on set of tables in a particular schema. That oracle user does not have permission to create tables in that schema.
This is causing 'Insufficient Privelege' issue in Oracle.
Upon inspecting the code a little bit, I found that TemporaryTableBulkIdStrategy is being used by hibernate core. Even though these tables are not created, due to insufficient priveleges, an insert operation and then a delete operation is attempted, which is created an exception: 'org.hibernate.exception.SQLGrammarException: could not execute statement'
The logs are attached, for more analysis.
Even though, we have overriden the Dialect class's method: supportsTemporaryTables() to return false, still it goes ahead and tries attempting operations, on not yet created tables.
As I had debugged the hibernate code, getTemporaryIdTableName() was not returning null, but getTemporaryIdTableDDL() was returning null, since the temporaryIdTableDDL could not be created, because boolean for supportsTemporaryTable() was false.
But, still getTemporaryIdTableName() was used in other locations, during bulk operations for inserting and deleting and since Oracle did not find this table, as it did not exist, it threw an exception.
Providing the context in a summary:
When there are joined-subclass relationships defined in hibernate mapping and if the entities are involved in delete operation, hibernate uses temporaryIdBulkStrategy, which attempts to create temporary tables. But, the user used in jdbc connection does not have permission to create tables, due to which, temporary table creation fails and then, bulk operation fails, due to which, delete operation fails, due to which the application layer above fails.
Hibernate: 4.2.13, Oracle Database Express Edition 11g Release 2
Applied Pull Request upstream.