Need to overhaul APEX object and system privileges - Oracle EE 12c, APEX 19

Dave_VZ

I'm new to a site and to APEX as an Oracle DBA. Nothing fancy, the workspace groups are APEX.RO, APEX.RW, and INTERNAL. The RO end-users use APEX just for production queries, but it seems the developer role has to be assigned for an end-user to access the SQL Workspace. Therefore, end-users inherit the developer classification. Next, for reasons fully undocumented, the former contract holder granted excessive system privileges directly to the APEX.RW account.

Has anyone experienced a reason why APEX security cannot follow recommended, time-tested permissions that are least-necessary? Or a way I might open up SQL Workspace to end users? TIA.

Scott Wesley

Opening up SQL Workspace to end users to execute ad hoc queries is ... not normal.

That said, there's a layer here not mentioned - each workspace only has access to certain schemas - using the 'time tested' Oracle security permissions.

Normally, database privileges are abstracted from the user using the Parsing schema of the application, making the written application the proxy for delivering whatever the user needs.

Dave_VZ

Good insight, Scott. Most of the project's customers know only the browser-based application. My use of end-users in this context is a subset of customers authorized as subject matter experts. Thus my RO workspace allows SELECT and EXECUTE on appropriate objects. Other venues such as SQLcl or SQLDeveloper are not available to them. To my noob level of understanding, the only way in APEX for these limited users to have the SQL Worksheet, however, is to categorize them as developers rather than not.

The actual developers on the project have RW roles in the non-production instances. My concern here is that they were previously granted unnecessary system privileges. That has to be fixed, but "delicately".

Scott Wesley

Yeah, sounds like a clean-out is required.

And an alternative for power users is to throw some Interactive Reports at them. They could have a basic set of data that they could then filter, pivot, compute, chart etc.

Rich H

Another avenue might be to consider the packaged Data Reporter application as it has in-built SQL query capability when creating a data source, it may need a bit of local customization depending on your requirements.

Dave_VZ

Scott, Rich, I appreciate the input. As a supporting contractor in a government project, I have neither bandwidth nor mandate to tinker with introducing new features. The application customers are content with what they have. I'm rocking the boat because there's a new expectation for audit compliance. The parsing/master schema was granted DBA in addition to all system-privileged roles -- and all with admin option. A clean out indeed. I had hoped, perhaps foolishly, that APEX documentation would include some idiot checks, like, here are the privileges a properly installed master schema should have.

The workspace customers are subject matter experts, who are focused on ferreting out data problems that the app cannot handle. Therefore, introducing reports is not a solution.

Dave_VZ

Attempting a restart on this, setting aside the previous meanderings. APEX_PUBLIC_USER, newly installed, should have which Oracle privileges and roles?