Latest LQ Deal: Linux Power User Bundle
Go Back > Forums > Linux Forums > Linux - General
User Name
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.


  Search this Thread
Old 09-06-2006, 02:33 AM   #1
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Rep: Reputation: 30
MySQL: Grant user database creation rights

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!
Old 09-06-2006, 04:54 AM   #2
LQ Addict
Registered: Jul 2002
Location: East Centra Illinois, USA
Distribution: Debian stable
Posts: 5,908

Rep: Reputation: 354Reputation: 354Reputation: 354Reputation: 354
Section 5.5.2 of the MySQL manual has instructions describing how the root administrator can grant permissions to users (including CREATE privilege).
Old 09-07-2006, 04:57 AM   #3
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Original Poster
Rep: Reputation: 30
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.

Old 09-13-2006, 03:17 AM   #4
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Original Poster
Rep: Reputation: 30
any advices?
Old 09-13-2006, 07:34 AM   #5
Wim Sturkenboom
Senior Member
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Login as database administrator
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
Please note that database dba does not have to exist yet.

Login as user wim and create the database
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)

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.

Last edited by Wim Sturkenboom; 09-13-2006 at 07:49 AM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Allowing the creation of MYSQL databases by a user rustyz82 Linux - Software 5 06-26-2006 02:11 AM
MySQL database - mail label creation mazzo Linux - Software 0 05-25-2006 07:36 AM
grant a user FTP rights only plisken Linux - Software 0 01-31-2004 06:55 PM
Mysql User grant problems ubers0ldat Linux - Software 7 07-30-2003 01:33 AM
mysql database creation zuessh Linux - Software 17 05-14-2003 02:13 PM > Forums > Linux Forums > Linux - General

All times are GMT -5. The time now is 02:33 AM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration