Want to create a mysql user who can create new databases users

Question

I'm creating a SAAS website, and every new account creation should have :

a new database created for it dynamically from my php code ,
a new MySQL user created dynamically from my PHP code and granted with privileges over the new database.

My question is: How can I create a MySQL user who have privileges to do these actions , ( create db, create users, grant privileges).

But It's important to note: I want this MySQL user to not be able to show or manipulate any other database not created by him.

Note: I have a server with WHM access.

score 3 · Accepted Answer · 2020-05-19 00:00:05Z

3

To enable the new user called userguy to create other users on database db

create the user

CREATE USER 'userguy'@'%' IDENTIFIED BY 'password';

he needs reload on global rights

GRANT CREATE USER, RELOAD  ON *.* TO 'userguy'@'%';

then whatever rights you want to give him on db

GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES  ON `db`.* TO 'userguy'@'%' WITH GRANT OPTION;

and flush at the end

FLUSH PRIVILEGES;

this should do the job.

UPDATE:
I am sorry, I have not read attentive the question (blame the coffee). If the user should also create new databases he also needs the global CREATE right, and for creating user for the DBs created by him he also needs CREATE USER

GRANT CREATE, CREATE USER, SELECT, RELOAD  ON *.* TO 'userguy'@'%';

In my opinion there is no need for global GRANT privileges

edited May 19, 2020 at 0:00

answered May 18, 2020 at 2:17

user1986815

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

2 Comments

Shadow Over a year ago

Nope, this user will not have the ability to create databases, so this answer is not correct.

user1986815 Over a year ago

@Shadow if you read the first row "To enable the new user called userguy to create other users on database db" ...

Moustafa Egypt · Accepted Answer · 2020-05-18 17:43:46Z

0

I have found the main answer to my problem in the following link: https://stackoverflow.com/a/13041542/9419598

By using : GRANT ALL PRIVILEGES ON testuser_% . * TO 'testuser'@'%';

this will allow the testuser to create databases starting with testuser_ and will have al priviledges over them

And he will not have privileges over other databases. And I can grant him the create user privilege

This does the stuff.

answered May 18, 2020 at 17:43

Moustafa Egypt

791 silver badge11 bronze badges

1 Comment

user1986815 Over a year ago

CREATE, CREATE USER, SELECT, RELOAD is enough

Collectives™ on Stack Overflow

Want to create a mysql user who can create new databases users

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related