onitake opened a new issue #3221: Proposal: Specify INVOKER security on table views to avoid DB user dependency URL: https://github.com/apache/cloudstack/issues/3221 <!-- Verify first that your issue/request is not already reported on GitHub. Also test if the latest release and master branch are affected too. Always add information AFTER of these HTML comments, but no need to delete the comments. --> ##### ISSUE TYPE <!-- Pick one below and delete the rest --> * Improvement Request ##### COMPONENT NAME <!-- Categorize the issue, e.g. API, VR, VPN, UI, etc. --> ~~~ DB ~~~ ##### CLOUDSTACK VERSION <!-- New line separated list of affected versions, commit ID for issues on master branch. --> ~~~ 4.11.2 ~~~ ##### CONFIGURATION <!-- Information about the configuration if relevant, e.g. basic network, advanced networking, etc. N/A otherwise --> N/A ##### OS / ENVIRONMENT <!-- Information about the environment if relevant, N/A otherwise --> MySQL 5.5 (later versions have the same issue) ##### SUMMARY <!-- Explain the problem/feature briefly --> According to https://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html , views on MySQL are created with SQL SECURITY DEFINER and the creator's user account as DEFINER by default. This causes problems when changing database access control or migrating the database, as the user (which includes a host name suffix) may no longer exist. Since there is no specific definition of security in the CloudStack database schema, I propose that views are created with SQL SECURITY INVOKER instead, as that would adopt the grants of the user executing the query on view. ##### STEPS TO REPRODUCE <!-- For bugs, show exactly how to reproduce the problem, using a minimal test-case. Use Screenshots if accurate. For new features, show how the feature would be used. --> On a database server with a user `cloud@localhost` but no user `cloud@%`, import a database dump from a server that had such a user: <!-- Paste example playbooks or commands between quotes below --> ~~~ zcat cloud.sql.gz | mysql -u cloud cloud ~~~ <!-- You can also paste gist.github.com links for larger files --> ##### EXPECTED RESULTS <!-- What did you expect to happen when running the steps above? --> No error is reported. ##### ACTUAL RESULTS <!-- What actually happened? --> The import fails because setting the DEFINER to a different user than the one creating it requires superuser privileges. <!-- Paste verbatim command output between quotes below --> ~~~ ERROR 1227 (42000) at line xxxx: Access denied; you need (at least one of) the SUPER privilege(s) for this operation xxxx: /*!50013 DEFINER=`cloud`@`%` SQL SECURITY DEFINER */ ~~~ ##### WORKAROUND There are two possible workarounds. Export the database with `mysqlpump` and specify `--skip-definer` (requires MySQL 5.7): ~~~ mysqlpump --skip-definer -h old-server -u cloud -p cloud | gzip -c > cloud.sql.gz ~~~ Or remove the lines from the dump during import: ~~~ zcat cloud.sql.gz | grep -v "50013 DEFINER" | mysql -u cloud cloud ~~~
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
