What's the relation of workspace and database users in Oracle Express?

Question

I have created a workspace with APEX, but the password is invalid now.

Then I logged in to the workspace and changed the password. However, the other password for login is not changed.

I am very confused with all these terms in oracle.

I have database username, workspace username, database password, workspace password. Also there is a user manager in the workspace, and these users are different from the other two mentioned. Some of the users can be seen in all_users table, some cannot.

What's the relation between all these kinds of users, and where does this information stored? I have read some material of Oracle, but none mentioned these basic terms.

Answer 1

It is confusing because there are two different (though complimentary) technologies being used here:

1) Oracle Database Server has the concept of database "users" which you can see by querying dba_users and all_users - these are owners of database objects and each automatically gets a schema of the same name. Each of these database users has a password, managed by the database. In the old days we used to provision a separate database user for each end user; nowadays we don't generally. These users are stored in the database data dictionary and are manipulated only using database commands such as CREATE USER and ALTER USER.

2) Oracle Application Express has the concept of "workspaces", each of which may have one or more "users". These users can be ordinary end users, developers or Apex administrators. Each of these users has a password, managed by Apex. These are not related to schemas on the database. These users are stored in the Apex data dictionary, and are manipulated using the Apex admin interface, or via calls to the Apex API (in PL/SQL).

Each apex Workspace is associated with a database schema (= database user) which holds the database objects (e.g. tables, views, etc) needed by the workspace. (Note: a workspace can be associated with more than one database schema).

To make things more confusing, in the default version of Apex that is pre-installed in OracleXE (the free version of the database), the Apex user SYSTEM has the same password as the database SYSTEM user.

By default, Apex applications use the Apex authentication scheme which authenticates users against the Apex data dictionary (as per (2) above). You can, however, use alternative authentication schemes which authenticate users against other repositories (such as LDAP, SSO, or custom schemes).