createAppUser in /container-entrypoint-initdb.d leads to "ORA-01917: user or role does not exist"

[rweisleder]

I have created three files inside /container-entrypoint-initdb.d to create new users and a view for each user.

01-create-user.sql

-- copied from /opt/oracle/createAppUser
CREATE USER TEST01 IDENTIFIED BY "pass" QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO TEST01;

02-create-user.sh

createAppUser TEST02 pass

03-create-tables.sql

CREATE VIEW TEST01.V_DUMMY AS SELECT * FROM dual;
CREATE VIEW TEST02.V_DUMMY AS SELECT * FROM dual;

I would expect that when I start the container, the two users are created and the view is present in both schemas. However, when I start gvenzl/oracle-xe:latest I face a ORA-01917 error. The output is:

CONTAINER: Executing user defined scripts...

CONTAINER: running /container-entrypoint-initdb.d/01-create-user.sql ...

User created.

Grant succeeded.

CONTAINER: DONE: running /container-entrypoint-initdb.d/01-create-user.sql

CONTAINER: running /container-entrypoint-initdb.d/02-create-user.sh ...

Session altered.

User created.

Grant succeeded.

CONTAINER: DONE: running /container-entrypoint-initdb.d/02-create-user.sh

CONTAINER: running /container-entrypoint-initdb.d/03-create-tables.sql ...

View created.

CREATE VIEW TEST02.V_DUMMY AS SELECT * FROM dual
*
ERROR at line 1:
ORA-01917: user or role 'TEST02' does not exist

CONTAINER: DONE: running /container-entrypoint-initdb.d/03-create-tables.sql

CONTAINER: DONE: Executing user defined scripts.

What did I miss?

For a complete example, see rweisleder/test-oracle-xe-with-multiple-users

[gvenzl]

Hi @rweisleder,

Thanks a lot for using these images!

The reason for this behavior is that createAppUser defaults to the pluggable database XEPDB1:

Usage:
  createAppUser APP_USER APP_USER_PASSWORD [TARGET_PDB]

  APP_USER:          the user name of the new user
  APP_USER_PASSWORD: the password for that user
  TARGET_PDB:        the target pluggable database the user should be created in, default XEPDB1 (ignored for 11g R2)

However, any script in initdb (or startdb.d) will be run against the container database (CDB) XE:

The` .sql, .sql.gz and .sql.zip files will be executed in SQLPlus as the SYS user connected to the Oracle instance (XE).

So what happened is that you created a user TEST01 in the CDB XE and a user TEST02 in the PDB XEPDB1. Then, when the script tried to create the views in the CDB, it worked for TEST01 but not for TEST02 because there is no user TEST02 in the CDB.

[rweisleder]

Thank you for the explanation, @gvenzl!

Well, I didn't know the difference between CDB and PDB before. This explains why the scripts behave differently.

From my point of view, the issue can be closed.

[gvenzl]

You are very welcome, @rweisleder!