Unable to access DB schemas/tables with default user

[leugimlenipse]

Hi, i'm trying use testcontainers into an existing Spring boot project with a basic configuration i found that works for my integration tests.

I'm using the gvenzl/oracle-xe image to create the container and recreate an existing database with a sql script using .withCopyFileToContainer(MountableFile.forClasspathResource("testcontainers/init.sql"), "/container-entrypoint-startdb.d/init.sql");

So far, the db initialization works. The problem is, the db i'm creating has multiple schemas, which are being created in the container db, but once it is ready to use and everything has been created, it seems like i'm not being able to access the tables/schemas.

According to the answers in this issue, i understand that there is a default "test" user which, from my app's logs, is the one being used when connecting to the db:

22-10-2023 19:47:07 [main] INFO 🐳 [gvenzl/oracle-xe:latest].tryStart - Container gvenzl/oracle-xe:latest started in PT2M52.8713929S
22-10-2023 19:47:07 [main] INFO c.e.u.c.i.config.CommonTestContainer.start - Username: test
22-10-2023 19:47:07 [main] INFO c.e.u.c.i.config.CommonTestContainer.start - Password: test

I'm not specifying the user/password when creating the container (i'm not using .withUsername and .withPassword), and even when specifying them, the problem persists.

I know that the schemas and tables are being created because i can query them logging into the db via the container's terminal with "sys as sysdba/password". In my init scripts, i have tried excecuting a SQL block to grant privileges to the 'test' user in all schemas and tables, but it still doesn't seem to work since i'm still getting the same error message after trying to run the tests (upon initialization, the app tries do run a 'select' query for an auditing service):

22-10-2023 19:47:42 [main] WARN o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - SQL Error: 942, SQLState: 42000
22-10-2023 19:47:42 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - ORA-00942: table or view does not exist

Some things i'm trying to figure out are:

• When the script to grant privileges to the "test" user is excecuted, it seems to work properly since i'm not getting any error logs.
• When connecting to the db via the container's terminal with 'sys as sysdba' and checking the user list, there is no 'test' user, which is giving me a lot of confusion. But i can see the list of users that define the schemas (created by the init scripts) and access the tables.
• When connecting to the db via the container's terminal with the 'test' user, i can see the list of users that define the schemas, but i cannot access the tables.

For a better explanation, this is what i'm talking about:

sh-4.4$ sqlplus sys as sysdba/password@//localhost:1521/XEPDB1
SQL> select username from all_users;
USERNAME
--------------------------------------------------------------------------------
SYS
AUDSYS
SYSTEM
MYUSER1
MYUSER2

sh-4.4$ sqlplus test/test@//localhost:1521/XEPDB1
SQL> select username from all_users;
USERNAME
--------------------------------------------------------------------------------
SYS
AUDSYS
SYSTEM
MYUSER1
MYUSER2
TEST

So, what could be happening for the default user not being able to access the db schemas/tables? And what other options to i have?

[gvenzl]

Hey @leugimlenipse,

Thanks a lot for using these images!

I think your issue is that your init script doesn't contain this line at the top:

ALTER SESSION SET CONTAINER=XEPDB1;

And hence the schema is created in the XE container database rather than in the XEPDB1 pluggable database.

Hope this helps!