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

[*] 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 Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 2 comments so far... add one


CategoryList of Unix and Linux commands
Disk space analyzersdf duf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Modern utilitiesbat exa
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg glances gtop jobs killall kill pidof pstree pwdx time vtop
Searchingag grep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
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.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum