Bug #78885

Install tool database examination fails with non-omnipotent database user

Added by Joschi Kuphal over 3 years ago. Updated about 1 month ago.

Status:
Under Review
Priority:
Should have
Assignee:
Category:
Install Tool
Target version:
-
Start date:
2016-12-05
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
Yes
Sprint Focus:

Description

With a fresh TYPO3 8.4 installation, it seems not possible to successfully run the installation wizard when

  • there are multiple databases,
  • the database user intended for installation * has the global privilege SHOW DATABASES, * has full privileges for the database intended for TYPO3, * has no privileges for all the other databases.

As soon as the database credentials are entered and submitted, it seems all databases get examined (e.g. for existing tables). During this process, the installer obviously even tries to access the databases which the given user has no access privileges for, resulting in the following error (see attached screenshot as well):

An exception occured in driver: Benutzer 'username'@'localhost' hat keine Zugriffsberechtigung für Datenbank 'bla'

(Translation: "User 'username'@'localhost' has no access privileges for database 'bla'". "username" and "bla" have been replaced in the error message. "bla" is another existing database which "username" has no access privileges for.)

After the first appearance of the above mentioned error, the installation attempt cannot be continued under no circumstances ("Oops ..." for all actions; no restart possible). All resources generated by the install tool have to be removed (which practically is a completeley fresh install).

The exact same setup worked (and works) flawlessly with TYPO3 7.x (and possibly before; not sure about TYPO3 8.0 - 8.3, didn't try these).

The only solution right now is to give the database user full access privileges for all databases — which works but surely isn't a viable solution.

Updates:

typo3_install_tool_db_error.png View (20.3 KB) Joschi Kuphal, 2016-12-05 09:51

Schermata 2018-05-18 alle 23.17.17.png View (20.9 KB) Riccardo De Contardi, 2018-05-19 00:01

Schermata 2018-05-18 alle 23.17.33.png View (75.2 KB) Riccardo De Contardi, 2018-05-19 00:02

Schermata 2018-05-18 alle 23.18.40.png View (50 KB) Riccardo De Contardi, 2018-05-19 00:02

Schermata 2018-05-18 alle 23.27.29.png View (70 KB) Riccardo De Contardi, 2018-05-19 00:02

db.png View (31.1 KB) Sybille Peters, 2018-05-20 12:48

History

#1 Updated by Benni Mack over 3 years ago

  • Target version changed from 8.5 to 8.6

#2 Updated by Benni Mack over 3 years ago

  • Target version changed from 8.6 to 8 LTS

#3 Updated by Benni Mack about 3 years ago

  • Target version changed from 8 LTS to next-patchlevel

#4 Updated by Riccardo De Contardi about 2 years ago

Is this one still reproducible? I performed the following test:

Prerequisites:

- MAMP environment with MAMP Pro (PHP 7.1.12, MySQL 5.6.38)
- Fresh installation of TYPO3 8.7.13

Test procedure:

- I created an empty database called "typo3test2"
- I created a database user called "typo3test2" and granted him the privileges as shown on the attached files
- I started the usual TYPO3 installation (manual installation with symlinks);

Results:

- When choosing the database, I was able to choose only typo3test2 (see attached Schermata 2018-05-18 alle 23.27.29.png)
- No error has been encountered, the installation has been successful.

I admit I am not expert in DB administrator, so is there a different test that should be performed? Or under different conditions?

#5 Updated by Sybille Peters about 2 years ago

  • Status changed from Needs Feedback to New

@Joschi

Thank you for your bug report. It would be really helpful in the future if you also provide some steps to reproduce for errors like these.

The less time it takes to try to reproduce issues, the more likely the bug is to get fixed.

I could reproduce it with the following:

Reproduce

System

  • TYPO3 9.3-dev (it is very probable that it is also reproducable with current 8.x version)
  • MySQL Server version: 5.7.22-0ubuntu0.16.04.1 (probably not relevant)

Steps to reproduce

1. Setup database and database permission in DB server

mysql> CREATE database db CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> CREATE USER username@localhost identified by 'password';
mysql> GRANT SHOW DATABASES ON *.* TO username@localhost;
mysql> GRANT ALL ON db.* TO username@localhost;

2. Start installation ...

Result:

Couldn't select existing database and got exception:

Exception
An exception occurred in driver: Access denied for user 'typo3'@'localhost' to database 'redmine_default'

General observations

But another thing: Why would you want to give a Database User for TYPO3 show databases privileges to all databases?

In my opinion this makes no sense at all and is not to be recommended. It's not required for TYPO3 and for reasons of security it is best to give only access that is required and nothing more. Even though in this particular case it does no harm but why would you do it?

Summary

I can reproduce it, and it is a bug and I agree it should be fixed, but I would give it low priority, because it can only be reproduced by doing things you should not be doing anyway.

#6 Updated by Sybille Peters about 2 years ago

  • Status changed from New to Accepted

#7 Updated by Sybille Peters about 2 years ago

quote: "The only solution right now is to give the database user full access privileges for all databases — which works but surely isn't a viable solution."

No, a better solution is to not give TYPO3 user access to `show databases` in the first place or when you get the exception while installing you can revoke it:

mysql>revoke show databases on *.* from user@localhost;

If I then reload the page with the exception, I can select the correct database and continue with the installation. This works for TYPO3 9.3-dev. Hopefully it works for TYPO3 8 too.

#8 Updated by Riccardo De Contardi about 2 years ago

  • Priority changed from Must have to Should have
  • Target version deleted (next-patchlevel)

I lower the priority for now; adjust it or ping me if this is the wrong decision

#9 Updated by Sybille Peters about 2 years ago

  • Description updated (diff)

#10 Updated by Joschi Kuphal about 2 years ago

@Sybille

First of all, thanks for your elaborations. I'm sorry I didn't include more information to reproduce — I simply didn't think that I was doing something so special that there's the need for extra instructions. I did nothing more than just run an regular installation as I'm successfully doing it for 15 years now, on the exact same setup as always, and all of a sudden it stopped working (and, btw, still doesn't work again with v9.3).

The only solution right now is to give the database user full access privileges for all databases — which works but surely isn't a viable solution.

To be clear here: This is NOT AT ALL something I recommend. It's just the only workaround I could find so far. Revoking global access after the install tool has done its job works just fine.

No, a better solution is to not give TYPO3 user access to `show databases` in the first place or when you get the exception while installing you can revoke it:

I can't remember when exactly I started doing so automatically (must be many, many years ago), but I'm absolutely sure that at some point in the past TYPO3 even required me to give it the global privilege to list (but not access) all databases. How would the install tool be able to fill the database selector otherwise? Adding the "show all databases" privilege has been part of my default setup routine for new TYPO3 project ever since (and this has never been a problem so far, btw). To be honest, it didn't come to my mind to even try it without this privilege, so I'll still have to test this. I suspect it to fail, however. I'll let you know as soon as I can confirm (or not confirm). Thanks.

#11 Updated by Frank Naegler about 2 months ago

  • Status changed from Accepted to In Progress
  • Assignee set to Frank Naegler

#12 Updated by Gerrit Code Review about 2 months ago

  • Status changed from In Progress to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/64564

#13 Updated by Gerrit Code Review about 2 months ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/64564

#14 Updated by Gerrit Code Review about 2 months ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/64564

#15 Updated by Gerrit Code Review about 2 months ago

Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/64564

#16 Updated by Gerrit Code Review about 1 month ago

Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/64564

Also available in: Atom PDF