Secure and Efficient Oracle DB Setup with Spring Boot and Testcontainers

Having one user for creating database objects (DDL) and another for application-level data operations (DML) has many benefits. It improves security, keeps things organized, and makes maintenance easier. Here’s a detailed explanation.

Why Separating DDL and DML?

1. Better Security

• Limit Permissions for the Application User: The application user only has permissions for tasks like SELECT, INSERT, UPDATE, and DELETE. This reduces the risk of mistakes or malicious actions, such as deleting or changing the database structure.
• Protect the Schema: The application user cannot change or delete important database structures like tables, views, or stored procedures. This ensures the schema stays safe.

2. Clear Role Separation

• Using separate users follows the “least privilege” principle. Each user only gets the permissions they need.
• The database owner handles schema changes (like creating or altering tables), while the application user only works with the data.

3. Easier Maintenance and Schema Management

• Simpler Schema Updates: The application doesn’t need extra privileges for database updates or migrations. Tools like Liquibase or Flyway can handle these tasks using the database owner.
• Better Version Control: Since only the database owner makes schema changes, these updates are easier to track and audit.

4. Prevent Data Issues

• Limiting the application user’s permissions prevents problems like accidentally deleting tables or modifying constraints, which could cause data integrity issues.

5. Testing with Isolation

• In test environments like Testcontainers, the schema can be set up by the owner user. The application user then interacts with the database in a way that mimics real-world usage. This makes the tests more realistic.

6. Auditing and Accountability

• Separating users makes it easier to track who did what. For example, you can see whether a schema change or a data update caused an issue.

7. Flexible Testing and Migration

• Tools like Testcontainers benefit from separate users:
  • The DDL user sets up the schema during test initialization.
  • The DML user behaves like the application, focusing only on data operations.

8. Smaller Attack Surface

• If the application user’s credentials are leaked, the damage is limited to DML operations. Attackers cannot modify the schema or perform other high-level database actions.

Example Project

I’ve created a project that shows how to use separate database users for DDL and DML when working with Oracle DB, Spring Boot, and Testcontainers. You can find the source code in the GitHub repository: https://github.com/simasch/oracle-testcontainers-springboot

Example Setup in This Project

• DEMOOWNER:
  This user sets up the schema during tests using Testcontainers. It has full rights to create tables, add constraints, and perform other schema-related tasks.
• DEMOUSER:
  The application uses this user for all data operations. It only has DML permissions, keeping the schema and database structure safe.

To simplify queries, DEMOUSER has synonyms, so you don’t need to prefix table names with the schema name.

Testcontainers With Two Database Users

When using Spring Boot, most examples only use on users. In most recent Spring Boot versions, we can use @ServiceConnection on a bean method that creates the testcontainer. Below is an example that the Spring Boot Initializer generates.

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    @Bean
    @ServiceConnection
    OracleContainer oracleFreeContainer() {
        return new OracleContainer(DockerImageName.parse("gvenzl/oracle-free:23-slim-faststart"));
    }
}

But this will not work for our setup, so we must do extra configuration.

@TestConfiguration(proxyBeanMethods = false)
public class TestcontainersConfiguration {

    static final String DEMOOWNER = "demoowner";
    static final String DEMOUSER = "demouser";
    static final String PASSWORD = "password";

    static OracleContainer oracleContainer = new OracleContainer(
        DockerImageName.parse("gvenzl/oracle-free:23-slim-faststart"))
        .withCopyFileToContainer(MountableFile.forClasspathResource("init_users.sql"),
				"/container-entrypoint-initdb.d/init_users.sql")
        .withUsername(DEMOOWNER)
        .withPassword(PASSWORD);

    @Bean
    DynamicPropertyRegistrar registerDatabaseProperties() {
        oracleContainer.start();

        return registry -> {
	    registry.add("spring.datasource.url", oracleContainer::getJdbcUrl);
            registry.add("spring.datasource.username", () -> DEMOUSER);
	    registry.add("spring.datasource.password", () -> PASSWORD);
	    registry.add("spring.flyway.user", () -> DEMOOWNER);
	    registry.add("spring.flyway.password", () -> PASSWORD);
	};
    }
}

