1

When I create a user account with localhost and password as

create user user1@localhost identified by 'user1'

It is ok to grant. But when i create user using '%' and without password, it is not ok for grant.

create user user2@'%'

grant select on newdatabase.s to user2

and I have been checked the privileges of user2 using show grants for user2. It shows correctly. But when i use as user2, user2 can not access 'newdabase' Why?

  • Did you try FLUSH PRIVILEGES as user1 before checking on user2? – Derek Downey Nov 25 '15 at 14:35
  • Flush privs not required.... as doc reads "The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements." – mysql_user Nov 25 '15 at 15:00
  • Do you have an anonymous user on localhost? Because it takes precedence. See dev.mysql.com/doc/refman/5.6/en/adding-users.html – Richard Smith Nov 25 '15 at 15:59
0

are you sure you're doing what you want? In first query you specified localhost while in second you specified %... so for % you will have to connect to server remotely or using ip.

Check below:

root@server3:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3797
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user user1@localhost identified by 'user1';
Query OK, 0 rows affected (0.21 sec)

mysql>
[1]+  Stopped                 mysql
root@server3:~# mysql -uuser1 -puser1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3798
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
root@server3:~# fg
mysql
create user user2@'%'; grant select on test.* to user2;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> show grants for user2;
+-----------------------------------------+
| Grants for user2@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'%'       |
| GRANT SELECT ON `test`.* TO 'user2'@'%' |
+-----------------------------------------+
2 rows in set (0.03 sec)

mysql> \! ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0c:29:db:fe:fe
          inet addr:192.101.86.13  Bcast:192.101.86.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fedb:fefe/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:250027 errors:0 dropped:0 overruns:0 frame:0
          TX packets:259715 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:105217244 (105.2 MB)  TX bytes:29466344 (29.4 MB)
          Interrupt:19 Base address:0x2024

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:158798 errors:0 dropped:0 overruns:0 frame:0
          TX packets:158798 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:33510924 (33.5 MB)  TX bytes:33510924 (33.5 MB)

mysql>
[1]+  Stopped                 mysql
root@server3:~# mysql -uuser2 -p -h192.101.86.13
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3804
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
root@server3:~#
  • I am just trying what I read from the book. It said that wildcard (%) which means MySQL lets the user connect from any host. So I tried that but it does not wok. I am sorry, but I don't know how to try what you shown above and how to connect to server remotely or using IP. – Ju Ju Nov 26 '15 at 4:31
  • Do the ip config on your mysql server... and then connect to that ip... it should work (as shown in sample above) – mysql_user Nov 26 '15 at 4:47

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.