how to give permissions for the user to create a database

[santhanalakshmi]

I am creating a USER in ROOT :

grant all privileges on databasename.* to username@localhost identified by 'password';

Now,i am logging through my username :

mysql -u username -p

Enter password : password

Now, i am trying to create a database :

mysql>create database sample;
it showing an error :

Access denied for user 'username'@'localhost' to database 'sample'

[mwasif]

User will only be able to create database named databasename.

[Atli]

To elaborate on mwasif's point...

You define in your GRANT statement's ON clause which databases the user can use. Any database you do not specify will not be accessible, whether it currently exists or not.

So, because you do:

GRANT ALL PRIVILEGES ON databasename.* ...

your user only has access to the database named "databasename". Trying to create - or in any other way use - another database will fail with the error you posted.

If you want the user to have access to another database, you will need to issue another GRANT statement for that database, or give the user global access by using the wild-card char (*).

GRANT ALL PRIVILEGES ON *.* ...

Be careful with this though, because it gives the user access to all databases, tables and columns, which is usually not a good idea.

[santhanalakshmi]

thanks for ur reply.....if we give GRANT permissions for a user to access a particular database means then that the user cannot create a new database.Its correct na.

we should follow,if the user want to create a new database means...

GRANT ALL PRIVILEGES *.* .....

[Atli]

If you want a user to be able to create whatever databases he likes, then you need to go with the "ON *.*" clause, yes. Just keep in mind that this does give him access to every database on the server, regardless of who created it or when it was/will be created.

You can, however, grant him access to databases that don't yet exist. The GRANT clause doesn't care about that. So if a user is mean to be able to create a set of tables - like say: if you are creating a user for a setup script that sets up it's own databases - then you can GRANT him access to them ahead of time.