CLI database server settings - access control - solved

[Jens Johansson]

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

 

[Jens Johansson]

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?

 

[Jens Johansson]

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 |
+-----------+------------------+-------------------------+