List:Internals« Previous MessageNext Message »
From:Alexander Keremidarski Date:February 5 2002 9:49am
Subject:Re: Jailed MySQL for hosting customers
View as plain text  
Hi,
David you aksed question which I predict couple of years ago will become 
very common to MySQL community.
I hope there will be lot of comments about it.

David Phillips wrote:

>We are offering our hosting customers MySQL databases.  Ideally, I would
>like to offer everyone unlimited databases.  What I would like is an option
>that when connecting to a database as a user, the MySQL server would access
>the actual database in the directory of the username.  That way users could
>create and delete databases normally, and no names would conflict with other
>users.
>
With current MySQL versions it is almost madness. Not because of MySQL 
itself, but becasue of SQL nature.
You said "unlimited". Whatever you meant it raises some very important 
questions:

1) What about Disk Space control?
You need control over diskspace used by user DB. Diskquota is not an 
option because MySQL uses many temporary tables and files in many cases 
which can be sometimes much larger than your tables.
There is no such options like:
MAX_TABLES_PER_DB
MAX_TABLE_SIZE
MAX_ROWS_PER_TABLE
...and many others you will like to see
2) What about priorities?
You definitely need DBA acess with higher priority than user processes 
and maybe you need some User-Level support

3) What about preventing  user from seeing other users data?
Answer is not so easy like it seems to be.

Running mysqld for every user is not an option at all. For 3-5 users 
maybe it is possible, maybe you can run up to 8 mysqld, but regular 
hosting server can have hundreds of users.

So using 1 mysqld with several user DB means mysqld has access to all 
this DB. Currently this means you never want to give users File_priv and 
you lose functionality. - No LOAD DATA INFILE...., no SELECT INTO 
OUTFILE ...
The problem is that User with File_Priv can read any file on server 
readable by mysqld !!

Ok. Looks like chroot environment can solve this.
Not at all if you want to place DB in user homedir.

Setup I use for users DB is following:

MySQL datadir
/usr/local/mysql/var

homedir:
/home/username
User DB
/home/username/db

chown mysql /home/username/db
chmod 640 /home/username/db

symlink
ln -s /home/user/db /usr/local/var/username

Yes it is RedHat style - not important ofcousre

With this setup user has read only access to his DB dir, mysqld has rw 
and user DB is seen in mysql as database 'username' and because user is 
charged for homedir space DB is included there.

What will happen if I try to chroot mysql at /usr/local/mysql/ ?

4) Most important. Any user can render your server unusable and there is 
no way to prevent this!!! I.e. DoS attack is very easy even by simple 
mistake and nothing can be done about it.

Imagine following scenario:
* I'm trusted user.
* You are carefull admin
* You give me SELECT_Priv Only to mine DB
* You create DB for me checking my structure first
* You load data into it for me cheking again. (sizes etc.)
* DB is relatively small - 5 tables, 1000 rows each

Looks safe?
Ot at all.

How can you stop me doing followng:

select * from tbl1, tbl2, tbl3, tbl3, tbl4, tbl5;

and forget where clause.

Note that this can be simple mistake. I type it, press <ENTER>, say 
"OOPS!!!", try to cancel it ...
Meanwhile mysqld tries to pump 1 000 000 000 000 000 rows eating all 
resources on your server.
And what if such query is burried in some PHP and constructed at runtime?
"select $some_vars from $list_of_tables where $where_clause" and for 
some reason $where_clause becomes 1

So?
Conclusion: There is absolutely no way to secure mysqld against DoS from 
user who is not extremely carefull.
Note again - I am talking about small typo not even newbie or malicious 
user.

3) As you said Name Space conlficts can occur.
It is not about DB names only, but usernames too

Ok you can establish rule like: UserDB is called 'username_db' where 
username is login name, but why users must be forced to use such mysql 
users? Every user may want to have users: Admin, Webuser etc.

So as I said many time (I said, I said ... :)) current Privileges System 
MySQL uses is suitable for 'Simple Setups' - few database 1 or 2 DBA, 
few users

With 'Complex Setups' - many DB, different DBA for every DB, several 
Users per DB, Complex Privileges it would be better if Privileges can be 
somehow moved to DB.
I mean that in this case Privileges are DB Property, not 'Global' like 
mysql.* tables currently are.
I will repeat suggestion about making them 'visible' within every DB.
Like : SYSTEM_user, SYSTEM_tables_priv
With many considerations like "If user has File_Priv - it is 'chrooted' 
at some_dir like PHP open_basedir option

And this can be done easily (I hope) if there is VIEWS support in MySQL.

Sounds like total revising of current MySQL behaviour isn't it?

Monty, remember in other thread:
Alexander> To say it different mysql.* is Admin space not User space
Monty> In practice there isn't really a big difference.

Don't you think cases like this become more and more common?
MySQL used as Webserver back-end on Hosting server.


Thread
Jailed MySQL for hosting customersDavid Phillips5 Feb
  • Re: Jailed MySQL for hosting customersSasha Pachev5 Feb
  • Re: Jailed MySQL for hosting customersDavid Phillips5 Feb
    • Re: Jailed MySQL for hosting customersSasha Pachev5 Feb
  • Re: Jailed MySQL for hosting customersAlexander Keremidarski5 Feb
    • Re: Jailed MySQL for hosting customersMichael Widenius10 Feb
  • Re: Jailed MySQL for hosting customersDavid Phillips5 Feb
    • Re: Jailed MySQL for hosting customersPaul DuBois5 Feb
    • Re: Jailed MySQL for hosting customersMichael Widenius10 Feb
  • Re: Jailed MySQL for hosting customersJeremy Zawodny6 Feb
  • Re: Jailed MySQL for hosting customersDavid Phillips6 Feb
    • Re: Jailed MySQL for hosting customersJeremy Zawodny6 Feb
    • Re: Jailed MySQL for hosting customersMichael Widenius10 Feb