So here’s a recent requirement that I’ve just encountered.
- I have a shared MySQL database
- I have several users that I want them to be able to create new databases
- However, I don’t want them to see each other’s databases
- And just to be absolutely clear, I obviously don’t want to give them all privileges to *.*
GRANT CREATE DATABASES ON *.* TO 'customer1'@'%';
Intuitively, I thought I can simply GRANT the user ability to create databases and that’s it. It turned out that that’s not sufficient. The user will be able to create database and tables, however, they won’t automatically have access / privileges to the newly created database until database admin explicitly grants privileges on the newly created database. Clearly this solution is a non-starter.
For the impatience, here’s the solution that worked for me. Read on for explanation.
DROP PROCEDURE IF EXISTS createdb; DELIMITER $$ CREATE DEFINER = 'root'@'localhost' PROCEDURE createdb (IN dbname CHAR(50)) BEGIN DECLARE EXIT HANDLER FOR 1007 SELECT CONCAT("Can't create database '", dbname, "'; database exists") as msg; SET @v_createdb = CONCAT('CREATE DATABASE ', dbname); SET @v_grant = CONCAT('GRANT ALL PRIVILEGES ON ', dbname, '.* TO \'',SUBSTRING_INDEX(USER(),'@',1),'\'@\'%\''); PREPARE stmt_createdb FROM @v_createdb; PREPARE stmt_grant FROM @v_grant; EXECUTE stmt_createdb; EXECUTE stmt_grant; FLUSH PRIVILEGES; END $$
The solution in detail
For reference please have MySQL’s documentation about creating stored procedure handy.
So I started by creating procedure and explicitly define a DEFINER for it for my root user. This is important because when someone executes it, MySQL’s default behavior is that the statements inside the procedure will run as if DEFINER is running them.
Then on line 8 – 10, I setup an error handler of 1007, which is the error code when we attempt to create database that’s already exists.
Line 12 – 17 is the actual statements that’ll be executed when running this stored procedure. It’ll create the database and grant the invoking user to the newly created database using MySQL’s USER() function. The reason I have to initialize variables that contain SQL statements first then create prepared statement then execute it is because I found that MySQL EXECUTE doesn’t work with incoming variable dbname on USING section. Hence, the following doesn’t work, hence, I have to resort to what I have:
SET @v_createdb = CONCAT('CREATE DATABASE ?'); EXECUTE stmt_createdb USING dbname;
It’s true that I could have assigned dbname to @dbname variable, etc etc which could be a better solution and better security (see below). There are many different ways you can do it. I just chose what I chose and it’s sufficient for my purpose now.
Line 19 – 21 is straight forward, I think. It creates database. Then grants the invoker access to the database. Then flush the privileges so that the invoker can access the newly created database immediately.
Caveat / potential improvements
The first caveat that I found is that I needed a database that can host this stored procedure. For my purpose, I just create a database named mgmt with no tables and have the stored procedure there. Then for my users, I’ll just have to grant them EXECUTE privilege on that database.
GRANT EXECUTE ON `mgmt`.* TO 'customer1'@'%';
In general, creating SQL statements with un-checked incoming variable is a bad code all around. User can, perhaps do SQL injection that’ll run as root privilege. Something like below, perhaps.
CALL mgmt.createdb('jajal'); CALL mgmt.createdb('jajal2; DROP DATABASE jajal');
When I run the 2nd statement, I got the following error which looks like the error is on the 2nd statement when it’s trying to grant privilege. Which could mean that DROP DATABASE jajal could’ve been executed.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';.* TO 'customer1'@'%'' at line 1
There are many ways to fix this, I’ll just leave it up to you guys to improve the stored procedure. I hope you enjoyed my solution and explanation. Please leave comments / suggestions / questions if you have.