Mysql – Newly created user has rights on MySQL database

MySQLpermissions

I use the root to create a new user, like this:
enter image description here

I have no more operations on it. Then I use this user to log in:
enter image description here

I try to do some operations like this:
enter image description here

Here "test" is a database which I created before. Why can I do all the operations? I have not done any grant operation on the test user. When I come back to use the root user to log in, I also cannot revoke the privileges on test(database) for test(user), like this:
enter image description here

I am really confused by these things, and this is my first time to ask question on StackExchange. My English is also not very good. If you cannot understand what I am talking about just tell me, I will reply soon.

Best Answer

If I am not mistaken, the test Database is created by default.

In addition, the mysql.db table contains rows that permit all accounts to access the test database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for testing but inadvisable on production servers. Administrators who want database access restricted only to accounts that have permissions granted explicitly for that purpose should remove these mysql.db table rows.

http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

Try to create a new DB with a different name and repeat your steps.

Hope this will help you.

Related Solutions

MySQL Mistake with GRANT OPTION

There is no "mass revoke" statement so you best option to change this is to update the users table:

UPDATE mysql.user SET Grant_priv = 'N' WHERE user != 'root';

Not that occasionally, there are users that should have grant privileges (and should have that) which are not named root. On my system, for example, I have debian-sys-maint (which I actually wonder if they should have grant privileges, but that's how it's installed).

Do not forget to reload the privileges after changing the user table:

FLUSH PRIVILEGES;

You can find more information on http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html.

Mysql – revoking thesql table level privileges

Use this script to help you create the grant script syntax : Run a root od mysql admin user.

  select 'grant all privileges on ', table_name, 'to 
someuser@somehost;' from information_schema.tables where table_schema = 'database name' and 
table_name not in ('table without access', 'table without access');

This will create an output that you can run after.

Just to replicate the error :

mysql> grant select on BASE_BIB.* to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use BASE_BIB;
Database changed
mysql> show tables ;

Now revoke select from a table form inside the BASE_BIB database:

mysql> revoke select on BASE_BIB.users from test123@'%';
ERROR 1147 (42000): There is no such grant defined for user 'test123'

on host '%' on table 'users'

No the right way to do it :

mysql> grant select on BASE_BIB.users to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke select on BASE_BIB.users from test123@'%';
Query OK, 0 rows affected (0.00 sec)

No error now !!

Managing access in mysql can be quite dificult !!

Once you gave him database.* you cannot revoke access for an object that is in that class. MySQL doesn't expand the Hotels.* wildcard to the individual tables The permissions tables store the granted permissions. Therefore, since you didn't actually grant anything on Hotels.AllHotels , there's nothing for MySQL to revoke. In this case you need to do it granular form the start !

Remove all privileges on database, table, column levels, etccc.

Grant privileges to EACH table, except 'you choose'.
Grant privilege to specified fields in table 'you choose'.

Best Answer

Related Solutions

MySQL Mistake with GRANT OPTION

Mysql – revoking thesql table level privileges

Related Question