Doctrine Bridge creating random tables prevents fine grained database permissions

bmorg commented 8 months ago

Symfony version(s) affected

7.1.0

Description

When running a database migration, a table schema_subscriber_check_ <RANDOM-STRING> is created and immediately dropped again.

This prevents us from setting restrictive database permissions where the Symfony application is only allowed to access a select subset of tables, but does not have the permission to create (and drop) arbitrary tables.

The corresponding code is located in AbstractSchemaListener->getIsSameDatabaseChecker:

$checkTable = 'schema_subscriber_check_'.bin2hex(random_bytes(7));
$connection->executeStatement(sprintf('CREATE TABLE %s (id INTEGER NOT NULL)', $checkTable));

try {
    $exec(sprintf('DROP TABLE %s', $checkTable));
} catch (\Exception) {
    // ignore
}

I was granting the following permissions for a single table in MySQL 5.7 to the Symfony application:

my_db.my_table:CREATE,DROP,SELECT,INSERT,UPDATE,DELETE

MySQL does not support granting permissions on table names with wild cards (schema_subscriber_check_*).

How to reproduce

I don't seem to be able to trigger the call of getIsSameDatabaseChecker in a dummy application.

Possible Solution

I am not 100% sure what the purpose of the "same database checker" is, so I can't propose an appropriate solution.

Additional Context

No response

carsonbot commented 1 month ago

Hey, thanks for your report! There has not been a lot of activity here for a while. Is this bug still relevant? Have you managed to find a workaround?

carsonbot commented 1 month ago

Could I get a reply or should I close this?

bmorg commented 1 month ago

Is this bug still relevant?

Yes, the mentioned code is still present in the 7.2 branch.

symfony / symfony