This is a tip for the new MySQL users out there. A common and dangerous practice used by many new developers and admins is to use the root account to access databases. While this certainly gives you the access you need to manage your databases and data from your applications it also elevates privileges far beyond what you really want.
Let’s assume that you are creating a database called mycontent for an application. You will also want to create a user that has access to just that database so that it isolates the rights to only the content you want to affect.
Decide on a good name for a user. For this example I will use the classic format of databasename_user so our user will be mycontent_user which is what we will use for our application.
Make sure you choose a complex password as well. It is always best to combine upper and lower case as well as numbers. Be careful with special characters because using some characters like /\%* can cause issues with systems when they pass the characters to a command. We are going to use 7U2JkyGg9TwbNnQ as our example password.
Launch MySQL from the command line as a user who has privileges to create databases and manage security.
mysql -u myadminusername -p
Type in your password at the ‘Enter password:’ prompt
Now we create the database:
create database mycontent;
Next we create your database user. I am assuming that your database server is also your web server so we will use the @localhost to define the user.
grant usage on *.* to mycontent_user@localhost identified by ’7U2JkyGg9TwbNnQ’;
Next step is to apply permissions to the database for your new user:
grant all privileges on mycontent.* to mycontent_user@localhost;
And finally we check our user is able to log in and access the database. Exit from your MySQL command line and log in again using the new account:
mysql -u mycontent_user -p’7U2JkyGg9TwbNnQ‘ mycontent
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
Success!
Now you can use this account/password combination in your application and it will have all of the privileges necessary to manage the database, tables, indexes and content with much less chance of compromising your other databases in your system.
