Prevent user from creating tables and or databases with hyphens

Question

Is there way in MySQL to prevent someone from creating tables and/or databases with hyphens? (Other than striping privileges from the user.)

For example, if we have user IamADummy who wants to do this,

CREATE TABLE `I-am-a-bad-table-name`;

OR

CREATE DATABASE `Bad-Idea`;

Is there a setting that would stop the user from doing so?

Thanks!

Answer 1

I am not an expert but it does not look like it:

http://www.learn-mysql-tutorial.com/Identifiers.cfm

Politely tell IAMADummy to use underscores and if he continues using hypens (minus sign) "strip the privileges from the user" or run a script every hour that removes all privileges from tables with hyphens. When IAMADummy calls, tell him its a new feature.

Answer 2

I don't think so. Hyphens are valid parts of a database or table name as the schema object names chapter in the mySQL manual points out.

Some brainstorming points:

• Triggers work on table level only

• I don't think there are any hooks that can execute a stored procedure before creation of a database/table - correct me if I'm wrong, of course

• As far as I can see there is no way to use permissions to block creation of tables/databases with a certain naming pattern.

The only thing that comes to mind is sabotaging the creation of the database directory on filesystem level if it contains hyphens. Can certainly be done somehow on a Linux system, but it's a horrible idea.

You would have to teach IAMADummy not to use hyphens in table names, or create a wrapper around mysql that analyzes the queries the user writes, and blocks the execution of offending commands before they are passed on to the database.

Answer 3

The only restrictions on table identifiers are as follows:

• No identifier can contain ASCII NUL (0x00) or a byte with a value of 255.
• Database, table, and column names should not end with space characters.
• Database and table names cannot contain “/”, “\”, “.”, or characters that are not allowed in file names.

In order to have any other restrictions, you would have to examine the query in your system before running it and adjust it appropriately.

Answer 4

If you are using PHP, you can use php functions to pare input.

If you are using MySQL "straight-up" perhaps you or someone you know can put together a small program that acts as a proxy between the user and MySQL. Rename the MySQL program and call the proxy (assuming windows) whatever the MySQL program is named originally.

The proxy should take the login information and a query and pass that to the MySQL client.