1

In a MySQL 5.6 database I found one user 'foo'@'10.0.0.2' with ALL PRIVILEGES.

I also discovered an user 'foo'@'10.0.0.3' granted only USAGE privileges. Does this second user definition make sense?

Thanks.

Edit: for the sake of clarity, the MySQL database is on a machine which is neither 10.0.0.2 nor 10.0.0.3.

Improve this question
4
  • 2
    'Make sense'. What do you mean? It could make sense, it could also make no sense, depends on the context. Care to elaborate a bit more?
    – Nelson
    Mar 24 '15 at 12:03
  • please add more details to the question, to make it easier to understand.
    – Ahmad Abuhasna
    Mar 24 '15 at 12:05
  • I'll try to explain myself better. As I can tell, these permissions allow the user foo to connect from 10.0.0.2 and administer the database. It also allows him to connect from 10.0.0.3 but do nothing on the database. In this case, I don't see what's the purpose of the 'foo'@'10.0.0.3' user apart from testing if he can connect to that MySQL instance.
    – dr_
    Mar 24 '15 at 12:08
  • @dr01 first check that user permission with "show full columns from yourdatabasename.tablename;"
    – Md Haidar Ali Khan
    Mar 24 '15 at 12:12
1 
USAGE = no privilege

https://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html#priv_usage

Now you have to options, either delete the user or grant all privileges to the user, which ever make sense to you

CASE 1: If you want to remove 'foo'@'10.0.0.3'

REVOKE USAGE on *.* from 'foo'@'10.0.0.3' ;

will never work as there is no grant called USAGE So use 

DROP USER 'foo'@'10.0.0.3';

CASE 2: If you want to grant all privileges 'foo'@'10.0.0.3'

GRANT ALL PRIVILEGES ON *.* TO 'foo'@'10.0.0.3';
FLUSH PRIVILEGES;
Improve this answer
0
0

Makes total sense, the privilege list is intended to include the IP/IP range and privileges for remote machines trying to connect to your database(s).

While trying to connect from your local machine you'll be making use of the localhost (127.0.0.1) permissions set, that should be at the top of the list.

For more details and GRANT syntax you could refer to the MySQL reference manual.

Improve this answer
3
  • Thanks @MySQLRockstar and ZQL for your answers. I am aware how grants work, but I was more looking for a possible real-world scenario for the configuration I posted. To me the second user definition looks like an error and was considering removing it from the database.
    – dr_
    Mar 26 '15 at 13:40
  • Mhmm, a real case scenario could look like the config I'm using to allow myself access to my personal databasesrv at home. User: zqlremote Host: % Password: Yes Global privileges: ALL PRIVILEGES Grant: Yes. With this I allow myself to connect from any place with my user and password and act as a full privilege user. Sure you can delete the second one and add the ones you think your going to use, you can white list your local subnet in order to allow other pcs to interact with your DBs.
    – Nelson
    Mar 26 '15 at 14:28
  • That's not a real case for the original scenario as you haven't set up a user that has only the USAGE privilege.
    – dr_
    Apr 7 '15 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.