Support creating a database user when the container starts

[filiphr]

For testing purposes it is really handy to start the container with a database already populated with a user that can be used in the tests. In order not to use the SYSTEM user.

Ideally I would like to run the container with:

docker run -d -p 1521:1521 -e ORACLE_PASSWORD=<your password> -e ORACLE_USER=<your database user> gvenzl/oracle-xe

I know that I can mount a volume with an initial script, but it is way easier to only define the user with an environment variable in the CI (e.g. in GitHub actions).

[gvenzl]

Hey @filiphr,

Thanks a lot for this request!

I think I understand where you are coming from. You basically want to have an application user ready to connect to and do things with, right? Would it be, for example, good enough to have two new variables APP_USER and APP_USER_PASSWORD[_FILE] that when specified will create that user on the initial database startup, and only the initial database startup so that no one can simply create a new user and connect in an already existing database and potentially compromise it?

Do you plan to do anything special with the user that requires special privileges, or would you be fine with a standard Oracle Database user, meaning having privileges for the most common database operations, such as creating tables, indexes, triggers, views, etc.?

[filiphr]

I think I understand where you are coming from. You basically want to have an application user ready to connect to and do things with, right?

Yes this is exactly what I want. I want to have a user that I can use to connect and do things in the DB.

Would it be, for example, good enough to have two new variables APP_USER and APP_USER_PASSWORD[_FILE] that when specified will create that user on the initial database startup, and only the initial database startup so that no one can simply create a new user and connect in an already existing database and potentially compromise it?

Yes having those two new variables is more then enough, and of course only doing it on the initial startup is more than enough for me. My biggest use case is running this on our CI. e.g. this is what we are doing currently and then use it like this.

Do you plan to do anything special with the user that requires special privileges, or would you be fine with a standard Oracle Database user, meaning having privileges for the most common database operations, such as creating tables, indexes, triggers, views, etc.?

I am fine need a standard Oracle Database.

[gvenzl]

Got it, I'll get on it then.

Btw, can I also persuade you to change :XE to /XE here: https://github.com/flowable/flowable-engine/blob/a5d6f1c090b6fef43f82675384a5e8ed928c9355/.github/workflows/oracle.yml#L57

The : tells the driver to connect to the Oracle instance itself. If you want to, e.g. connect to a pluggable database like XEPDB1 in the 18c image, this will not work with :XEPDB1 because there is every only one Oracle instance.

[filiphr]

Btw, can I also persuade you to change :XE to /XE here: https://github.com/flowable/flowable-engine/blob/a5d6f1c090b6fef43f82675384a5e8ed928c9355/.github/workflows/oracle.yml#L57

Of course, just did it in flowable/flowable-engine@aa8c585. I was not aware about the difference between the two. I've mostly used the :XE approach until now. Thanks for the tip.

[gvenzl]

Thanks a lot! Yeah, it's an easy to overlook thing – unfortunately. One tiny character makes all the difference whether you can use services, pluggable databases and RAC or not. :(

Just googled, nothing good out there, suppose I should write a blog post on that one...
Meanwhile, please spread the word! :)

[gvenzl]

Getting somewhere :)

[gvenzl]

And the 18c equivalent (note, the app user is created in the pluggable database XEPDB1):

[gvenzl]

Implemented.

[filiphr]

Just tested this and it works great with the APP_USER and APP_USER_PASSWORD. Thank you very much for the speedy implementation.