1

I have the following scenario:

  • mariaDB (mysql Ver 15.1 Distrib 5.5.49-MariaDB to be exact - I could bug the sysadmins to update if necessary)
  • I want users to be able to create their own, distinct databases that start with their own name and an underscore. So user amy should be able to create a database amy_coursework but not interfere with other people's databases.

My first thought was to create a stored procedure for this use case and grant everyone execute access on it. The following does not work (if it did, I'd replace USER_ with something derived from CURRENT_USER() to get the username, then add a GRANT statement at the end):

DROP PROCEDURE IF EXISTS create_schema;
DELIMITER $
CREATE PROCEDURE create_schema(IN name VARCHAR(20))
    BEGIN
    SET @sname = CONCAT('USER_', name);
    PREPARE stmt FROM 'CREATE SCHEMA ?';
    EXECUTE stmt USING @sname;
    DEALLOCATE PREPARE stmt;
END $

You can't use positional parameters in DDL so that ? won't work, but I hesitate to just concatenate strings together and risk SQL injection.

Is there any sensible and secure way to do this?

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.