Want to subscribe to topics you're interested in?
Become a Member

MariaDB Database Permission for User

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Jimmy, May 5, 2017.

  1. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    6:27 PM
    1.13.x
    MariaDB 10.1.x
    CentOS 7 64bit 123.09beta01 latest MariaDB (10.1.23)

    I'm failing hard at trying to do the simplest thing... Grant Privileges to a user.

    Creating a user works fine:
    Code:
    # mysql
    > CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
    > exit
    Code:
    MariaDB [(none)]> SELECT User FROM mysql.user;
    +----------+
    | User     |
    +----------+
    | root     |
    | root     |
    | user    |
    | root     |
    | root     |
    +----------+
    
    Creating a database works fine:
    Code:
    mysqladmin -u root -p create <database>
    Code:
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | mydatabase |
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    4 rows in set (0.00 sec)
    
    Getting the permissions setup for the user I'm getting nothing but errors.

    Error using ( also tried without the use <database>; ):
    Code:
    # mysql
    > use <database>;
    > GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'localhost';
    OR
    > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES ON <database>.* TO 'mysqlusername'@'localhost';
    > FLUSH PRIVILEGES;
    > exit
    Also tried:
    Code:
    mysql -u root -p -e "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES ON <database>.* TO '<username>'@'localhost'; FLUSH PRIVILEGES;"
    Keep getting this error
    Code:
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<database>.* TO '<username>'@'localhost'' at line 1
     
    Last edited: May 5, 2017
  2. eva2000

    eva2000 Administrator Staff Member

    28,984
    6,579
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,766
    Local Time:
    8:27 AM
    Nginx 1.13.x
    MariaDB 5.5
    For creating MySQL databases and MySQL username and password you have a few options all outlined via the following linked information (including mysqladmin_shell.sh addon below).
    Centmin Mod Addons - phpmyadmin.sh & mysqladmin_shell.sh
    Inspect addons/mysqladmi_shell.sh code for clues too.

    Also if you have underscores in mysql database name, they need to be escaped with a backward slash i.e. username_mysqldb database name will have to written as `username\_mysqldb`.*
     
  3. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    6:27 PM
    1.13.x
    MariaDB 10.1.x
    Not using any userscores. My names are basic:
    database: 3e7w11ews8Ue49Sddu
    username: e8rw4jk3

    Code:
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '3e7w11ews8Ue49Sddu.* TO 'e8rw4jk3'@'localhost'' at line 1
    I had no problem creating the username or database.

    I even typed out the GRANT line incase some illegal character was being copied over from the text doc I'm using.
     
  4. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    6:27 PM
    1.13.x
    MariaDB 10.1.x
    I have a doc which I use that has all the information about creating databases. I've used it multiple times in the past with success. I haven't created a dbase in awhile, but now the same instructions which worked before don't work. Weird.
     
  5. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    6:27 PM
    1.13.x
    MariaDB 10.1.x
    Ok, there seems to be something with the length or characters used - even though it was just numbers and letters (all lower case). I tried one with a simpler username / database name and it worked. Interesting that it would create the username / database name, but then throw up and error on adding permissions.
     
  6. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    6:27 PM
    1.13.x
    MariaDB 10.1.x
    After several tests, the issue seems to be the use of a number at the start of the database name. Removing the number from the beginning of the name and there is no issue.
     
  7. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    6:27 PM
    1.13.x
    MariaDB 10.1.x
    My final reference file on creating / deleting databases - if anyone wants the info:

     
    Last edited: May 5, 2017
    • Informative Informative x 2
  8. eva2000

    eva2000 Administrator Staff Member

    28,984
    6,579
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,766
    Local Time:
    8:27 AM
    Nginx 1.13.x
    MariaDB 5.5
    yeah numbers first = no no :)
     
    • Agree Agree x 1
  9. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    6:27 PM
    1.13.x
    MariaDB 10.1.x
    Never knew that, now I do! Won't do that again. ;)
     
    • Like Like x 1