• Plesk Uservoice will be deprecated by October. Moving forward, all product feature requests and improvement suggestions will be managed through our new platform Plesk Productboard.
    To continue sharing your ideas and feedback, please visit features.plesk.com

CLI database server settings - access control - solved

Jens Johansson

Basic Pleskian
Hi there,

i would like to change the access control for datebase users via CL:
command: /usr/local/psa/bin/database --update-dbuser web1_presta -server localhost:3306
reply: The database user was successfully updated.
examination: mysql> select host, user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | web1_presta |
| localhost | web1_oxid |
| localhost | web1_presta |
| localhost | web1_shopware |
| localhost | web1_wp |
+-----------+------------------+

Only when using the GUI:
home -> Subscriptions -> Website & Domains -> Databases -> Users -> web1_wp -> Access control
-> from Allow remote connections from any host
-> to Allow local connections only

is also the user adjusted:
mysql> select host, user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | web1_oxid |
| localhost | web1_presta |
| localhost | web1_shopware |
| localhost | web1_wp |
+-----------+------------------+

Does anyone have an idea :)? Many Thanks!

Erik
 
damn, wrong command, correct is: /usr/local/psa/bin/database -u web1_wp -update_user web1_wp -set-acl localhost

Can anyone tell me how to change all users and all databases with one command?
 
much easier with "replace" ...

check:
mysql> select host, user, db from mysql.db;

+-----------+------------------+
| host | user |
+-----------+------------------+
| % | web1_wp |
| localhost | web1_oxid |
| localhost | web1_presta |
| localhost | web1_shopware |
+-----------+------------------+

+-----------+------------------+-------------------------+
| host | user | db |
+-----------+------------------+-------------------------+
| % | web1_wp | web1\_wp |
| localhost | web1_oxid | web1\_oxid |
| localhost | web1_presta | web1\_presta |
| localhost | web1_shopware | web1\_shopware |
+-----------+------------------+-------------------------+

set:
update db set host = replace(host,'%','localhost');
update user set host = replace(host,'%','localhost');

mysql> select host, user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | web1_oxid |
| localhost | web1_presta |
| localhost | web1_shopware |
| localhost | web1_wp |
+-----------+------------------+


mysql> select host, user, db from mysql.db;
+-----------+------------------+-------------------------+
| host | user | db |
+-----------+------------------+-------------------------+
| localhost | web1_oxid | web1\_oxid |
| localhost | web1_presta | web1\_presta |
| localhost | web1_shopware | web1\_shopware |
| localhost | web1_wp | web1\_wp |
+-----------+------------------+-------------------------+
 
Back
Top