testcontainers-java: [OracleContainer] Unable to execute statements which need system privileges

@gvenzl thank you for your feedback!

Really nice to see developing this thread to useful outcome for us all!

Just to add my 2 cents here without having the history or knowledge of the inner works:

I think that running init scripts with superuser privileges would be appropriate.

I think you would have to run init scripts with superuser privileges as the very setup to get to such an application user for testing would require these privileges to begin with, meaning that without superuser privileges one cannot create a new application user on the database. It is true that the image I produce has a mechanism built-in for that but that’s orthogonal to a Testcontainers user who uses Testcontainers so that he/she doesn’t have to care about underlying images and their implementation details.

In general, there are two broad categories of pre-testing conditions that a user has to think about before running tests:

1. Get the data model into the right shape
2. General DB setup

1. Get the data model into the right shape: That’s usually the step where you make sure that your new tables/columns have been added and your starter set installed. It’s the basic framework that you need to test your application against the schema that your application provides and something that would probably be used and tested in conjunction with a Flyway or Liquibase.

2. General DB setup: That has much less to do with your application schema and functionality and much more about making sure that the database is in the right shape to allow for testing, i.e. the right database parameters, tablespaces, config, etc. setup. It’s the plumbing that in the good, old UAT world would have been done once by the admin who then passed on the connection details to the developers to do their testing against that one, central test database. In the brave, new containerized CI/CD world that setup now needs to be ensured/verified after every new container that has been created.

Generally, 1. concerns itself only within a given schema (unless an application uses more than one schema) and hence the user owning that schema can perform everything necessary (create new tables, indexes, etc.). 2., however, is where one definitely needs superuser privileges, and don’t forget that this step may also involve the plumbing to get to step 1., i.e. the creation of such a schema user that can then be used.

I’m not sure whether this is helping or not, but in a nutshell, there is no easy way around for Testcontainers not to provide a way to run scripts without superuser privileges without also making it potentially harder for users to get their database setup into the right shape.

Hi both!

First of all, thanks for the additional explanation, @tomdevroomen, this helps a lot!

We might think about running withInitScript with elevated permissions (e.g. as SYSTEM) instead of the normal user.

Yes, I think this is the right thing to do. The initialization scripts are intended to further align/tune the database setup according to the needs of the user. Perhaps some database parameters need to be changed, new pluggable database or tablespaces created, all of which require super-user privileges to do so. I have to be careful here, of course, when saying initialization scripts. When I’m referring to these, I mean the mechanism in the image itself by mounting files under /container-entrypoint-initdb.d. All of the .sql scripts found in there are executed as SYS AS SYSDBA, which allows users to change the entire database setup, if wanted, including restarting the DB (sometimes required for parameter changes). I believe that the same concept/mechanism is valuable to be exposed via Testcontainers as well, after all, the test setup needs themselves are probably the same regardless of whether tests are executed via Testcontainers or other means. Note that the init scripts can still be used to also further align/tune the test setup of the actual application user as well via two mechanisms:

1. Because the super-user can do anything and hence also create objects in the test user schema. This is what @tomdevroomen is doing by adding the TEST. before the table name. That way the super-user creates the table in the test schema.
2. One can always connect and reconnect as the TEST user and do whatever is necessary. So for example, the script could also do this:

-- ADDED
ALTER SESSION SET CONTAINER=XEPDB1;

CREATE TABLESPACE MY_TABLESPACE
    DATAFILE 'tablespace.dbf'
    SIZE 1m;

-- ADDED
alter user test quota unlimited on MY_TABLESPACE;

--> Connect as TEST user <--
CONNECT TEST/<pwd>@XEPDB1

-- Create table as TEST user.
CREATE TABLE MY_TABLE
(
    ID              NUMBER(19) NOT NULL PRIMARY KEY
);

...

The APP_USER is intended to give the application a user to run against that isn’t a super-user. This is so that applications would not test/run with a user that has super-user privileges as these applications will almost definitely not end up running with these privileges in production either. It’s the good, old “yes, running everything as root makes your tests easier but also unrealistic as to what you will encounter in production”. Too often (during my day job) I see all (database) privileges granted to the test system and then headaches, surprises and sometimes horrors when they try to deploy that app into production.

On a side note, @tomdevroomen, there should be no need for this one:

-- ADDED
grant all ON TEST.MY_TABLE to TEST;

Given that you already created the table under the TEST schema, that schema automatically has all privileges on that table because it is the owner of it.