Professional Web Applications Themes

Strange MySQL permissions problem - MySQL

I'm trying to setup a MySQL database (foo) and user (bar) that will be used to store the data for a PHPBB website. For security reasons, the webserver and database will be on separate machines (ideally, the MySQL server will host other databases in the future). I want to restrict the user "bar" to only have access to database "foo". I've setup the new user using the following command: grant select,insert,update,delete,create,drop on foo.* to 'bar''webserver' identified by 'secretpassword'; When I log in as user "bar" from server "webserver" and run a "show grants;", I get the following: GRANT USAGE ON ...

  1. #1

    Default Strange MySQL permissions problem

    I'm trying to setup a MySQL database (foo) and user (bar) that will be
    used to store the data for a PHPBB website. For security reasons, the
    webserver and database will be on separate machines (ideally, the MySQL
    server will host other databases in the future). I want to restrict the
    user "bar" to only have access to database "foo".

    I've setup the new user using the following command:

    grant select,insert,update,delete,create,drop on foo.* to
    'bar''webserver' identified by 'secretpassword';

    When I log in as user "bar" from server "webserver" and run a "show
    grants;", I get the following:

    GRANT USAGE ON *.* TO 'bar''webserver' IDENTIFIED BY PASSWORD '12345'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `bar`.* TO
    'bar''webserver'

    The "GRANT USAGE ON *.*" appears to give me complete access to other
    databases. For example, I can switch to another database "test" and
    successfully run select.

    I assume I'm doing something stupid - please can someone advise? I've
    tried running a revoke on the usage, but this doesn't work. How do I
    stop user bar from accessing the other databases?

    I'm running MySQL Ver 14.7 Distrib 4.1.7, for redhat-linux-gnu (i386) on
    Red Hat Enterprise Linux 4.0

    Thanks in advance.

    JR
    JR Guest

  2. #2

    Default Re: Strange MySQL permissions problem

    JR wrote:
    > I'm trying to setup a MySQL database (foo) and user (bar) that will be
    > used to store the data for a PHPBB website. For security reasons, the
    > webserver and database will be on separate machines (ideally, the MySQL
    > server will host other databases in the future). I want to restrict the
    > user "bar" to only have access to database "foo".
    >
    > I've setup the new user using the following command:
    >
    > grant select,insert,update,delete,create,drop on foo.* to
    > 'bar''webserver' identified by 'secretpassword';
    >
    > When I log in as user "bar" from server "webserver" and run a "show
    > grants;", I get the following:
    >
    > GRANT USAGE ON *.* TO 'bar''webserver' IDENTIFIED BY PASSWORD '12345'
    > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `bar`.* TO
    > 'bar''webserver'
    >
    > The "GRANT USAGE ON *.*" appears to give me complete access to other
    > databases. For example, I can switch to another database "test" and
    > successfully run select.
    >
    > I assume I'm doing something stupid - please can someone advise? I've
    > tried running a revoke on the usage, but this doesn't work. How do I
    > stop user bar from accessing the other databases?
    >
    > I'm running MySQL Ver 14.7 Distrib 4.1.7, for redhat-linux-gnu (i386) on
    > Red Hat Enterprise Linux 4.0
    >
    > Thanks in advance.
    >
    > JR
    The behavior you are witnessing is due to the default privileges that apply to the test
    database and any database with a name starting with "test_%".
    To fix the problem, you can use an utility provided with MySQL default installation, called
    mysql_secure_installation

    Or you can manually issue these commands, as root

    use mysql;
    set password=password('put_your_password_for_root_here ')
    delete from user where password='';
    delete from db where db like 'test%';
    flush privileges;

    After that, your user 'bar' will only access the database it was assigned.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Strange MySQL permissions problem

    Giuseppe Maxia wrote:
    > JR wrote:
    >> I'm trying to setup a MySQL database (foo) and user (bar) that will be
    >> used to store the data for a PHPBB website. For security reasons, the
    >> webserver and database will be on separate machines (ideally, the MySQL
    >> server will host other databases in the future). I want to restrict the
    >> user "bar" to only have access to database "foo".
    >>
    >> I've setup the new user using the following command:
    >>
    >> grant select,insert,update,delete,create,drop on foo.* to
    >> 'bar''webserver' identified by 'secretpassword';
    >>
    >> When I log in as user "bar" from server "webserver" and run a "show
    >> grants;", I get the following:
    >>
    >> GRANT USAGE ON *.* TO 'bar''webserver' IDENTIFIED BY PASSWORD '12345'
    >> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `bar`.* TO
    >> 'bar''webserver'
    >>
    >> The "GRANT USAGE ON *.*" appears to give me complete access to other
    >> databases. For example, I can switch to another database "test" and
    >> successfully run select.
    >>
    >> I assume I'm doing something stupid - please can someone advise? I've
    >> tried running a revoke on the usage, but this doesn't work. How do I
    >> stop user bar from accessing the other databases?
    >>
    >> I'm running MySQL Ver 14.7 Distrib 4.1.7, for redhat-linux-gnu (i386) on
    >> Red Hat Enterprise Linux 4.0
    >>
    >> Thanks in advance.
    >>
    >> JR
    >
    > The behavior you are witnessing is due to the default privileges that apply to the test
    > database and any database with a name starting with "test_%".
    > To fix the problem, you can use an utility provided with MySQL default installation, called
    > mysql_secure_installation
    >
    > Or you can manually issue these commands, as root
    >
    > use mysql;
    > set password=password('put_your_password_for_root_here ')
    > delete from user where password='';
    > delete from db where db like 'test%';
    > flush privileges;
    >
    > After that, your user 'bar' will only access the database it was assigned.
    >
    > ciao
    > gmax
    >
    Giuseppe - many thanks for your quick response. I have just run the
    mysql_secure_installation script and it is now working perfectly.

    JR
    JR Guest

Similar Threads

  1. Strange CFC errors with MySQL ... HELP!
    By steve@averum.com in forum Macromedia ColdFusion
    Replies: 5
    Last Post: May 15th, 06:51 PM
  2. Very Strange MySQL Problem
    By Google Mike in forum PHP Development
    Replies: 4
    Last Post: November 11th, 04:29 PM
  3. Strange Permissions problem
    By Steven Cohn in forum ASP.NET Security
    Replies: 1
    Last Post: October 2nd, 02:17 PM
  4. strange way to install mysql ? !
    By Jake in forum PHP Development
    Replies: 1
    Last Post: July 28th, 10:38 AM

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