LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
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.

Notices


Reply
  Search this Thread
Old 09-06-2006, 01:33 AM   #1
Swakoo
Member
 
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, 03:54 AM   #2
bigrigdriver
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, 03:57 AM   #3
Swakoo
Member
 
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.

anyone?
 
Old 09-13-2006, 02:17 AM   #4
Swakoo
Member
 
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Original Poster
Rep: Reputation: 30
any advices?
 
Old 09-13-2006, 06: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
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.

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


Reply


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 01:11 AM
MySQL database - mail label creation mazzo Linux - Software 0 05-25-2006 06:36 AM
grant a user FTP rights only plisken Linux - Software 0 01-31-2004 05:55 PM
Mysql User grant problems ubers0ldat Linux - Software 7 07-30-2003 12:33 AM
mysql database creation zuessh Linux - Software 17 05-14-2003 01:13 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - General

All times are GMT -5. The time now is 10:39 AM.

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