MySQL: Grant user database creation rights

Swakoo · 09-06-2006, 01:33 AM

Hi guys, I am now practicing specifying what database my user can access (and its a painful process without phpmyadmin cos I can't access it via web!)

Right now I give them the standard rights of select,insert,update,create,delete,drop,alter

but they can't create database. am I able to set it they can create database, and that only they can access? can i restrict the number of database they can create? thanks!

bigrigdriver · 09-06-2006, 03:54 AM

Section 5.5.2 of the MySQL manual has instructions describing how the root administrator can grant permissions to users (including CREATE privilege).

Swakoo · 09-07-2006, 03:57 AM

don see any related topic on that chapter?

Also, in MySQL manual, under GRANT Syntax, it explains 'CREATE' as create privilege for tables.

Yet in 5.7.3 (Privileges Provided by MySQL) under MySQL v4 manual, it explains Create as privilege for database and tables.

Mixed info..

but currently I have even tried issuing 'Create' privilege on *.* but to no avail.

anyone?

Swakoo · 09-13-2006, 02:17 AM

any advices?

Wim Sturkenboom · 09-13-2006, 06:34 AM

Login as database administrator

Code:

mysql> grant all on dba.* to 'wim'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'wim'@'localhost';
+-----------------------------------------------------------------------------------+
| Grants for wim@localhost                                                          |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wim'@'localhost' IDENTIFIED BY PASSWORD '571c3ced13511691' |
| GRANT ALL PRIVILEGES ON `dba`.* TO 'wim'@'localhost'                              |
| GRANT ALL PRIVILEGES ON `btd_budget`.* TO 'wim'@'localhost'                       |
| GRANT ALL PRIVILEGES ON `btd_documents`.* TO 'wim'@'localhost'                    |
| GRANT ALL PRIVILEGES ON `cc_rfs`.* TO 'wim'@'localhost'                           |
+-----------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

Please note that database dba does not have to exist yet.

Login as user wim and create the database

Code:

mysql> create database dba;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+---------------+
| Database      |
+---------------+
| btd_budget    |
| btd_documents |
| cc_rfs        |
| dba           |
+---------------+
4 rows in set (0.00 sec)

mysql>

I'm not a guru, but as far as I know you can not grant permissions to users to generate a number of databases. Either they can create any database (same as the administrator) or they can create predefined ones (as shown above after which the users can create them).

Personally I also don't see a need for it. The administrator should (in my opinion) decide on the databases that a user can create. Point is that you might want to create a db called mydb and I also want to do that. There can however only be one mydb.