How to create an Anonymous or limited access only account on MySQL

187
0

Sometime it is necessary to create an anonymous or limited access only account. This allows anonymous user to use MySQL server. I received following criteria

[*] Create a user called anonymous
[*] Set up read and write permission to account
[*] anonymous cannot set or update password (remember if one user changes the password, no other anonymous login can be accepted again).
[*] Grant anonymous access to table called xyz

Please note that this anonymous user is for internal WAN/Lan user and not for Internet users.

However, I have noticed default anonymous mysql account.
$ mysql -u anonymous

MySQL allows to login in anonymous user (or any user) from localhost. However this user is not allowed to use any / critical database such as mysql or set password:

mysql -u anonymous

Output

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2630835 to server version: 4.1.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Try to use mysql database:

mysql> use mysql;

Output

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

Try to setup password:

mysql> SET PASSWORD FOR anonymous@localhost=PASSWORD('secrete');

Output:

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

So all I have to do is set permission to table called xyz with GRANT SQL command.

mysql -u root -p

Now grant permission to xyz table:

mysql> use dbnane;
mysql> GRANT SELECT ON xyz TO ''@localhost

Now any anonymous user can connect to mysql database server and query xyz table from localhost.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.