Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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:~#
FLUSH PRIVILEGESas user1 before checking on user2?localhost? Because it takes precedence. See dev.mysql.com/doc/refman/5.6/en/adding-users.html