Professional Web Applications Themes

privileges for creating databases - MySQL

Hi folks, 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. 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 ...

  1. #1

    Default privileges for creating databases

    Hi folks,

    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.

    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.

    Is there a way around this seeming impasse?

    Many thanks,

    Dave

    --
    Dave Stratford ZFCA
    http://daves.orpheusweb.co.uk/
    Hexagon Systems Limited - Experts in VME systems development

    Dave Guest

  2. #2

    Default Re: privileges for creating databases

    >I'm a bit of a beginner with MySQL, but I'm also having to be the DBA as 

    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).
     

    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.

    Gordon Guest

  3. #3

    Default Re: privileges for creating databases

    In article <supernews.com>,
    Gordon Burditt <org> wrote: [/ref]
     

    Sure.
     [/ref]
     

    No indeed.
     

    I couldn't make this work. My statement:
    GRANT ALL ON re\_%.* TO re_dev'%';

    Just gave me Error: Unknown command '\_' followed by a No database
    selected.

    When I did a use on the MySQL database I then get You have an error ...
    near '\_%.* etc.
     

    Erm. Let me go away and re-read my manual on this one.

    Thanks,

    Dave

    --
    Dave Stratford ZFCA
    http://daves.orpheusweb.co.uk/
    Hexagon Systems Limited - Experts in VME systems development

    Dave Guest

  4. #4

    Default Re: privileges for creating databases

    >> >I'm a bit of a beginner with MySQL, but I'm also having to be the DBA as [/ref]

    >
    >Sure.
    > [/ref]

    >
    >No indeed.

    >
    >I couldn't make this work. My statement:
    >GRANT ALL ON re\_%.* TO re_dev'%';[/ref]

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

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

    Gordon Guest

  5. #5

    Default Re: privileges for creating databases

    In article <supernews.com>,
    Gordon Burditt <org> wrote:
     [/ref]
     
     

    Brilliant, thank you very much indeed.

    Dave

    --
    Dave Stratford ZFCA
    http://daves.orpheusweb.co.uk/
    Hexagon Systems Limited - Experts in VME systems development

    Dave Guest

Similar Threads

  1. Replies: 6
    Last Post: September 19th, 08:28 AM
  2. Any head-starts for creating databases?
    By RonSanSolo in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: June 26th, 02:06 PM
  3. Federated Databases, joins across databases etc
    By Benjamin Stewart in forum IBM DB2
    Replies: 2
    Last Post: August 1st, 03:05 PM
  4. Creating compressed databases
    By Rick Altman in forum FileMaker
    Replies: 0
    Last Post: July 18th, 11:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139