[OracleContainer] Unable to execute statements which need system privileges

[tomdevroomen]

I just updated to testcontainers 1.16.2 with new OracleContainer setup.
Before I could use a initScript to create tablespaces, but now I'm not able to use system user anymore.

The system user has been disabled, but I need to create tablespaces, for which the test user doesn't have privileges.

I've tried to copy the initScript to the container with .withCopyFileToContainer(MountableFile.forClasspathResource("init.sql"), "container-entrypoint-startdb.d/"); but the script wasn't picked up when the container was started.

How should I proceed?

Thank you!

[kiview]

This approach should work, see also the "Initialization script" paragraph in the image docs:
https://hub.docker.com/r/gvenzl/oracle-xe

Can you please share a simple reproducer example, as per https://github.com/testcontainers/testcontainers-java-repro?

[tomdevroomen]

Thank you for your reply.
I worked around this with:
new OracleContainer("gvenzl/oracle-xe:slim") .withCopyFileToContainer(MountableFile.forClasspathResource("testcontainers/init.sql"), "/container-entrypoint-startdb.d/init.sql");

So, my initialization wasn't right.

In the script I execute I explicitly need to add ALTER SESSION SET CONTAINER=XEPDB1 to create my tablespaces.
And need to add TEST schema for tables I create within this init script.

Before 1.16.2 the container was much easier to use, imho.

I'll try to complete the sample asap.

[kiview]

This code looks fine. But do I understand it correctly that it works now?
In this case, we don't need an example.

What aspect exactly was easier to use with the old Oracle XE image? Asking so I can understand if this is something that Testcontainers should provide as an API or something that should be changed in the image (cc @gvenzl).

[tomdevroomen]

Yes, all is working now.

I'd like to provide feedback on the changes made in TestContainers and the Oracle-XE image:

From a testcontainers users view, I'm not allowed to use the SYSTEM user anymore, which makes it more difficult to create database objects, which need sys admin privileges.
I've seen in the code this is explicitly prohibited now (probably with good reasons?)

From a oracle-xe image users view, now the default user and schema is now "TEST".
Meaning tables I create need to be prefixed with "TEST" if I use the Initialization script.
Plus I need to add ALTER SESSION SET CONTAINER=XEPDB1; to put them in the right place.

Before 1.16.2 this all wasn't needed.

I'm also using Flyway, before 1.16.2 and the latest Oracle XE image, all this setup could be managed with a Flyway beforeMigrate script and now I need to have scripts on different levels, because of the limitations added in TestContainers and the addition of the default TEST user in the image.

So, it is more a combination of things, why I need to do some extra labour to connect all the dots and make it work.

[kiview]

Unfortunately, I can't really comment on the implementation details and best practices of the Oracle XE image we use (since this is provided by the community, @gvenzl in this case). So in this regard, we tend to rely on technology experts from the community that maintain Docker images. Testcontainers always needs to implicitly integrate against the used image and its constraints. Also, note that compared to the previously used default image by an anonymous maintainer, we are now using an image that has been adopted by wider parts of the community and considerably improves our abilities to continuously support it.

However, we can always improve the UX, either by providing higher-level APIs or implementing it upstream in the image. In this case, I personally can't assess what is the best way to use the image for your use case and what are the Oracle idiomatic ways of using tablespaces and initialization scripts (or if it could be solved by other means or by using a pluggable database?).

[gvenzl]

Hi all, I think this issue here is related to the one that @tomdevroomen also opened over at gvenzl/oci-oracle-xe#41.

From a oracle-xe image users view, now the default user and schema is now "TEST".
Meaning tables I create need to be prefixed with "TEST" if I use the Initialization script.
Plus I need to add ALTER SESSION SET CONTAINER=XEPDB1; to put them in the right place.

Before 1.16.2 this all wasn't needed.

