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? 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). 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. Any comments? TIA Brent --------------------------------------------------------------------- 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
