|« Writing can change the world||The internet is one big soapbox »|
Creating a MySQL user under PleskJanuary 24th, 2012
This article shows us how to create a database, add a user and set up their permissions. I’ve always found MySQL’s permission and grant structure unnecessarily complicated so in this example we are just going to give the users all the default permissions.
First up create the database. Under Plesk you can call the database anything you like, but I’ve adopted the naming convention <domain>_<usage>. This gives me several advantages:
- It groups the databases together in the file system so it is easier to find what you are looking for.
- Avoids name clashes.
- If you create a database outside Plesk it is not managed, which means it won’t get deleted when you delete the domain. All MySQL database files are owned by MySQL so it’s difficult to see which domain a particular database instance belongs to. Not any more!
- Your system looks like there is order!
First up is to create a database for example.com and we are going to use it for the world’s best blogging software B2evolution.
mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` create example_com_b2evolution
mysql -uadmin -p`cat /etc/psa/.psa.shadow`
Strictly speaking you can create database instances from inside MySQL using the following command.
CREATE DATABASE example_com_b2evolution;
I prefer to do it externally though, partly because after the database has been created you can enter the MySQL command prompt straight into that clean database but mostly because soon after creating the database I do an import which is another external command.
The only people accessing the database will be my web server on the
localhost. Users can have access to database, tables, DDL commands, functions, store procedures and triggers. In reality though, in almost all cases you just want one user who can access everything in one database instance and that’s it. I’ve been using MySQL for 15 years and never had to give a user a single table, select only permission! Enough babbling, MySQL says you have to create a floating user first then assign permissions to them. If I was going to go completely anal I would insist on having a separate user for each application and add table level permissions for that user to access just those tables, but generally speaking it’s not worth the hassle. Domain specific administrators want to be able to log in and check their databases, they don’t want to keep switching users to administer different applications in the same database. You’ll find in fact that you’ll need a database user for some operations like repairing or optimising tables.
From the MySQL command prompt:
CREATE USER david;
Now that we have a user we’ll assign them some rights. This statement says “Give david access to all the tables in the example_com_b2evolution database, but only when he logs in from the
localhost. You can set different permissions for when I log in from home or whether I login from work! I don’t like the idea that my security level changes depending on where I’m logging in from. It just feels wrong. I always thought it would be better and cleaner to have a separate user as it can lead to confusion.
GRANT ALL ON example_com_b2evolution.* TO david@localhost;
Finally we must set a password for our user. A side effect of having floating users and host name restrictions is that you can have a different password depending on where you log in from. This seems even wronger!
SET PASSWORD FOR david@localhost=PASSWORD(’!,DaViD54890′);
I have experimented with this in the past and it was a pain in the arse! There were all sorts of peculiar behaviours and I seemed to spend most of my time trying to figure out why you could log in from
userA but not with