1

I want to grant a user (my program) all access rights to a given database - read/write, even delete.

It is important that, after deletion (and, initially, before it ever exists), the user be able to create the database - but only with a given database name and the user should have no access to anything other than this database.

I am at a loss of the GRANT ...

Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
  • 1
    Look if it can help you http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database – Jkike Aug 28 '15 at 08:25

1 Answers1

1

The database does not have to exist to grant access to it. As a privileged user such as root you can do

mysql> grant all on dooda.* to 'dooda'@'localhost' identified by 'dooda';
mysql> exit

then

jason:>mysql -u dooda -p
Enter password: 
etc
mysql> create database dooda;
Query OK, 1 row affected (0.00 sec)

but you can't

mysql> create database somethingelse;
ERROR 1044 (42000): Access denied for user 'dooda'@'localhost' to database 'somethingelse'

and if you

mysql> drop database dooda;
Query OK, 0 rows affected (0.00 sec)
mysql> create database dooda;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dooda              |
| test               |
+--------------------+
MagicLAMP
  • 1,032
  • 11
  • 26
  • Ok, I think that I understand, and that is what I expect. I - as root - create the user and grant all rights to that user on the non-existent database, and then the user can create and manipulate only that database? is that correct? – Mawg says reinstate Monica Aug 28 '15 at 08:56
  • 1
    yes. They can even delete the database and recreate it, but they cant create any database with a name different to the one root grants them access to. – MagicLAMP Aug 28 '15 at 12:12