Your DBA is Always Right. Except This One Time.

By Chad Johnson posted 01-31-2018 00:00


DBAs take their responsibility to protect the organization's data very seriously. This is a good thing, however ...

As Oracle Application Express (APEX) has become a 'go-to' tool for web development with Oracle databases, DBAs, developers and managers must consider the security issues surrounding the exposure of a database in a web-facing application. One of the most contentious discussions is whether to install APEX in a production database or to put it in a separate database and use database links or an ETL solution. 

The first inclination of a traditional belt-and-suspenders DBA is to say, "No way, that is a serious threat to security." Developers will often point out the better performance of an app embedded in the data source. Offloading data to a reporting database with ETL, Golden Gate, Active Data Guard, etc., are valid approaches if real-time data is not required. For this discussion we will assume the lag in data updates preclude them.
My opinion is that we can realize the best of both worlds. First, I think we can all agree that fetching data directly without the latency and availability issues of database links will provide better performance. With the proper design, this approach can provide a high level of security for the vital production data.

Believe it or not, DBAs and developers can live in harmony.

Achieving an acceptable level of isolation boils down to three basic rules:

  • Always install APEX in its own schema. (Please do not use SYSAUX – more on that another time.)
  • Allow the application read-only access to the production data with well-planned views, not select privileges.
  • Perform updates only with APIs, no direct table updates. This is extremely important in E-Business Suite environments.

I would also recommend building each APEX application in its own schema. This will simplify change management and increase the specificity you can apply to security.

While not directly related to security, there is one other recommendation I would like to make. As much as possible, put code in PL/SQL stored in the database rather than in APEX pages. This is especially beneficial if you have similar code in several pages of the APEX app. Calling one function from multiple pages means you have only one place to make code changes and all the pages will stay in synch with no extra effort. If you do use database links to access the data, consider placing the PL/SQL code in the source database. Why send a bunch of data across the network if your code will filter out a good portion of it?

Believe it or not, DBAs and developers can live in harmony.

About the Author

Chad Johnson is a reformed CPA who decided years ago IT is more fun than accounting. He is currently an Apps DBA in Polk County, Florida, and a big fan of APEX.