MySQL create an Anonymous or limited access only account

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

ADVERTISEMENTS

[*] 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.

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source & DevOps topics via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallCentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNCentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
2 comments… add one
  • Zamshed Farhan Aug 5, 2009 @ 7:31

    Nice tutorial and very helpful for system admins.

  • G Prasanth Kumar Jun 10, 2013 @ 7:09

    I tried created a readonly user with the 3-commands as follows:

    CREATE USER ‘guest’@’192.168.%’ IDENTIFIED BY ‘guest’;
    GRANT SELECT ON *.* TO ‘guest’@’192.168.%’ IDENTIFIED BY ‘guest’;
    FLUSH PRIVILEGES;

    I logged into anther system in the network and I am able to connect and the see the database/s.

    Apart from these I am able to create new tables, update existing insert new records and also drop an existing table.

    I am unable to find where the problem is.

    Can somebody help me on this regard.

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.