Understanding and Resolving the ORA-20987 Error in Oracle APEX

Oracle Application Express (APEX) is a powerful low-code development platform that enables the rapid creation of web applications. However, during development, you might encounter the frustrating ORA-20987 error message:

ORA-20987: APEX – The user requires ADMIN privilege to perform this operation. – Contact your application administrator. 

This error can be disconcerting, but let’s explain what it means and how to fix it.

Causes of the ORA-20987 Error

The ORA-20987 error typically surfaces in the following scenarios:

• Workspace Administration: You’re attempting to perform actions that require workspace administrator privileges, such as creating users, managing workspaces, or modifying workspace settings.
• APEX APIs: You’re using certain APEX APIs (e.g., APEX_UTIL.CREATE_USER) within your application code, and your current user lacks administrative privileges.
• Database Operations: Your APEX application is trying to execute operations requiring elevated privileges on the underlying database schema.

Troubleshooting and Solutions

Here’s a step-by-step approach to resolving the ORA-20987 error:

1. Identify the Source: Determine the specific action that triggers the error. Is it during workspace administration tasks? Does code trigger it in your application?
2. Check Privileges:

• • If you’re performing workspace administration, log in to the Oracle APEX Administration Services as a workspace administrator.
  • If the error stems from code, review the APEX API calls and ensure the executing user has the required privileges. You should grant additional database privileges to the database schema used by your APEX application.

1. Grant Workspace Repository Permissions (if applicable):

• • For workspace-level operations, navigate to Shared Components -> Security -> Workspace Repository.
  • Locate the “Modify Workspace Repository” permission and enable it for the relevant user or role.

Example: Using APEX_UTIL.CREATE_USER

Let’s say you have code using the APEX_UTIL.CREATE_USER API to create new APEX users. The user executing this code will need the ‘ADMIN’ privilege. You have two options:

• Option 1: Log in as a workspace administrator and run the code.
• Option 2: Grant the necessary database privileges to the schema associated with your APEX application. Consult Oracle documentation for specific privileges that might be required.

Important Considerations:

• Granting administrative privileges should be done with caution, considering security implications.
• For complex scenarios or if you have limited database administration access, consult your database administrator (DBA).

Prevention

To minimize the ORA-20987 error in the future:

• Carefully plan your APEX application security model with well-defined roles and privileges.
• Document the required privileges for different types of users and operations within your application.

Let’s Get This Fixed!

By following these steps and understanding the causes, you can quickly troubleshoot and resolve the ORA-20987 error, ensuring the smooth operation of your Oracle APEX applications.