Hack 97. Allow an Anonymous Account

You can allow anonymous users into your SQL database if you take some precautions first.

Before you allow the public into your server, you need to set up an anonymous account to read and write with the permissions that you are prepared to allow. In addition, you need to make sure that the anonymous user cannot change the password. If one user changes the password, the next anonymous user will not be able to get into the system.

You should accept that it will be very difficult to defend yourself against a conscious denial-of-service attack, but you can take steps to defend your server against incompetent users who unwittingly set off expensive queries [Hack #98].

You can allow access from a web-based interface [Hack #100], or you can allow users to connect to your server using clients running on their own machines. There are two problems associated with the client approach: first, you must open the appropriate ports in your firewall; and second, your users must obtain and install the relevant client.

12.2.1. A Limited MySQL Account

By default, MySQL allows anonymous access for users on the same host. MySQL will allow the user anonymous (or indeed, any unused account name) to connect to the database, provided they are coming from the localhost host. Unless you specify otherwise, such users are allowed only to use the database test. Here's how to grant access [Hack #94]:

GRANT SELECT ON tableName TO ''@localhost

The MySQL permissions system can seem confusing. If someone logs on to MySQL as hawkeye when no such account exists, they appear to be user hawkeye@localhost but they have the permissions of ''@localhost:

andrew@SQLZoo3:~> mysql -u hawkeye
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 33315 to server version: 5.0.18-standard

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
mysql> SELECT CURRENT_USER;
+--------------+
| CURRENT_USER |
+--------------+
| @localhost |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT user( );
+-------------------+
| user( ) |
+-------------------+
| hawkeye@localhost |
+-------------------+
1 row in set (0.00 sec)

It is important to note that it is impossible for the anonymous user to change the password:

mysql> SET PASSWORD FOR hawkeye@localhost=PASSWORD('tiger');
ERROR 1133: Can't find any matching row in the user table
mysql> SET PASSWORD FOR ''@localhost=PASSWORD('tiger');
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'

 

12.2.2. A Limited SQL Server Account

SQL Server allows two mechanisms for authentication: Windows authentication and SQL Server authentication. If you use the Windows authentication method, you check and set passwords outside the SQL Server system.

You need to set up an operating system account for this purpose, and you should ensure that this account has only limited privileges on your filesystem because it is possible to set off operating system commands from with the SQL Server language. The stored procedure, xp_cmdshell, allows SQL users to execute operating system commands, but the command is blocked by default.

If the xp_cmdshell stored procedure is blocked and your anonymous users are logged in to SQL Server using Windows authentication, there is no way for the anonymous user to set or change the password.

12.2.3. A Limited PostgreSQL Account

In PostgreSQL, there are several mechanisms for authenticating users. If you use Kerberos authentication or Ident-based authentication (as described in Section 6.2 of the manual located at http://www.postgresql.org/docs/7.3/interactive/auth-methods.html), the user's password may not be changed. As with SQL Server, you will need to set up an operating system account in such a way that the user cannot change that password.

12.2.4. A Limited Oracle Account

Oracle has plenty of options aimed at forcing users to change their passwords at regular intervals, but it does not have a command to prevent password changes. You can use a sneaky trick if you want to prevent a password change altogether. PASSWORD_VERIFY_FUNCTION allows system administrators to veto weak passwordstypically is it used to prevent short passwords. You can implement a password policy that is so strict that no password will ever be good enough. This neatly prevents our anonymous user from changing the password:

CREATE OR REPLACE FUNCTION no_pw_good_enough 
(
userid_parameter VARCHAR2,
password_parameter VARCHAR2,
old_password_parameter VARCHAR2
)
RETURN boolean IS
BEGIN 
 RETURN FALSE;
END;
/

With a function that always rejects the new password, you can force it on the anonymous account to ensure that no password change is possible:

ALTER PROFILE anonProfile LIMIT
PASSWORD_VERIFY_FUNCTION no_pw_good_enough;

ALTER USER anonymous PROFILE anonProfile