Creating a web portal to access multiple databases (security and best practice?)

Question

I am looking to create a website.

There will be a portal, from which the user (and thier associated users) can create/access one or more databases. There will be many different databases in the background (for historical reasons, please just go with that bit).

It must obviously be as secure as possible.

I thought it probably needs a database that then has links for the other databases to be accessed. So, the portal might have a schema like simplified example below.

enter image description here

When the user clicks a database link, the 'initialAccessKey' would create a session token with which the database could be accessed.

My questions are:

1. Is this a reasonable way to approach the task?

2. Is there a better way?

3. Is it secure? It seems like all the sensitive info is in one place! A bug could enable access to someone elses database, which would be catastrophic.

Any help much appreciated.

EDIT: Ben Whyall's answer is good for an environment where AD can be used (have upvoted). If anyone has a suggestion without AD, I'd be interested to know.

• 9000: "100% secure" against what? Think about and write down your threat model. Role-based security is fine for most cases, though. You can consider mapping it directly to databases' roles / users, e.g. by creating views only including the right subsets of tables / rows for the role.
• Robert Harvey: There's no such thing as "100% secure."

Answer 1

We do this in a different way. We have a directory based (in our case MS Active Directory) authentication. Access to the databases is given to active directory groups within SQL server. Then the central repository database holds only the databases available to particular active directory groups.

When a user logs on his/her groups are retrieved and used to get a list of databases that the user can access.

Within the system we also have a granular access control to screens and functions by active directory group too.

Even if the access portal showed the database list the fundamental restrictions in SQL would prevent the data being compramised.