I've been subscribing to Oracle's cloud-based database, the Autonomous Transaction Database (ATP), for over a month now. Apart from minor problems, we've been steadily adding applications to the platform.

The first major difficulty was understanding the administration. As database and APEX application developers, the four things we need to focus on most when it comes to administration are:

• Server

• Database

• APEX instance

• APEX workspace and users

Server

As I mentioned in the previous article, as developers, we don't really want to be involved in administration. This is why using cloud-based services is a good choice. To get started, there is nothing extra to do other than subscribe. After registration, an Administrators group is created and we immediately become members of it.

The management and allocation of privileges is done through groups. The group can be found under Identity & Security/Domains/Default domain/Groups where you can add users to the group.

And we need to assign policies to the groups. The policies contain the privileges themselves. Policies can be found under Identity & Security/Policies.

Of course, as I wrote, initially we get admin rights and we can do anything in the cloud. However, once we want to add more users and not with administrator rights, we need more understanding.

Database administration

By going into the Oracle Database/Autonomous Database menu, we have to click on our database - I only have one - and we are taken to the database administration.

Here, clicking on Database Actions/Database Users will bring up the default database users, including ADMIN.

Here it is important that Web access is enabled (it is enabled for ADMIN) so that we can open an SQL window.

The ADMIN user has the rights of the classic SYS user and is therefore the main administrator. It allows you to create and manage additional users and privileges.

APEX Instance

APEX instance administration can be accessed via the Database Actions/View all database actions/APEX menu.

In the cloud, the administration of APEX users is very different. After a few hours of trying, I managed to understand the logic of this. When creating a workspace, you have to specify whether you want to use an existing schema or create a new one.

New Schema

When creating a new schema, we do not have the option to specify its name.

The logic surrounding the workspace username and password in Oracle's APEX can indeed be complex. Here's a simplified explanation:

1. Existing Database User:

   • If the workspace username (e.g., DPATAKI) already exists as a database user, the workspace will link to this existing database user.

   • To log into the workspace, use the database password associated with this username.

   • The workspace password you set will be used for the APEX user within the applications created in the workspace.

2. New Database User:

   • If the workspace username does not exist in the database, a new database user is created.

   • In this case, the database password and the APEX password will be the same as the one you set during workspace creation.

3. Privileges:

   • A new user created by an APEX workspace in the database will not automatically have privileges to create connections.

This setup ensures that the database and APEX environments are securely linked while maintaining separate authentication mechanisms for database access and APEX application access.

Existing Schema

I prefer to create the schema first as a database administrator and here only the new workspace associated with it. The reason for this is that the naming conventions I use for schema names are very important to me.

The logic here is the same as in the previous point. The interesting thing is that the database user and the workspace username can be the same.

APEX workspace and users

It logically follows from the previous section that you can only access the APEX workspace with a database name and password. Let's say the login screen shows that this is the case, I just didn't notice it.

In the APEX workspace, the three levels of privilege—Administrator, Developer, and User—function similarly in both on-premises and cloud environments.

However, in the cloud, when you add an Administrator or Developer to a workspace, a database schema is automatically created for them. This automatic creation of a schema can be confusing, but it is likely designed to ensure that each Administrator or Developer has a dedicated space for their work, enhancing security and organization.

On the other hand, creating a simple User does not result in a new schema; the User exists only within the APEX workspace, which simplifies user management for non-development roles.