Oracle DB test user password must be quoted if it starts with a number

[Mariusz Felisiak]

Oracle DB test user password cannot start with digits because quotation marks are missing in SQL. For example:

CREATE USER foo
IDENTIFIED BY 2fXHVnA9KRH4uTSSvV3fVDel4kyVum
DEFAULT TABLESPACE foo_tbls_test
TEMPORARY TABLESPACE foo_tbls_temp_test
QUOTA UNLIMITED ON foo_tbls_test;

is incorrect (ORA-00922: missing or invalid option) it should be:

CREATE USER foo
IDENTIFIED BY "2fXHVnA9KRH4uTSSvV3fVDel4kyVum"
DEFAULT TABLESPACE foo_tbls_test
TEMPORARY TABLESPACE foo_tbls_temp_test
QUOTA UNLIMITED ON foo_tbls_test;

All versions are vulnerable ie 1.8.16, 1.9.11, 1.10.3 and master.

[Tim Graham]

​PR. This might explain some of the failures that have popped up on Jenkins. Release notes for 1.10.4, 1.9.12, and 1.8.17 are also needed.

[Mariusz Felisiak]

I added release notes for 1.8.17/1.9.12/1.10.4.

[Shai Berger]

Replying to Tim Graham:

This might explain some of the failures that have popped up on Jenkins.

I believe they are actually unrelated, the error was about password expiry.

[Marti Raudsepp]

I was really puzzled about this bug because it should have a 10/62 chance of occurring, but I had ran dozens of tests using patched Django and never seen such a failure.

I investigated this further and the consequences are uglier than I expected. :(

If the test user creation fails -- with whatever exception -- and --keepdb is specified, then _create_test_db simply returns out of the function without switching to the test user. This means that tests are executed using the main connection parameters instead of the test user.

    def _create_test_db(self, verbosity=1, autoclobber=False, keepdb=False):
        ...
            try:
                self._create_test_user(cursor, parameters, verbosity, keepdb)
            except Exception as e:
                # If we want to keep the db, then we want to also keep the user.
                if keepdb:
                    return
                ...
        self._maindb_connection.close()  # done with main user -- test user and tablespaces created
        self._switch_to_test_user(parameters)
        return self.connection.settings_dict['NAME']

[Mariusz Felisiak]

I had bad luck and my tests failed just few hours after Django upgrade. The simplest (and quickest) solution for now is to set PASSWORD parameter for TEST database (without number or special character at the beginning).

[Mariusz Felisiak]

I agree that this little hack is currently unnecessary and can cause unexpected behavior. It should be removed.

--- a/django/db/backends/oracle/creation.py
+++ b/django/db/backends/oracle/creation.py
@@ -77,9 +77,6 @@ class DatabaseCreation(BaseDatabaseCreation):
             try:
                 self._create_test_user(cursor, parameters, verbosity, keepdb)
             except Exception as e:
-                # If we want to keep the db, then we want to also keep the user.
-                if keepdb:
-                    return
                 sys.stderr.write("Got an error creating the test user: %s\n" % e)
                 if not autoclobber:
                     confirm = input(

[Tim Graham <timograham@…>]

In c4b04e15:

Fixed #27420 -- Quoted the Oracle test user password in queries.

[Tim Graham <timograham@…>]

In dacef913:

Refs #27420 -- Removed exception hiding in Oracle test user creation during --keepdb.

If the test user creation fails here, _create_test_db() would return without
switching to the test user which caused the tests to run using the main
connection instead of the test user.

[Tim Graham <timograham@…>]

In a3fa2c4:

[1.9.x] Fixed #27420 -- Quoted the Oracle test user password in queries.

Backport of c4b04e1598c4325454c808183dce17b284ed9e28 from master

[Tim Graham <timograham@…>]

In bc74bc1f:

[1.10.x] Fixed #27420 -- Quoted the Oracle test user password in queries.

Backport of c4b04e1598c4325454c808183dce17b284ed9e28 from master

[Tim Graham <timograham@…>]

In 32f50999:

[1.8.x] Fixed #27420 -- Quoted the Oracle test user password in queries.

Backport of c4b04e1598c4325454c808183dce17b284ed9e28 from master