Do not use sys_context('userenv', 'authenticated_identity') for schemas

[PhilippSalvisberg]

In the commits ec01236 and d34b6ee the use of the pseudo column

user

was replaced with

sys_context('userenv', 'authenticated_identity')

Using this context variable is a good idea for end users connecting to the database. It can be used for database users and for enterprise users managed outside of the database, e.g. via Oracle Enterprise User Security. However, it's a bad idea to use for schemas. The following example shows why:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> column user format a10
SQL> column auth_id format a10
SQL> column owner format a10
SQL> column table_name format a10
SQL> connect bond@pdbea51
Enter password:
Connected.
SQL> select user, sys_context('userenv', 'authenticated_identity') as auth_id from dual;

USER       AUTH_ID
---------- ----------
EUS_USER   BOND

SQL>
SQL> create table t (c1 number);

Table created.

SQL>
SQL> select owner, table_name from all_tables where table_name = 'T';

OWNER      TABLE_NAME
---------- ----------
EUS_USER   T

SQL> grant select on t to public;

Grant succeeded.

SQL> connect m@pdbea51
Enter password:
Connected.
SQL> select user, sys_context('userenv', 'authenticated_identity') as auth_id from dual;

USER       AUTH_ID
---------- ----------
EUS_USER   M

SQL> create table t (c1 number);
create table t (c1 number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>

The enterprise users BOND and M are authenticated via LDAP, but they are connected to the Oracle Database user EUS_USER. This user has the create table privilege, which usually makes no sense. In this case, it shows that both end users share the same Oracle Database user. All objects are owned by this database user. It's not possible that objects are owned by sys_context('userenv', 'authenticated_identity').

Based on this information, the pseudo column user should be used instead of sys_context('userenv', 'authenticated_identity') and the changes made by the commits mentioned above should be reverted.

Thanks to @rvo-cs for pointing this out in another channel.