We need to create an OracleContainer and define a username and password. This will be the schema owner. Next, we want to add an application user. This is done in the init_users.sql script that we attach to the container. The Oracle container will execute startup scripts located in the directory /container-entrypoint-initdb.d. More information about the Oracle container, provided by Gerald Venzl can be found on Docker Hub: https://hub.docker.com/r/gvenzl/oracle-free

Now, the properties of the “regular” and flyway data sources must be set. This can be done using the DynamicPropertyRegistrar, added in Spring 6.2.

Finally, let’s look at the init_users.sql script:

alter session set container=freepdb1;

grant create any synonym to demoowner;

create user demouser identified by "password";
grant connect, resource to demouser;

In the first line, the session is initialized with the default container set in the Docker image. If we want to use synonyms, we must grant the right to the demoowner user because this is not set by default. Finally, we create the application user.

Database Schema and Migrations

You’ll find some entities (Firm, Customer, Address) and corresponding JPA repositories in the domain package. The mapping is validated with Hibernate. Because we use synonyms we must activate them for the validation as well:

spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.synonyms=true

There are two SQL migrations scripts for Flyway: src/main/resources/db/migration/V0001__Create_Customer.sql creates the database objecst and src/test/resources/db/migration/V9001__Create_Test_Data_Customer.sql inserts some test data.

Run the Test

To test the setup, I created a DataJpaTest for the CustomerRepository:

@Import(TestcontainersConfiguration.class)
@DataJpaTest(showSql = false)
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class CustomerRepositoryTest {

    @Autowired
    private CustomerRepository customerRepository;

    @Test
    void findAll() {
        List<Customer> customers = customerRepository.findAll();
        assertThat(customers).hasSize(3);
    }
}

First, the Testcontainers configuration is imported. Then I turned off the default SQL logging because I prefer to add logging.level.org.hibernate.SQL=debug to the application.properties to have it logged with the logger. And because we want to use the Oracle database for testing, we must disable the automatically configured in-memory database.

We are ready to run the test: ./mvnw test

Starting CustomerRepositoryTest using Java 21.0.5 with PID 72048 (started by simon in /Users/simon/Workspace/demo/oracle-testcontainers-springboot)

t.gvenzl/oracle-free:23-slim-faststart   : Creating container for image: gvenzl/oracle-free:23-slim-faststart
t.gvenzl/oracle-free:23-slim-faststart   : Container gvenzl/oracle-free:23-slim-faststart is starting: 47d84eb33004bd485fd393c2c299a7844f7117b9306c65fcaf94482e52f9795f
t.gvenzl/oracle-free:23-slim-faststart   : Container gvenzl/oracle-free:23-slim-faststart started in PT4.856352S
t.gvenzl/oracle-free:23-slim-faststart   : Container is started (JDBC URL: jdbc:oracle:thin:@localhost:57006/freepdb1)

o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.037s)
o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table "DEMOOWNER"."flyway_schema_history" ...
o.f.core.internal.command.DbMigrate      : Current version of schema "DEMOOWNER": << Empty Schema >>
o.f.core.internal.command.DbMigrate      : Migrating schema "DEMOOWNER" to version "0001 - Create Customer"
o.f.core.internal.command.DbMigrate      : Migrating schema "DEMOOWNER" to version "9001 - Create Test Data Customer"
o.f.core.internal.command.DbMigrate      : Successfully applied 2 migrations to schema "DEMOOWNER", now at version v9001 (execution time 00:00.072s)

c.m.d.o.c.domain.CustomerRepositoryTest  : Started CustomerRepositoryTest in 9.218 seconds (process running for 9.536)
org.hibernate.SQL                        : select c1_0.id,c1_0.created_date,c1_0.firm_id,c1_0.first_name,c1_0.last_name,c1_0.picture,c1_0.version from customer c1_0

[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 9.697 s

Conclusion

Using separate database users for DDL and DML operations enhances security, simplifies maintenance, and ensures clear separation of responsibilities. By leveraging this approach with Oracle DB, Spring Boot, and Testcontainers, you can build a robust and secure application environment that minimizes risks and promotes best practices for database management.

Adding this extra safety requires some extra steps. Luckely Spring Boot and the Oracle database Docker image provide functionality for the setup.

Special thanks go to Gerald Venzl for his help in setting up the database!