Re: privileges for creating databases

>I'm a bit of a beginner with MySQL, but I'm also having to be the DBA as
>well.
>
>On a new install of MySQL, I've set up the root user for me, and also a
>developers user for the software developers. I cannot work out how to set
>up permissions to allow the developers to create databases.

A developer needs CREATE privilege on a database to be able to
create it. The same privilege lets him create tables in it. (He'll
also need SELECT, INSERT, DELETE, and UPDATE to use it, among
others).

>Reading the various books and manuals I've got implies I have to GRANT
>them permissions to the database first from the root user, the problem is
>of course that until they want to create the databases, no one knows what
>they'll be called.

You can grant permissions on *ALL* databases ahead of time (which
you probably don't want to do), or you can insist you know something
of the name beforehand and use wildcards on the database name, and,
say grant Bob create privilege (and others he'll need to use them)
on databases 'bob\_%', and grant John create privilege on databases
'john\_%'. (That backslash is needed to prevent the _ from being
interpreted as a wildcard).

It is also possible to grant permissions to the developers WITH
GRANT OPTION so they can grant single-database limited-privilege
accounts for use by webservers or whatever.

>> >I'm a bit of a beginner with MySQL, but I'm also having to be the DBA as
>> >well.
>> >
>> >On a new install of MySQL, I've set up the root user for me, and also a
>> >developers user for the software developers. I cannot work out how to set
>> >up permissions to allow the developers to create databases.
>
>> A developer needs CREATE privilege on a database to be able to
>> create it. The same privilege lets him create tables in it. (He'll
>> also need SELECT, INSERT, DELETE, and UPDATE to use it, among
>> others).
>
>Sure.
>
>> >Reading the various books and manuals I've got implies I have to GRANT
>> >them permissions to the database first from the root user, the problem
>> >is of course that until they want to create the databases, no one knows
>> >what they'll be called.
>
>> You can grant permissions on *ALL* databases ahead of time (which
>> you probably don't want to do),
>
>No indeed.
>
>> or you can insist you know something
>> of the name beforehand and use wildcards on the database name, and,
>> say grant Bob create privilege (and others he'll need to use them)
>> on databases 'bob\_%', and grant John create privilege on databases
>> 'john\_%'. (That backslash is needed to prevent the _ from being
>> interpreted as a wildcard).
>
>I couldn't make this work. My statement:
>GRANT ALL ON re\_%.* TO re_dev@'%';

MySQL seems to like backquotes on database names with funny
characters in them:

GRANT ALL ON `re\_%`.* TO re_dev@'%';