Create new MySQL User granting permissions to new database

By whgandalf | June 19, 2018

There are multiple systems where you need to set up a new database, Installing MySQL is always an option to achieve that, but after installation, you will have to follow some more steps to be able to provide the connection credentials to the system you are trying to connect, let’s try it following the next steps:

How to create a database?

First check if you have MySQL installed, you can do it running the command:

    ~: mysql -V
    mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

If you get a not found message, you have to install it first. Installation commands depend on what operating system you are using.

Now, connect to MySQL using your root or another admin user, type the password when it requests:

    ~: mysql -u root -p
    Enter password:

If the connection is successful, you will get the next messages:

    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 18420
    Server version: 5.5.56-MariaDB MariaDB Server

    Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    MariaDB [(none)]>

You are ready to proceed by creating a new database, in this example, we will create testdatabase typing:

    MariaDB [(none)]> CREATE DATABASE testdatabase;

How to create a new MySQL user and grant permissions?

Now you can proceed to create a new user for your desired service, this time we will use databaseman as the username and manPasswd as the password

    MariaDB [(none)]> CREATE USER 'databaseman'@'localhost' IDENTIFIED BY 'manPasswd';

At this moment you have a user created, but it cannot change anything because it doesn’t have any permissions or privileges assigned.

Let’s grant all the testdatabase privileges to the new user databaseman:

    MariaDB [(none)]> GRANT ALL PRIVILEGES ON testdatabase . * TO 'databaseman'@'localhost';

the asterisk after testdatabase. means the privileges will be applied to all tables within the selected database.

You are almost ready, everything is prepared to work, but you will need to restart the privileges list for MySQL and exit after, you can do that running:

    MariaDB [(none)]> FLUSH PRIVILEGES;
    MariaDB [(none)]> exit;

How can I test the new database and user?

At this point, you are ready to go, but maybe you want to make sure everything works before using it with another application. Doing it is easy, let’s just connect to MySQL using the new credentials like this:(username: databaseman, password: manPasswd)

    ~: mysql -u databaseman -p
    Enter password:

If your credentials worked, you must be in MySQL (MariaDB) command line interface again, you can now list the databases the current logged in user has access to by typing the command SHOW DATABASES;, hit <enter> key and you should see your new testdatabase in the list:

    MariaDB [(none)]> SHOW DATABASES;
    | Database           |
    | information_schema |
    | testdatabase       |
    2 rows in set (0.00 sec)

Following these steps will give you a new MySQL database with a new user with permissions granted to manage it, now you have all you need to use it in some systems which require MySQL databases like WordPress.

comments powered by Disqus