Let MySQL users create databases, but allow access to only their own databases

Question

I want to have multiple a MySQL users to be able to issue commands like

CREATE DATABASE dbTest;

But I also want each of these users to be able to see and access only their own databases.

All I could find was how to either create the databases by a DBA and grant the privileges on this database to the specific user:

GRANT ALL PRIVILEGES ON dbTest.* TO 'user';

or grant privileges on all databases to a user:

GRANT ALL PRIVILEGES ON *.* TO 'user';

But neither is what I want, because it needs to scale and be secure.

V. Bozz · Accepted Answer · 2024-11-11 13:00:23Z

107

You can use

GRANT ALL PRIVILEGES ON `testuser\_%` .  * TO 'testuser'@'%';

to grant the user testuser privileges on all databases with names beginning with testuser_.

This allows the user testuser to create databases limited to names starting with testuser_.

Note that the query has an escape before the underscore, since the underscore is a glob that means "whatever single character", so, the escape.

edited Nov 11, 2024 at 13:00

V. Bozz

1,6321 gold badge23 silver badges42 bronze badges

answered Jun 1, 2010 at 13:30

user254875486

11.3k7 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Drew Hammond · Accepted Answer · 2014-12-04 01:03:45Z

26

You can use

GRANT ALL PRIVILEGES ON `testuser_%` . * TO 'testuser'@'%';

to grant the user testuser privileges on all databases with names beginning with testuser_.

EDIT: I'm not sure if this user is now also allowed to create databases.

Yes, this allows the testuser to create databases limited to names starting with testuser_

edited Dec 4, 2014 at 1:03

Drew Hammond

5885 silver badges19 bronze badges

answered Oct 24, 2012 at 0:55

Xycris

2853 silver badges2 bronze badges

3 Comments

Programster Over a year ago

I had to wrap backticks around testuser_% but unfortunately in stack overflow that wont come through as backticks signify a code section.

Drew Hammond Over a year ago

This should be a comment on Lex's answer, not an answer itself.

sitilge Over a year ago

I'm not sure if this user is now also allowed to create databases.... No, it isn't.

Janek Bogucki · Accepted Answer · 2010-06-01 13:39:07Z

16

Create a stored procedure that is defined by the admin user and invokes with the admin user privileges by using SQL SECURITY DEFINER. In the stored procedure,

Create the database.
Set the privileges on the database so only the current user has access.
Execute FLUSH PRIVILEGES to reload the privileges from the grant tables.

Use USER() to get the current user login details.

Find out more about SQL SECURITY DEFINER.

edited Jun 1, 2010 at 13:39

answered Jun 1, 2010 at 13:34

Janek Bogucki

5,1433 gold badges32 silver badges41 bronze badges

1 Comment

Nest Over a year ago

Where to create the stored procedure. Which database has this stored procedure?

Ada Lovelace · Accepted Answer · 2015-06-29 21:43:12Z

1

It is impossible to do this using permissions only .

The workaround as suggested in another answer: GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%'; has the problem that the users must then be very careful in naming their databases.

For example if user aaa creates database bbb_xyz, it can then be accessed exclusively by user bbb but not by user aaa.

edited Jun 29, 2015 at 21:43

answered Jun 29, 2015 at 21:36

Ada Lovelace

8852 gold badges8 silver badges20 bronze badges

1 Comment

Jack_Hu Over a year ago

I don't catch your meaning... user aaa will not be able to create a database bbb_xyz, it will only be able to create a database called aaa_xyz

Collectives™ on Stack Overflow

Let MySQL users create databases, but allow access to only their own databases

4 Answers 4

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related