VerifyPermission throws errors on Oracle database if permissions are given to both User and Power User on a layout portlet

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.X EE, 7.0.0 DXP SP1
• Fix Version/s: 6.2.X EE, 7.0.0 DXP FP7, 7.0.0 DXP SP1, 7.0.3 CE GA4, Master
• Component/s: Core Infrastructure, Core Infrastructure > Permissions, Core Infrastructure > Verify Extender
• Labels:
  • liferay-digital-enterprise-70-sp1
  • liferay-fixpack-de-7-7010
  • liferay-fixpack-portal-131-6210
• Branch Version/s:
  7.0.x, 6.2.x
• Backported to Branch:
  Committed
• Story Points: 1.25
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/43887

Description
If permissions are given to both the User and the Power User role for any portlet on a page, running the VerifyPermission process will cause errors to be thrown, which causes the verify process to fail and the portal to shut down. This is only reproducible on an Oracle database.

This problem was introduced in LPS-67131, which attempted to fix the performance problem caused by the deleteConflictingUserDefaultRolePermissions method. LPS-67131 removed the low-performance method and instead used Oracle's error logging feature to get around the potential errors. However, this was never actually tested on Oracle, and it fails to work because Oracle's error logging functionality is not invoked in the case that UPDATE or MERGE operations raise a unique constraint or index violation (see https://oracle-base.com/articles/10g/dml-error-logging-10gr2).

The goal of this LPS will be to find a fix for this issue for Oracle databases that does not significantly reduce performance.

Steps to reproduce:

1. Start up the portal connected to an Oracle database, and log in as Test Test.
2. Go to "My Profile"
3. Select any portlet on the My Profile page (e.g. the Navigation Menu portlet in ee-7.0.x, or the Language portlet in ee-6.2.x).
4. Modify this portlet's permissions for the User role and the Power User role. Save your modifications.
5. Shut down the portal.
6. Add the following lines to your portal-ext.properties file:

   verify.frequency=-1
   verify.processes=com.liferay.portal.verify.VerifyPermission
   

7. Try to start up the portal. The VerifyPermission process will run because of the lines you added above.

Expected result: The VerifyPermission process is completed successfully and the portal starts up.
Actual result: The VerifyPermission process fails with the following error:

11:20:39,393 ERROR [localhost-startStop-1][DBUpgrader:253] Unable to execute verify process: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BPR7228B.IX_8D83D0CE) violated_ [Sanitized]
com.liferay.portal.verify.VerifyException: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BPR7228B.IX_8D83D0CE) violated_ [Sanitized]
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:73)
        at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:113)
        at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:78)
        at com.liferay.portal.verify.VerifyProcessUtil.verifyProcess(VerifyProcessUtil.java:47)
        at com.liferay.portal.events.StartupHelper.verifyProcess(StartupHelper.java:175)
        at com.liferay.portal.events.StartupHelperUtil.verifyProcess(StartupHelperUtil.java:87)
        at com.liferay.portal.tools.DBUpgrader.verify(DBUpgrader.java:247)
        at com.liferay.portal.events.StartupAction.doRun(StartupAction.java:243)
        at com.liferay.portal.events.StartupAction.run(StartupAction.java:85)
        at com.liferay.portal.servlet.MainServlet.processStartupEvents(MainServlet.java:1324)
        at com.liferay.portal.servlet.MainServlet.init(MainServlet.java:268)
        at javax.servlet.GenericServlet.init(GenericServlet.java:160)
        at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1280)
        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1193)
        at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1088)
        at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5176)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5460)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:633)
        at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:656)
        at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1635)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BPR7228B.IX_8D83D0CE) violated_ [Sanitized]
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1814)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
        at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:292)
        at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:262)
        at com.liferay.portal.kernel.dao.db.BaseDBProcess.runSQL(BaseDBProcess.java:52)
        at com.liferay.portal.verify.VerifyPermission.fixUserDefaultRolePermissionsOracle(VerifyPermission.java:467)
        at com.liferay.portal.verify.VerifyPermission.fixUserDefaultRolePermissions(VerifyPermission.java:320)
        at com.liferay.portal.verify.VerifyPermission.doVerify(VerifyPermission.java:228)
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:70)
        ... 27 more
11:20:39,441 ERROR [localhost-startStop-1][MainServlet:271] com.liferay.portal.kernel.events.ActionException: com.liferay.portal.verify.VerifyException: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BPR7228B.IX_8D83D0CE) violated_ [Sanitized]
com.liferay.portal.kernel.events.ActionException: com.liferay.portal.verify.VerifyException: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BPR7228B.IX_8D83D0CE) violated_ [Sanitized]
        at com.liferay.portal.events.StartupAction.run(StartupAction.java:91)
        at com.liferay.portal.servlet.MainServlet.processStartupEvents(MainServlet.java:1324)
        at com.liferay.portal.servlet.MainServlet.init(MainServlet.java:268)
        at javax.servlet.GenericServlet.init(GenericServlet.java:160)
        at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1280)
        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1193)
        at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1088)
        at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5176)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5460)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:633)
        at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:656)
        at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1635)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: com.liferay.portal.verify.VerifyException: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BPR7228B.IX_8D83D0CE) violated_ [Sanitized]
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:73)
        at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:113)
        at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:78)
        at com.liferay.portal.verify.VerifyProcessUtil.verifyProcess(VerifyProcessUtil.java:47)
        at com.liferay.portal.events.StartupHelper.verifyProcess(StartupHelper.java:175)
        at com.liferay.portal.events.StartupHelperUtil.verifyProcess(StartupHelperUtil.java:87)
        at com.liferay.portal.tools.DBUpgrader.verify(DBUpgrader.java:247)
        at com.liferay.portal.events.StartupAction.doRun(StartupAction.java:243)
        at com.liferay.portal.events.StartupAction.run(StartupAction.java:85)
        ... 19 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BPR7228B.IX_8D83D0CE) violated_ [Sanitized]
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1814)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
        at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:292)
        at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:262)
        at com.liferay.portal.kernel.dao.db.BaseDBProcess.runSQL(BaseDBProcess.java:52)
        at com.liferay.portal.verify.VerifyPermission.fixUserDefaultRolePermissionsOracle(VerifyPermission.java:467)
        at com.liferay.portal.verify.VerifyPermission.fixUserDefaultRolePermissions(VerifyPermission.java:320)
        at com.liferay.portal.verify.VerifyPermission.doVerify(VerifyPermission.java:228)
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:70)
        ... 27 more

Reproduced in:
master: Unable to test in master because Oracle databases are no longer supported in master. However, the same problem in the code exists in master, so we will commit this fix to master to keep the code consistent between it and ee-7.0.x.
ee-7.0.x: Yes, reproduced in ee-7.0.x (4e29392d06d5db2d9d69098b5b461346870fae86)
ee-6.2.x: Yes, reproduced in ee-6.2.x (a6a02dd612a7e32ac0e46a7d07ffe9f9c7c8527e)