Posted by Gordon Burditt on 04/16/06 17:55

>> MySQL has good permissions, you could connect to the db as a different
>> user and with only a limited set of permissions.
>
>I like that idea. Do you have suggestions of what would constitute a
>minimal set of permissions that would still enable outsiders to make
>queries that I can think of?

For read-only access to tables, a user needs SELECT (probably on
one database only) and possibly CREATE TEMPORARY TABLES (which is
sometimes needed implicitly for ORDER BY). This presumes that
you supply the tables and the data, created by an account that has
more privileges. This doesn't prevent running your database out
of disk space with temporary tables.

If you want to allow the user to alter data, but not the tables,
SELECT, INSERT, UPDATE, and DELETE privilege on one database, along
with CREATE TEMPORARY TABLES is probably sufficient. This does allow
them to wipe out any sample data and run your database out of disk
space.

This does not prevent hammering the db with queries (there are some
rate-limiting features for that) or loading down the server with
joins that create huge numbers of rows in the result.

>> What about functions?
>>
>> select LOAD_FILE('/etc/passwd');'

This requires FILE privilege to read files on the server.
This is a privilege you shouldn't hand out lightly.

Gordon L. Burditt

[Back to original message]