Is it possible to create a mid-root MySQL user, who will have the ability to create other users, with scope limited to databases which mid-root user creates? This mid-root user would not have the privileges of a root user, and would not be able to perform any of the CRUD operations on any of the databases other than on those they've created themselves.

Hopefully, this illustration would do a better job of explaining.enter image description here

In this scenario, the user ROOT has access to everything, and can create other users, with limited privileges.

The user called mid-root is somewhat of a demigod or middle management - they can create other users as well, but can grant them only access to the databases that the mid-root user has created. Mid-root can only access the databases they themselves have created (simple db1, simple db2, etc), while being unable to access those created by the root user (db1, db2, db3, db4).

Finally, users created by the fictional mid-root user could only perform standard CRUD operations with their given databases. By standard, I mean everything other than DROP database, or ALTER table, or TRUNCATE, etc.

Is any of this possible?

Real life use scenario follows.

There is a webapplication, and a potential client or clients (one or more of hundreds, or thousands) wishes to try out a demo for a certain period of time (a month or so). When that client registers, background logic creates a MySQL user and the appropriate DB, tables, etc, and fills them with necessary data. Since the server might be shared between different types of webapps, there's a need for a mid-root user, who can perform these tasks, and only them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.