It's one of those things I need to do fairly often when a user (or me) needs a new database for whatever they are playing with at the moment. That piece of software also needs its own username to access the database, just to make sure it doesn't have access to any of the other databases that it shouldn't need. I don't use mysql enough that I remember the commands the same way I do when working with the everyday unix command line tools, so I always need to look up how to create that user and grant the privileges to a database. It's pretty simple though, first of all start up mysql with your admin user, don't forget the -p option because you hopefully have a password set

# mysql -p

Then in the mysql tool you can create a user, in this example the user is called newappuser and the password is SecretAppPass. If you are using a different version of MySQL or MariaDB your prompt will look a bit different.

MariaDB [(none)]> CREATE USER 'newappuser'@'localhost' IDENTIFIED BY 'SecretAppPass';

Note that this user can only login from localhost, so the app will need to make sure it connects to the SQL server using the hostname localhost. I have even had problems before where the user was denied access because the app was connecting to instead of localhost even though they should be the same thing. If you want to allow the user to work from any host then you can specify '%' in the hostname part. Now the user is created, you can create the database for the app and give the new user full access to it

MariaDB [(none)]> CREATE DATABASE newappdb;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON newappdb.* TO 'newappuser'@'localhost';

The ALL PRIVILEGES bit gives that user full access to every table in the database, but you could only give them access to certain commands if you needed like INSERT or SELECT so then they wouldn't be able to delete or modify data in the tables. For the full list of database and table privileges look at the MariaDB site.

If you want to remove the database and the user you can use the DROP command

MariaDB [(none)]> DROP DATABASE newappdb;
MariaDB [(none)]> DROP USER 'newappuser'@'localhost';

Previous Post Next Post