databaseservermariadbdebianmariasql

GRANT on db with a separator in name not working


I have a problem on my Debian server with MariaDB on it. I'm trying to grant all privileges to a user ('agricoop') on my database called extranet-agricoop.

I'm writing : GRANT ALL PRIVILEGES ON extranet-agricoop.* TO 'agricoop'@'localhost'; I get the error message : "You have an error in your SQL syntax [...] near 'extranet-agricoop.* TO 'agricoop'@'localhost'' at line 1"

It worked for my other users on other table but just not for that one. If I select ‘*.*´ it works so for me the problem seems to come from the name of the db. I've tried to escape the separator but still not working.

Have you got any idea ? Thanks :)


Solution

  • Identifiers have to be quoted if it contains one or more character which is not part of [a-z,A-Z,0-9,$,_] (or isn't a unicode character > 0x0080).

    If sql_mode ANSI_QUOTES is set, you have to use double quotes ("), if sql_mode is MSSQL square brackets([..]) have to be used.

    Example:

    GRANT ALL ON `better-use-dash-than-minus`.* TO foo@localhost