mysql permissions for new server/ispcsrv account

Discussion in 'General' started by nhybgtvfr, Jun 14, 2017.

  1. nhybgtvfr

    nhybgtvfr Member

    hi.
    i've added another server to our multi-server setup, and it's been having some issues with the user permissions to the master db.

    i've checked the permissions for another ispcsrv* user and tried to recreate those manually.

    these are the permissions for a correct already existing user:
    +------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for [email protected] |
    +------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'ispcsrv13'@'10.10.12.105' IDENTIFIED BY PASSWORD '***hidden****' |
    | GRANT SELECT, INSERT, UPDATE ON `ispconfig_CP`.`ftp_traffic` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, UPDATE (response, action_state) ON `ispconfig_CP`.`sys_remoteaction` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, INSERT, DELETE ON `ispconfig_CP`.`monitor_data` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, INSERT, DELETE ON `ispconfig_CP`.`web_backup` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, INSERT, UPDATE ON `ispconfig_CP`.`web_traffic` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, UPDATE, DELETE ON `ispconfig_CP`.`aps_instances` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, DELETE ON `ispconfig_CP`.`aps_instances_settings` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, UPDATE (status) ON `ispconfig_CP`.`software_update_inst` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, UPDATE (updated) ON `ispconfig_CP`.`server` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, UPDATE (dnssec_last_signed, dnssec_info, dnssec_initialized) ON `ispconfig_CP`.`dns_soa` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, UPDATE (ssl_key, ssl, ssl_request, ssl_action, ssl_letsencrypt, ssl_cert) ON `ispconfig_CP`.`web_domain` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, INSERT, DELETE ON `ispconfig_CP`.`mail_backup` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, UPDATE (status, error) ON `ispconfig_CP`.`sys_datalog` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, INSERT, UPDATE ON `ispconfig_CP`.`mail_traffic` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT ON `ispconfig_CP`.`sys_group` TO 'ispcsrv13'@'10.10.12.105' |
    | GRANT SELECT, INSERT ON `ispconfig_CP`.`sys_log` TO 'ispcsrv13'@'10.10.12.105' |
    +------------------------------------------------------------------------------------------------------------------------------------------------------+

    i've applied them all successfuly for the user 'ispcsrv14'@'46.101.22.156' except for this one:
    GRANT SELECT, UPDATE (ssl_key, ssl, ssl_request, ssl_action, ssl_letsencrypt, ssl_cert) ON `ispconfig_CP`.`web_domain` TO 'ispcsrv14'@'46.101.22.156';

    all i get is ERROR 1064 (42000): You have an error in your SQL syntax;

    i've tried applying it to each field separately, and they all work fine except for 'ssl', it just won't let me grant select or update to that field.
    even if it try to re-apply it to 'ispcrv13'@'10.10.12.105' i get exactly the same error for that, even though it already has those permissions, and the exact same line works for each individual field except for ssl, so there isn't any syntax error.

    any ideas on what could be causing this? and how to fix it?

    thanks
    lee.
     
  2. nhybgtvfr

    nhybgtvfr Member

    ok. it seems it works ok if the field ssl is enclosed in `quotes.
    ie:
    GRANT SELECT, UPDATE (ssl_key, `ssl`, ssl_request, ssl_action, ssl_letsencrypt, ssl_cert) ON `ispconfig_CP`.`web_domain` TO 'ispcsrv14'@'46.101.22.156';

    So I guess ssl has become a reserved word in mysql since the last time i added a server to our system.
     
    till likes this.

Share This Page