Insecure SQL Privileges

I’m in the process of migrating my install to a new server. Looking at the privileges granted by the setup scripts on the original install, I see this:

| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD 'password hash' |
| GRANT ALL PRIVILEGES ON `invoices`.* TO 'user'@'%'    |

Which leads me to a few questions.

  1. Why is global access allowed by default? 'user'@'%'
    I can see in some larger distributed installs where this would be beneficial, but in my opinion on a standard install this is a security hole

  2. Why does this user have access to all databases? GRANT USAGE ON *.*
    I answered this one myself with a quick search, and kept it in case anyone else was curious.
    This one appears to actually make it more secure, by protecting your other databases from this user.
    Taken from MySQL 5.7 Reference Manual

The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

  1. Does this user really need such liberal privileges? GRANT ALL PRIVILEGES
    I don’t actually know what permissions the user needs to accomplish everything, but from everything I know about privileges and security, “The least permissive way to get the job done is the safest.”
    So a list of what privileges actually get used would be beneficial.

PS. I really like this software and I would love to get involved in contributing. I have looked at ways to do so and intend to start once I get my install running again. So thanks to everyone currently and previously involved.

It seems that you may use a different version than the original InvoicePlane. Because there is no such statement in the codebase:✓&q=GRANT

Could you please give use more information about from where you got InvoicePlane.

I wasn’t very clear where that info came from. That code is the result of running SHOW GRANTS FOR 'user'; on the existing InvoicePlane database I was migrating, and it was run in an attempt to find out what privileges the user on the new database needed. I do not recall the exact version I was running, but it was probably before 1.4.0.

I’ll do a fresh install with the latest version and see what database privileges are granted the the user created during install. I will let you know what I find.

Whatever permissions your previous database user has: they were either granted by yourself or by someone other.

The database user for InvoicePlane does not need any global permissions. Just the basic permissions needed to work with a single database:

After trying a fresh install I’ve learned that InvoicePlane doesn’t create a user at all; nor is there stated anywhere in the docs anything about creating a user when discussing the database.

It is pretty common that apps do not create any databases or users. It’s indeed obvious that this is an administration task you have to do.

It is not obvious at all. I hope that was just a bad translation; if those were your exact words as you meant them, then I can no longer use or assist with this project.

When it comes to documentation, nothing is obvious, otherwise one wouldn’t need the docs.

Other issues have already forced me to use something else for my invoicing needs. I wasted an entire day debugging the PDF issue without success. I did post on that in another thread.

Actually it IS common use in Open Source software that DB User (and mostly also the DB itself) are created manually which makes sense from security point of view. You wouldn’t want an application to create a user having ALL privileges now would you?

Bye J :smirk:

1 Like

What a sad way to address people. Kovah is a fine man who invests a lot of time and energy into this project, only to be slapped in the face by rude comments as those of JustinTJones… even after being pointed out as the sole reason for his “security” problems. I guess we won’t miss him when he goes. Good riddance.


@JustinTJones I’m not sure what kind of Open Source packages you use?

There is almost no OS package that create own DB users, since it’s one hassle to get this right.
The OS packages on the internet only ask for user/password that you have (or created yourself).

You don’t have to be rude, if you don’t want to help, then go somewhere else trying “helping” people.
We appreciate anyone that wants to contribute, but respect each other.
Like Kovah and other people in this thread said, it’s common that most (if not all) OS package on the internet only asks for a DB user/pass and not create on for you.

1 Like

Having IP create SQL users automatically and assign privileges on-the-fly would mean IP has root privileges over your DB.
That would also mean you would have to put your SQL root username:password within IP configuration files.
How secure does this sound to you?

Yes, localhost is safer than % and GRANT ALL PRIVILEGES is bad. You will need to blame your DB administrator though, not the contributers. Those are I believe (bad) default settings of PhpMyAdmin when one creates a new user.

Thank you for your concerns.

1 Like