Date
25 June 2021 | Version APEX 20.2
Low-Code User Managment – EEK!
In Part I, I asked: can I create an effective user management system using a low-code approach within Oracle APEX (v20.2)?
The answer is “No” or ought to be “No”. Maybe technically one might say “YES”, but it should NOT be done. I’ll will discuss why and how to fix this problem in this article.
When I envisioned writing this article, even through yesterday, I thought I would issue a series of warnings and caveats, which I will go through later. Then this morning while testing and polishing the sample application for publication, I locked myself out (again). A day’s worth of work is locked up, error messages blow, and while I am confident that with another couple of hours of effort, I can un-mess this mess, there are just too many risks.
As a newbie APEX developer, I spent hours reading articles and following the footprints of others. My feeling now is that if I lay a track of prints, some other well-intentioned human being will follow my path and miss my warnings, dismiss my risks, ignore my pointing fingers, and try anyway.
Of course, you are welcome to try to create a low-code user management system that incorporates the internal APEX users and APEX’s Access Control features. I’ll give you the links, but I’ll not give you the application.
In nearly a decade of reading articles and posts about Oracle APEX, I do not believe I have run across an author/developer who said: “Just Don’t Do It!”. Today, that is me.
Low-Code – A Definition
Low-code software application development involves techniques focusing on drag-n-drop, select lists, and check boxes to create an application. Oracle Application Express (APEX) is a free tool available for every modern Oracle database. It provides robust and professional level tools for the design, development, and operation of high-quality, secure, web-based applications.
This cloud-based tool has demonstrated its capabilities. One of our commercial applications underwent scrutiny by the U.S. Department of Homeland Security (DHS) cybersecurity teams. Our application has utilized by inspectors general. This application incorporates 58,000 lines of code in packages and uses over 300 tables. Granted this application has successfully managed $5B USD in federal funds and while managing/storing 400,000 PDF documents. This application is not low code.
My business partner has written a few low-code and no-code APEX applications. One of his low-code applications fetches the pool temperature and weather statistics of the pool outside of his Florida home and graphs changes the during the day. Low code because he used our template for the API with this weather station he bought and the rest was basic skills with a mouse. Maybe one or two simple queries for his graph. He’s also done a no-code application to help the homeowner’s group manage documents and photographs. It provided more power and capabilities than WordPress with nearly no more need for coding skills. APEX met the demands.
In each case, John creates users within APEX then assigns them to an application with the APEX Access Control Feature.
Low-code – write a few queries with SELECT statements, call a procedure or function as needed. Likely does not have any packages of compiled code.
No-code applications don’t even require queries or calls to procedures/functions. APEX can do it – does it all the time for people, even me!
Why Low-Code / No-Code?
Isn’t that the dream? How many of us have heard the line: “I’ve got an app for you” followed with some version of well all be rich. Anyone can write an application, that’s the promise. We have spent decades lowering the threshold for entry and making database application development more approachable and more accessible.
I love low-code/no-code because it serves me as a rapid prototyping and demonstration tool. I can do things immediately, now! It will work. I don’t have to destroy a prototype as ideas mature, as clients provide feedback. Low-code/no-code serves both as the pencil sketch pad foray to finished product.
Low-code / no-code works great for John and thousands like him. Toss Excel to the bin and have a website that uses data for managing and viewing data dynamically. Perfection.
A Gap
As mentioned in Part I, APEX includes a feature that can be installed with a new application called “Access Control”. Implied in this feature is that this Access Control process predicate on APEX users. This is not the case, this feature can be used with custom authentication process as described by Doug Gault in his article.
Given Oracle and the great team at developing our APEX tools emphasize the no-code/low-code benefits of APEX for rapid application development, I decided to explore using a low-code techniques for user management. In short, although I did not technically fail, the risks of what I explored significantly outweigh the benefits.
And, in subtle ways Oracle reinforces my positions.
Low-Code User Management
I decided that my low-code approach for user management should rely on Oracle data storage and Oracle procedures/functions. If I do need to write PL/SQL blocks of code, they ought to be:
- Within APEX and not in a package, procedure, or function
- A few lines long, certainly under 50 lines
In time, I conceded that if I can write a sample application that pushed these code limits a bit, but provided that application as a free download from my Github (don’t look, it is not there). Then sample app could be used by others risk-free in a low-code/no-code environment.
I was 99.999% successful. I was ready to push to GitHub and give the app to John for his homeowner’s association and his weather station thingy. Then, for the third time, I entirely fried my APEX environment. I believe myself to be expert, and the severity of the mistakes I made informed me that the architectural foundation of my premise and experiment Failed.
If this were an episode of Myth Busters, I’d stamp “Busted” on this effort.
Risk Identifier #1 – No Sample App from Oracle
I did ask myself why doesn’t Oracle, who is promoting low-code/no-code, provide a sample app for user management? Or even a softer edge to APEX that permits incorporation into an application?
This is a clue.
A low-code method would have to gain access to “Application Express Accounts” as show here. This is an image from Shared Components > Security Authentication Schemes.
Oracle provides a strong set of tools to manage APEX accounts.
A sample app, which can be installed by any developer, would require:
- Administrator Rights to APEX;
- Would have access to modify the rights to nearly any developer and administrator within APEX.
When I examined Risk Identify #1, I thought Oracle was protecting itself from lawsuits and the malice a disgruntled developer having a bad day at work. I was wrong. It protected APEX from me, a well-intentioned, experience developer working alone in a private space.
APEX relies on “Application Express Accounts” for proper access, and function. Sample applications are often treated as learning tools, toys, and templates. It is a bad idea to let a toy destroy your work environment.
Risk Identifier #2 – Removing Important Security Feature
In order to let developers and APEX applications modify APEX users accounts, you must remove a layer of protection from APEX. Buried deep in the security attributes for an application (Shared Components), there is a Check Box at the bottom of a page that reads:
Runtime API Usage
When checking the “Modify Workspace Repository”, you’ve now let application developers modify APEX user accounts and all sort of internal stuff.
If you want a security for your corporate or institutional environment, if you value your job, if you value your data, leave that box unchecked.
Risk Identifier #3 – Must be APEX Workspace Administrator
Within APEX there are classes of users and permissions. The king is Internal/Admin as this user sets up workspaces and users. Next down the ranks are Workspace Administrators. These folk can edit user permissions within a workspace: change passwords, do that cool stuff. Down the ranks another step: Workspace Developers. Developers can write applications and depending on toggles, access SQL and other stuff. The bottom rank are the Not-an-Admin and Not-a-Developer. This is the group of application users.
In order for an application to Modify Workspace Repository (APEX accounts) the application user must be a Workspace Administrator.
Seriously, we should never give the user of an application Workspace Administrator rights. It is an evil idea. What could possibly go wrong?
Risk Management
These protections exist on purpose!
Before starting I assess the risks and developed strategies to minimize the risks
- Developed app at the free tier of Oracle Cloud Infrastructure with APEX installed
- I did NOT do this on my own systems
- I did NOT do this on my corporate systems
- Know my PST (Primary, Secondary, Tertiary Actions)
- Primary recovery action: Run application backups
- Secondary recovery action: Have spare users, don’t mess with Internal/Admin
- Tertiary recovery action: Wipe my OCI space clean and start again
I still found trouble – I mean, I created my own trouble.
Mistake #1 – Update APEX Account with Null data
Whilst I did a complete fetch user and populated matching page items, I really didn’t want to update every parameter when I pushed the data back with Edit User.
First, I tried to limit the data I fetched but commenting out parameters I did not want. The code would not verify. Fail!
Next, I limited the data I pushed by commenting lines from apex_util.edit_user. Hey, this code compiled just fine! I edited my name and destroyed my APEX account. Yes, sent nulls back to all sort of important stuff.
I set my group IDs and developer roles to nulls. I turned my own account from Workspace Administrator to a User.
Recovery Step – Log in as Internal/Admin and fix my own account.
Mistake #2 – Remove All Administrators from Application
When using the APEX Access Management feature, you must have at least one account identified as an “Administrator” within the access control list for the application. That account must also be a Workspace Administrator.
I am working through the process of changing rights from application administrator to application contributor to application reader.
One can not just execute the repair code from SQL Developer. You must be within an APEX Session (essentially within an APEX application). I wrote code that executed blindly on page 1 to add myself back to the application administrator group.
Recovery Step – Execute a PL/SQL block from within an APEX session to add a workspace administrator user to access control list as an application administrator.
Prevention Step – I wrote come elegant code that prevent the user from removing the rights of the last administrator. This was not a novice level query, but it was short. This code does not qualify for “low-code/no-code”. It did prevent me from repeating this mistake.
Mistake #3 – WTF?
At Mistake #3, I decided I’d had just enough. I thought to myself: “Oh, I should permit a user to change their own password”. I popped in a quick modal page with two page items: old password, and new password. I dropped in the necessary reset password call, definitely a low-code effort (if you really want to leap after me, it is apex_util.reset_password).
I change my user’s password.
Now none of my users can log into my sample application. I couldn’t log into APEX. I fixed my APEX account with Internal/Admin (again). Found the application wrecked-beyond-recognition and decided I had enough evidence to write this article.
Don’t Do It
Hot coffee is often served in cups that read: Contents Maybe Hot. Fuel stations provide warning signs about gasoline being flammable.
If you care about your APEX environment, do not permit an external application to modify workspace repository stuff such as APEX user accounts. Hot stuff is hot. Don’t touch a hot stove top.
Do not give the administrator of an application the full permissions needed to be an APEX Workspace Administrator.
As a long-time paramedic and chief of an emergency medical service, I underwent annual training on infectious disease prevention. The lesson often started and ended with: “Don’t touch it; Don’t touch it; Don’t touch it.” Today, it would include, “wear a mask”.
Oracle APEX requires a secure and non-volatile environment in order to operate well, that includes the parameters and passwords for APEX administrators and developers. Please do isolate your applications from your development environment.
Hey Oracle, Help Us!
In striving to capture more marketspace in the low-code/no-code environment, the team at Oracle has the opportunity to provide an application-specific suite of tools for low-code/no-code user management.
Maybe there is a place for developers initially use their own developer accounts, but even that fosters bad security practices.
We should ask Oracle to remove the ability to permit applications to be authenticated against developer accounts. Instead, rely on a distinct and isolated dataset for application user accounts.
With cybersecurity risks front-and-center in news, we should all do every thing we can to envision a more secure world. Linking your database security, your developer’s security, and your application security with a single account is unwise.
Yes, No Code & No Sample Application
I am not providing sample code, code to copy, or a sample application. If you wish to recreate my stupid mistakes, go do your own research. I will not enable that level of stupid. Oracle has all the documentation you need.
Yes, this is an Oracle APEX blog post with no code.
Christina Moore
25JUN2021
