CODEWITHSUNDEEP
mysql
Kosta Kontos
Kosta Kontos

Reputation: 4322

Allow MySQL user with limited database access to create more users with similar access

I know that I can create a user with the create user privileges like so:

create user primary_user identified by 'pass';
grant all on *.* to primary_user with grant option;

This will in turn allow me to create users whilst logged in with primary_user.

Now let's assume I have a subset of databases, all with prefix abc_, and that I want my primary_user to only be able to access these databases.

Then the grant query above would look like so:

grant all on `abc_%`.* to `primary_user` with grant option;

The problem now, however, is that when I log in with my primary_user and try to create a secondary_user, I get the following error:

ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

TL;DR

Basically what I want is primary_user to only be able to access databases with the abc_ prefix, and to also be able to create secondary users that in turn also only have access to databases with an abc_ prefix.

Is this possible? Or is my only option to create the secondary users with a user account that has create user privileges on *.*?

Upvotes: 2

Views: 659

Answers (1)

Sevle
Sevle

Reputation: 3119

As mentioned in MySQL Documentation:

To use CREATE USER, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database.

Thus, users with privileges in specific databases cannot create users. Ofcourse, since you have with grant option, your primary_user can grant all database level privileges to other already created users (for the abc_ databases).

Note #1: Here you can find an interesting table with the various privileges and the different levels that they can be granted.

Note #2: Be extra cautious when giving GRANT OPTIONS to non-admin users because they can modify the privileges of other users which can lead to chaos. The Open Web Application Security Project states:

[Grant privilege]... should be appropriately restricted to the DBA and Data (Table) owners. Give specific permissions on an as needed basis and use different logins for different purposes.

Upvotes: 1

Related Questions