One database user for several databases — Docs

An application that uses two databases — a main one and a separate schema for a back office, say — does not need two logins. Grant one user access to both.

Give a user another database

Databases → Database users → on the user's row, Add database.

You are asked for the short name of the database (shop, not youracct_shop). The user keeps its existing password and its existing access; the new database is added to it.

Each row in that table shows what the user can currently reach:

app        Databases: shop, admin        Access: localhost only

A user with nothing yet reads no databases yet.

Take one away

Same row, Remove database. The button appears once the user holds at least one database. You are asked which one, and it is prefilled when there is only one to choose from.

Removing a database from a user does not delete the user, and does not touch the database. The user keeps its password and every other database it holds. That is the point: before this existed, the only way to withdraw access was to delete the user, which broke every site still signing in with it.

Worth knowing before you use it

Access is granted one database at a time. A user never receives blanket access to the server; each grant names a single database of yours.
You can remove the last user of a database. Nothing stops you, and nothing warns you. The database and its contents remain, but no login can reach it until you grant one again — which is the fix if a site suddenly reports it cannot connect.
Names carry your account prefix. A database you create as shop is youracct_shop on the server, and a user app is youracct_app. Type the short name in the panel and the full name in your application's configuration.
Only your own databases and users. A grant naming another account's database is refused. So is a revoke.

Setting one up from scratch

Databases → Create database, twice — for example site and admin.
Create user & connect: name the user, choose site from the dropdown. That is the first grant.
On the new user's row, Add database, and enter admin.

Copy the password when it is shown. The panel stores it in a form it cannot read back, so a lost password is reset, not recovered — that is Password on the same row.

When something looks wrong

"No such database for this account." The name was mistyped, or you entered the prefixed form. Use the short name as it appears in the Databases table above.

"No such database user for this account." The user list on the page is stale. Reload it.

The application connects to one database but not the other. Confirm the row lists both under Databases, and that the application is using the prefixed name (youracct_admin) and the same host — localhost unless you configured Remote hosts for that user.

A grant appears to have no effect from a remote machine. Grants added here apply to the local login. If that user also connects from an outside address, re-save its Remote hosts list after granting, so the remote entry is brought up to date with the databases the user now holds.