Autonomous DB - The Developers' Playground

As mentioned in a previous post, the Free Tier for Oracle Cloud (OCI) is every Oracle Developer's dream. For a while now, I have hosted a personal Oracle Application Express (APEX) instance on Linode. It currently runs an older Oracle Database 11gR2 Express Edition (XE), and I would like to upgrade it to version 18c. The key issue I had though, was that the existing virtual machine hosted on Linode, only has one gigabyte of RAM and that does not meet the 18c XE minimum requirement of two. That left me procrastinating for a long time, but perhaps a blessing in disguise. With this new offering, the next steps are pretty obvious.

After setting up my new OCI development environment, the next task I looked into was exporting an existing workspace, and then importing it into the new APEX instance, hosted on an Autonomous Database (ADB). During the import process, the you will be asked to either assign an existing schema or create a new one. If you are using an exsiting schema, be sure to include the necessary database privileges for an APEX parsing schema. This all went pretty much as expected, very smoothly!

However, my workspace did not have an Application Express user, commonly called "admin" (ADMIN), that had workspace access and administrative rights. Hence, I was unable to login to the migrated workspace.

APEX in ADB is configured to use database user accounts for workspace access. Similar to configuring the workspace access with LDAP, HTTP Header Variable or Social Sign In authentication schemes, a matching Application Express user is required. If your workspace user matches up with a database user, then that user should have no troubles logging in. This user assumes whatever rights the equivalent Application Express user has been granted.

Suppose I have the following:

• Workspace: myworkspace
• Schema: myschema
• Administrator's username: jdoe

To allow JDOE access to the workspace, create a database user with the same name. Do this using either SQL Developer, SQL*PLus or SQLcl. Instructions to connect to the ADB can be found here. Login as ADMIN, the administrator account created when the ADB was provisioned.

Once connected, execute the command:

create user jdoe identified by "mySuper!P@55";

For the purpose of authenticating to APEX, no specific privileges or quota's are necessary. Login to the workspace using the workspace and database user's credentials.

The Application Express account with the same username will need to have its password reset. If this is not done, you will continually be prompted to do so. Strangely, clicking on that big blue button and then using the password change dialog does not seem to perform this successfully for me. What worked was using the user and group management utilities and then forced setting the user's password.

Alternatively, before exporting the workspace, create an APEX workspace administrator with the same username as its schema, e.g. MYSCHEMA. Note that the ADB's APEX Instance Administration functionality is severely limited and does not appear to allow management of workspace users and groups, so the user must be added from the old instance.

Workspace Authentication with Database Accounts

How Oracle has laid out the approach for managing the APEX environment is limiting, but for good reason. It also does present a different perspective on how an administrator manages developer access to the resource.

We know that with ADB, we have access to a variety of tools for using the dataabase:

• APEX
• SQL Developer Web (SQLDEVWEB)
• Oracle ML SQL Notebooks (OML)
• SQL Developer (SQLDEV)
• SQLcl
• Oracle REST Data Services (ORDS); and
• Traditional connectors like OCI, JDBC and ODBC.

An indispensible item in an APEX developer toolchain is access to the database schema. The ability to browse, modify and compile schema objects are an essential part of what we do. Depending on company policies and best practices, there are varying access levels and approaches. For example, workspaces available on apex.oracle.com, only allows developers to access the database schema through SQL Workshop. I'm sure you'd agree, this impacts developer productivity.

For corporate APEX instances however, it's not uncommon for database administrators to dish out the schema credentials to developers, either in the form of usernames and passwords, or an encrypted SQLDEV connnection export file. But what happens when the password expires, or one of the developers decide to change the it?

One approach I'm inclined to use these days is to create for developers, a personal account, and then grant them ability to proxy as the APEX schema user.

-- As a privileged user, execute:
alter user myschema grant connect through jdoe;

In the context of APEX+ADB, here are some examples of how we could benefit from this approach:

• JDOE uses one account to access APEX App Builder and workspace schema user using the tool of choice. For example, JDOE could connect to the database as MYSCHEMA by simply authenticating using the username jdoe[myschema] and logon with his/her password.
• DBAs can, through profiles, apply standards like password expiration and complexity. When a developer leaves the organisation, there is no need to reset the schema password and communicate that to everyone else involved.

SQL Developer Web

To enable access to a schema, it must first be REST-enabled. For schemas linked to APEX workspaces, one option is to use the RESTful Services utility. For other schemas though, the official documentation dictates that we execute the procedure ords_admin.enable_schema, for example:

begin
  ords_admin.enable_schema(
    p_enabled => true
    , p_schema => 'JDOE'
    , p_url_mapping_type => 'BASE_PATH'
    , p_url_mapping_pattern => 'jdoe'
    , p_auto_rest_auth => null
  );
  
  commit;
end;

To access SQLDEVWEB, JDOE would have to use the URL in the following pattern:

https://<TENANT_CODE>-<ADB_NAME>.adb.<REGION_ID>.oraclecloudapps.com/ords/<SCHEMA_ALIAS>/_sdw/?nav=worksheet

where

• <TENANT_CODE> - a random string assigned to all ADB resources in the tenant. This can be easily obtained using the links in the Service Console.
• <ADB_NAME> - the ADB's database name.
• <REGION_ID> - the region identifier where the ADB instance was created in.
• <SCHEMA> - the value specified in p_url_mapping_pattern above.

Unfortunately, it appears that SQLDEVWEB currently does not support proxy user access. Hopefully this would be possible in the future.

Oracle ML SQL Notebooks

I haven't done a lot with this tool, but if you are familiar with Jupyter, it sounds very similar, just seemingly SQL-centric, designed for data analytics and a means to keep good records of data processing workflows.

Enabling database accounts access to OML is pretty simple and straightforward. Either enable users through OML as ADMIN, or grant users the role OML_DEVELOPER. OML users are provisioned personal workspaces that they could then grant other developers access to. One scenario in how this might be useful, is for MYSCHEMA to grant JDOE access to its workspace for running analytics and visualization code that had been collected through an APEX application.

All these is cool isn't? And it's just the beginning!