Requires user to have full database permissions to be able to use "--write-sql" - Security issue

[rimvislt]

Version - "doctrine/migrations": "^3.2",

migrations:migrate --write-sql ~/migration.sql
In ConnectionError.php line 19:
REFERENCES command denied to user 'xxxuser'@'x.x.x.x' for table 'xx_table';

This user does not have full database permissions for most of it - can not create tables, can not alter tables etc..

Previously with doctrine/migrations v 1.x was not executing migrations inside database and no need for user to have all permissions.

We need this functionality because during release we are passing migration.sql file to hosting to review it and execute it on production server.

We do not want to have this web server user all permissions on database to be able to generate migration script.

As a work around we found that running with dry-run we can almost achieve it.
migrations:migrate --dry-run --write-sql ~/migration.sql

but we need to go into migration.sql file and add insert versions into migrations table.

INSERT INTO doctrine_migration_versions (version) VALUES ('Migrations\Version2021xxxxxxxx');

[goetas]

is there a migration in your list that uses the REFERENCES keyword?

[rimvislt]

yes it is and it is denied for the user which is running migrations:migrate --write-sql ~/migration.sql

   $this->addSql("SET SQL_SAFE_UPDATES=1");

    $this->addSql("ALTER TABLE xx_table " .
        "ADD COLUMN aaa_id INT(11) DEFAULT NULL, " .
        "ADD CONSTRAINT `fk_xxxxx1`".
        "FOREIGN KEY (`aaa_id`) REFERENCES `aaa_table` (`aaa_id`)" .
        "ON DELETE NO ACTION ON UPDATE NO ACTION"
    );

[wiistriker]

i think this can be closed
#1265