Manage APEX users from your app

I had one interesting task: to enable application end-users to manage users (to create new users from the app). Of course, I'm talking about APEX users and authentication.

Sounds simple, but is it?! I've done this 10 years ago...no problem...

To make it work I've used apex_util.create_user API:

As noted in a comment above, you need to add Workspace Admin privilege to the user you create so that this user can create other users. Sounds good...but you have one big problem. This user can login to the APEX Builder (of course, you can disable APEX builder access on test/production environments, but who does that in reality 😉).

The thing that worked before (I think last in APEX 5.1) was that you could create a new user that is locked by default:

You can't login to the APEX builder, but unfortunately, you can't login to the app neither.

So I came to a new solution, and it's a simple one. 

By default I add new end-users to predefined User Group (don't forget to create user group before):

After that, on the APEX workspace instance level (Manage Instance > Security > Development Environment Authentication Schemes > APEX Accounts), I've added Post-Authentication procedure that doesn't allow users from that group (in my case APP_END_USERS) to login to the APEX Builder:

...and it works. When you try to login to the APEX builder, you'll get something like this:

*Note: this is only a part of the code. Don't forget to add more security checks to your apps.

...and that's all folks! Stay safe & enjoy!

Tested in APEX 20.2.0.00.20

Reset APEX builder login authentication scheme

If you change Development Environment Authentication Schemes (aka the way you login to the APEX Builder) at the instance level and you don't read a confirm dialog carefully, as I didn't you may struggle to login as the instance admin next time:

In my case, I've changed it to the Database Accounts, and though that doesn't affect the instance admin account. But I was wrong. In my case, the solution was to create a database user with username admin.

After that, I've logged in as instance admin and changed the authentication back to the APEX Accounts.

If you change it to some other Authentication Scheme you may need to use PL/SQL API:

Link in the documentation.

That's all! Stay safe & enjoy!

Tested in APEX 20.2.0.00.20

Bulk submit all region items to session state

Recently I came across one simple but effective solution so why not to share it... 

Colege and I were building a search page with lots of filters on the left side of the screen (similar to Faceted Search).  In the middle of the page, we've created an Interactive Report that uses a function that returns the SQL query. There's a lot of logic going in there and we used the v function to fetch item values (no worries, SQL query uses only bind variables). It was just easier and faster than to put everything into input parameters. Also, I'm not a fan of using functions to return SQL queries, but in this case, it was a perfect match. 

The only problem was that we didn't want to submit the page to get item values into the session state nor to put all the items into region property Page Items to Submit (it's hard to handle that with lots of items). So we needed a quick and easy solution to submit all items from the filter region to the session state and here it is:

First, I've created a dummy AJAX Callback process, that doesn't do anything, just returns empty JSON object:

After that, I've created a simple JS function

Function loops through region items (didn't test it for all possible item types in APEX), adds them to the JS array, and with the use of the feature of apex.server.process that you can put an array of item names in property pageItems, it submits them to the session state.

On success, it refreshes the Interactive Report region.

That's all!

Stay safe & enjoy!

Tested in APEX 20.1.0.00.13