Understanding and Resolving the ORA-20987 Error in Oracle APEX

Oracle APEX (Application Express) is a popular low-code development environment for building web applications on Oracle databases. As unique as it is, developers may sometimes encounter the ORA-20987 error. This error message can be a bit cryptic, but don’t worry – this blog post will guide you through its causes and solutions.

What is the ORA-20987 Error?

The ORA-20987 error in APEX usually looks like this:

ORA-20987: APEX – User [username] requires ADMIN privilege to perform this operation. – Contact your application administrator. 

This error indicates that a specific user account in your APEX application is trying to perform an operation requiring administrative privileges that they don’t possess.

Common Causes of ORA-20987

1. Workspace Access: One primary reason for this error is when a developer or user without the ‘APEX_ADMINISTRATOR_ROLE’ attempts to access an APEX workspace. Workspaces are the primary units of organization in APEX, and only administrators have complete control.
2. Modifying Workspace Repository: When a user without adequate privileges attempts to change the workspace repository (which stores application metadata), the ORA-20987 error can occur.
3. API Calls: Certain APEX APIs, like APEX_UTIL.CREATE_USER requires administrative access. If a standard user tries to execute such API calls, they’ll encounter this error.

How to Troubleshoot and Fix ORA-20987

1. Check User Roles: The first step is identifying the user account that is encountering the error and verifying their roles within the APEX workspace.

• • Navigate to Manage Workspaces within your APEX Administration area.
  • Select the affected workspace.
  • Go to Manage Developers and Users and check that the user has the necessary administrator roles if the action they’re trying to perform requires it.

1. Adjust Application Properties (If Necessary):  Sometimes, the issue is misconfiguration within the APEX application.

• • Access your application properties.
  • Locate the ‘Modify Workspace Repository’ security attribute.
  • If this is set to ‘Administrators Only,’ alter it to include the appropriate roles or even ‘Public’ if the action should be allowed more broadly. Proceed with caution here, as this has security implications.

1. Grant API Privileges (If Required): If the error arises from using specific APIs:

• • An APEX administrator will need to directly grant the necessary privileges to the user within the database for operations like APEX_UTIL.CREATE_USER.

Additional Tips

• Always observe the principle of least privilege. Grant administrative access only when strictly necessary.
• Thoroughly review your application’s security settings and user roles to avoid future occurrences of ORA-20987.
• If you’re still stumped after these steps, consult the Oracle APEX documentation or seek help on the Oracle APEX forums – the community is a great resource!