@tomdevroomen, I'm afraid I don't have much knowledge about how things were before 1.16.2, so I apologize for my naivety here in advance. Obviously creating additional tablespaces or other database components is something to be expected on a regular basis, hence I want to make sure that we make this easy again. How do the steps now compare to the ones before? Why do tables need to be prefixed with "TEST" if you use the initialization scripts and how did you create that new tablespace (and others) previously?

[kiview]

@tomdevroomen Can you please provide a code example of how you did it with the older version?

Note that you can also construct the JDBC URL yourself and e.g. use SYSTEM user. Would this solve your problem?

[tomdevroomen]

Hi, thank you for this conversation and let's see if we can improve things, either on my side or the testcontainers/oci-oracle-xe side :-)

Before my script to have a basic setup for my db was:

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

CREATE TABLE MY_TABLE
(
    ID NUMBER(19) NOT NULL PRIMARY KEY
);

CREATE ROLE MY_ROLE;

I was able to run this script through the TestContainers API:

OracleContainer db = new OracleContainer("gvenzl/oracle-xe:slim").withInitScript("tables_views.sql");

Now with the changes made in 1.16.2 and the introduction of the default test user on the image the script and code to have the same result are:

-- ADDED
ALTER SESSION SET CONTAINER=XEPDB1;

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

-- ADDED
alter user test quota unlimited on MY_TABLESPACE;

-- ADDED TEST.
CREATE TABLE TEST.MY_TABLE
(
    ID              NUMBER(19) NOT NULL PRIMARY KEY
);

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

CREATE ROLE MY_ROLE;

OracleContainer db = new OracleContainer("gvenzl/oracle-xe:slim")
                .withCopyFileToContainer(MountableFile.forClasspathResource("init.sql"), "/container-entrypoint-startdb.d/init.sql");

So, for me the TestContainer .withInitScript(...) isn't possible anymore as the TestContainer API now disabled the SYSTEM user, thus now I need to use the withCopyFileToContainer(...) and explicitly need to create objects in the TEST schema.

If this is the way it is intended, that's fine, and I found my way now.
But the usage is different from what it was, previously.

Thank you for your cooperation!

[kiview]

Thanks for sharing this. We might think about running withInitScript with elevated permissions (e.g. as SYSTEM) instead of the normal user. Would this be an Oracle DB idiomatic thing to do?

[tomdevroomen]

That would help in my situation.

I'm not an Oracle DB expert, maybe @gvenzl can answer this?

[gvenzl]

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.

[tomdevroomen]

@gvenzl thank you for your feedback!

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

[kiview]

After checking some of the images of the other database modules, I think we should discuss aligning their behavior.

E.g. for PostgreSQL and MySQL, we are implicitly returning a JDBC connection for a superuser and would also run the init scripts with those privileges. It might therefore more appropriate to implement it in a similar fashion for OracleContainer and say it is the responsibility of the Testcontainers user to create their specific application user with limited rights and use it accordingly for the actual tests.

However we proceed, I feel we need a general alignment across all JDBC containers. Do you have some thoughts on this @rnorth?

[rnorth]

I agree, up until

say it is the responsibility of the Testcontainers user to create their specific application user with limited rights and use it accordingly for the actual tests

😄

I think that this would be at odds with ease-of-use and would probably be a breaking change, so for this aspect in particular I'd be wary.

I think that running init scripts with superuser privileges would be appropriate. However, I'd still want to think about whether this could introduce a breaking change somehow (either just implicit expectations about which user runs the init script, or breaking peoples workarounds). Hopefully we'll conclude that it's safe to do, especially with precedent for it existing in other DB modules.

I would love to have a wider think about init scripts in general some time, and whether our approach of pushing data through JDBC (with all its statement-splitting nightmares) is something we should be avoiding via copying scripts to the container - for DBs that support it. This was suggested by a user previously and weighs on my mind as a potentially pragmatic step.

[gvenzl]

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.

[pchandolu]

After creating the container, how can we access the container and perform ddl operations on the db created in container?can you please share any reference to perform dynamic operations using java springboot