I would like to create a user on database with the minimum required privileges for Magento. Anyone done this?
Add a comment
|
3 Answers
Give full permissions to the database for the specified user. You will not get away with limiting permissions. It needs to read and write to every single table. Baring in mind it requires full access to admin users table if you're server gets hacked.. you've got a wider problem... they'd still be able to read the DB... I wouldn't worry about them writing to it.
According to the magento docs you can grant all privileges to the database user:
GRANT ALL ON magento.* TO magento@localhost IDENTIFIED BY 'magento';
source: http://devdocs.magento.com/guides/m1x/install/installing_install.html
-
This would give full privileges to the user, for security reasons I would like to limit really only the necessary. For example if I am on production environment which I have no modules updates, Magento will not need create table privilege.– EduardoCommented Aug 11, 2015 at 10:44
-
This is true although as you said on the first initial install it will need to create table's when you first run the site. Also when 3rd party modules try to run updated to install new tables. But then sure you could limit it down although the dev docs does say to grant all privileges– rob3000Commented Aug 11, 2015 at 12:20
-
"you can" doesn't mean "you should". This is a serious concern and should be taken as such on production servers.– AakashCommented Jul 31, 2017 at 7:08
In a typical Magento installation, we'd of course grant all privileges to the MySQL database. On my current project however I've recently been forced to answer this same question as the corporate environment I work in does not want such permissions to be allowed in production.
So far, locally, I've been able to get away with granting Magento's MySQL user limited permissions by narrowing down what table(s) Magento modifies on it's out-of-the-box indexers and cron jobs and on a per table basis granting additional privileges to those table(s). This has yet to be successfully applied to a production environment so please do your own due diligence and test yourself in a non-production environment.
Due to the way Magento handles upgrades and new module installations, limiting our database user's MySQL privileges also comes with the additional complications of how to handle these scenarios. Our plan, come time for patches, version upgrades, and/or new modules, is to supply to our Ops team a MySQL file to apply any and all database related changes that occur from a Security Patch, Version upgrade, and/or module installation, prior to shipping out the new code to the production environment. This of course also implies updating the data_version and schema_version value in the setup_resource table for those modules as well (or INSERT in the event of a new module). In the event that an update is non-backwards compatible we will have to most likely communicate again with our Ops team to decide how to handle the situation at that time.
Note: The following is for a Magento 1.14 EE installation, with 3 stores. If you're on CE, then the enterprise_% tables will not apply to you. Also you will need to revise the number of tables to match the number of stores your site has.
Last Note: MySQL doesn't allow certain GRANT actions performed if the table does not already exist. A simple work around for this is to create a dummy table, apply your permissions, then drop the table. The permissions for the MySQL user will be retained afterwards.
+-------------------------------------------------------------------------------------------------------+
| Grants for magento@localhost |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'magento'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON `magento`.* TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_1` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_1_old` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_1_tmp` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_2` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_2_old` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_2_tmp` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_3` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_3_old` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_category_flat_store_3_tmp` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_product_index_group_price` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_product_index_price_idx` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_product_index_tier_price` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`catalog_product_index_website` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`cataloginventory_stock_status_idx` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`enterprise_targetrule_index` TO 'magento'@'localhost' |
| GRANT CREATE, DROP, ALTER ON `magento`.`enterprise_targetrule_index_reindex` TO 'magento'@'localhost' |
+-------------------------------------------------------------------------------------------------------+
The above has allowed me to successfully run a php shell/indexer.php reindexall and php shell/cron.php without error (In regards to the cron, I actually just sat and watched the cron jobs for a while to verify they were running properly). I'll try and remember to update this SE answer if I come across any additional permissions that I found were necessary.
