Setting up application privileges in MySQL

Question

Say you created a blog application, and it's data is stored in a MySQL database. In your application configuration you set the data source name to myBlog user root password whatever

Now, when users start using your blog to access, post to, and comment on threads, etc... I am assuming they connect as root through the application myblog ...

So... users connect to the application myBlog who in turn connects to MySQL as user root , using password whatever --- it's not really the users that are connecting to MySQL, it's the application. Correct?

Is there not a security issue with this approach? Should I create a new username in MySQL for the application myBlog with specific privileges and leave root only for administering the database?

Answer 1

yes, the application connects to the db. you should create a new mysql user for your application, do something like

CREATE DATABASE myblog_env;
CREATE USER 'myblogenv-user'@'%' IDENTIFIED BY 'your pw';
GRANT ALL PRIVILEGES ON myblog_env.* TO 'myblogenv-user'@'%' WITH GRANT OPTION;

something like the above should do it. The 'env' part of the above is for if you want to create a new db for difference environments, like dev, stage, prod, whatever....

this way your application user has complete access to its db, but no other dbs in the mysql instance.

Answer 2

First of all, you should NEVER use the root account of a mysql database for anything else then admin work. Second of all, in theory yes the user of your blog would be the "root" in your mysql database, but hopefully there is a lot of sanatizing and cleaning up in your blogs code before any queries are executed...anything else would be know as an "sql inject"

Answer 3

The short answer is: Yes.

Long answer:

Security: You should have a different user for your application than you do for yourself as the administator. That application user should only have read (and write if necessary) privileges on the specific database it needs to access. Also, it should not have privilege-granting privileges, nor drop table privileges, nor database creation/dropping privileges, nor anything else that is reserved for you.

Convenience: If you ever need to change your password, you don't want to have to change your application, and vice versa.

Answer 4

You are exactly right. This is called the principle of least privilege. You should give the application the minimum access rights that it needs to complete the job. This would not be root.