I have the following scenario:
- mariaDB (
mysql Ver 15.1 Distrib 5.5.49-MariaDBto 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
amyshould be able to create a database
amy_courseworkbut 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?