> -----Original Message-----
> From: BD [mailto:[EMAIL PROTECTED]]
> Sent: 18 February 2002 21:31
> To: [EMAIL PROTECTED]
> Subject: Security concerns on webserver with PHP & InnoDB
>
>
> I'm creating a web application with MySQL, PHP, InnoDB and I need to know
> whether I should split the one large table into 3 different tables with
> different user privileges defined on each table.
>
> As it stands now, the user logs in by entering a username & pw
> and this is
> checked against a membership table. If it is found, the PHP script grants
> him access to certain PHP forms that allows him to insert rows to the
> NewTable, browse the PublicTable, and delete/insert/update rows from the
> PrivateTable. The actual MySQL username/pw for the database is
> stored in an
> include file which the PHP script loads when generating the page. So
> currently everyone is using the same MySQL username/pw and the PHP script
> controls access to the various forms depending on the security level the
> person has in the membership table.
>
> The data I'm concerned about is stored in 1 large table (up to 1 million
> rows) and there is a rcd_type field to indicate whether the record is
> Public, Private, New. I currently only have 1 MySQL web related user
> defined and it has Select, Insert, Update, Delete privileges to
> this table.
> The PHP script creates an SQL statement with the rcd_type field set to
> filter the records so he can only see, insert, update records of
> that type.
> Access to the tables are controlled through the PHP scripts.
>
> Here's are the questions.
> 1) Security Question
> Should I split up the large table into 3 tables and assign these
> privileges
> to them: PublicTable (ReadOnly), PrivateTable (read/write/delete) and
> NewTable (Insert). I'm concerned that even though the single username/pw
> I'm using now is hidden from the user and the user has no way to
> update the
> SQL statements that accesses the tables, is it really necessary
> to further
> restrict access to prevent the user somehow updating or deleting rows in
> the Public table? In other words, has anyone had their PHP website
> compromised by someone finding a backdoor into the database by
> circumventing their PHP scripts?
Be afraid, be very afraid.........
Even if you are not paranoid they probably ARE out to get you :)
Seriously, depends to some degree how much risk you want to/can afford to
carry. You need to balance this against the extra work.
Users are often the weakest link. Odds on some user will use 'john' and
'john' as username and password, or something similar. If John has delete
privilages, and some script kiddie feels like trying their luck, then
disaster could be close.
If you want to be secure, I would suggest the following :-
1. MySql access only by localhost
2. If you are on shared hosting make sure php is in safe mode (I think that
is the correct term, I am not an expert). If it is not, other php users on
the host can view your config files.
3. Pass only a (complex) sessionID as identity and relate this to userID
using php/mysql internally. If userid (the value the system uses to identify
the user ) is passed then it may be spoofable.
4. Don't give anybody else an account on the server itself, this is the most
likely source of a backdoor attack.
5. User login by SSL connection - avoids sniffers picking up usernames and
passwords
6. Generate secure passwords for the users, rather than letting them set
their own
7. Lock users out after 3 failed loggins
8. Put a short time out on the session
9. Verify that the user is allowed to execute the sql that they are
executing before you execute it
10. Screen user input for illegal characters. php4 backslashes
automatically, though this can be disabled, php3 does not. This could allow
the user to add a ';' and then another sql statement on their text.
11. Don't echo errors - it can reveal table and field names which helps a
hacker.
12. Use unusual table and field names 'Users' and 'username' and 'password'
are guessable table and field names, 'X_1_users' is harder.
13. Encrypt passwords stored in the database - if you do have a break in it
limits damage, otherwise all password would have to be reset.
You may have already done all this, but thought i'd list them anyway.
>
> 2) Speed Question
> Now if I were using MyISAM tables then I'd be forced to split it
> up into 3
> tables because the table locks would hamper the read requests of
> the Public
> rows. But I'm using InnoDb so is there any speed advantage in producing 3
> different tables? Using 3 tables will of course mean more work
> because I'll
> need to manipulate the PHP code to generate the different login
> username/pw
> and also alter the table name in the sql statement for the 3 tables based
> on the type of user. (The 3 tables will of course have the same structure
> and I can probably get away with reusing the same PHP Update form for the
> Private and New tables).
>
Would this work? the user logs in, use their user group to determine the
connection string - different database, same tables, so you have a read
only database, and insert database and an update database, that way you can
use the same scripting for either of the 3 databases, or maybe just a
different user for the same db.
> So splitting the one large table into 3 tables *may* provide some
> additional security, but am I being overly paranoid? Do I really need the
> additional MySQL passwords when the security is currently handled by the
> PHP scripts. I don't want to make more work for myself unless it improves
> security or speed.
If you are sure your php security is watertight, then different db users may
be overkill. On the other hand if you are sure you will be a hacker target
then do everything possible.
>
> Any comments? TIA
>
> Brent
>
hth
Peter
-----------------------------------------------
Excellence in internet and open source software
-----------------------------------------------
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
-----------------------------------------------
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
