If your attack model allows "attackers to upload their own scripts", then they have access to everything those same script can see or do, and "secure access to database", for this value of secure, is simply not possible.
(Short of redesigning the database so that it can run a check on all PHP files involved in a given connection - while theoretically possible, it would be exceedingly difficult - just think what it means to identify the PHP scripts, included by a Web process, that opened a connection, of which the DB server only knows the source address and port! - and very likely a performance killer)
But at this point you can move some logic away from the webserver, taken as unreliable, and see what security can be found elsewhere.
For example, you can make it so the different web application access levels use different credentials - you might have three database users "guest", "user" and "admin". You can organize things so that, for example, user administration by the user admin is not done on the same web server (you can separate it by process user, on a different webroot, another VM, or a physically separated web server).
Most functions and queries you can replicate using high-privileged procedures and functions that can access underlying data to which the web users have no access: for example you can do login checks with a function:
DELIMITER //
CREATE FUNCTION is_valid_user(user varchar(32), pass varchar(32))
returns BOOLEAN
DETERMINISTIC SQL SECURITY DEFINER
BEGIN
DECLARE ret BOOLEAN;
SELECT 1 = COUNT(*) INTO ret FROM users WHERE user = username AND MD5(pass) = password;
RETURN ret;
END//
DELIMITER ;
The Web application as user web has EXECUTE
privilege, but has no SELECT/INSERT/DELETE
privileges on the users
table. So it can't enumerate users, or reset all the passwords in one fell swoop,
It is even possible (but not very useful, since ability to upload executable scripts makes an almost ideal man-in-the-middle scenario) to use the guest account to retrieve the web access credentials, stored in the users
table, provided that the Web user has the correct password:
- user John logs in with password 'Doe'. Web application is running as guest
- the function
get_credentials('John','Doe')
returns 'web:myfirstpass'
- Web application switches credentials to web, and has now access to more features
All data logging is INSERT
only (only the admin can delete log records), and access to important/"financial" data (e.g. an invoice
table) can be done by running all ops on a temporary table owned by web, while a privileged procedure can be used to finalize and validate the table, and copy it into invoices
.
With a bit more work, the user can be associated with a highly random 'security identifier' string that is unique to him. Only by supplying that security identifier can he access procedures that retrieve invoices; and those procedures will only select invoices with that same identifier.
This way - a very labor-intensive way, admittedly - an attacker can only access the data of those users that log in, and are tricked out of their credentials, on the compromised web server.
You're left with the problem of quickly reacting to such a compromise.
On a Linux server with inotify
, it would be possible to continuously monitor files and remove those that are not "approved" (i.e. their SHA1 hash is not in the "allowed" list). Any modification or addition to monitored files would trigger their removal as well as an alert being sent to the administrator:
inotify: '/srv/www/padma/htdocs/ CREATE dfqjkw.php'
inotify: '/srv/www/padma/htdocs/ MODIFY dfqjkw.php'
inotify: '/srv/www/padma/htdocs/ CLOSE_WRITE,CLOSE dfqjkw.php'
ANOMALY: '/srv/www/padma/htdocs/dfqjkw.php' (SHA: e0a6bf9020320dca178cf115da4fa26de0278a25cf41702d667988877cdbc2d1) is neither known nor in testing
inotify: '/srv/www/padma/htdocs/ DELETE dfqjkw.php'
ACTIONS: '/srv/www/padma/htdocs/dfqjkw.php' targzipped, sent to root, and removed
This would also require a pretty bit of tweaking for people uploading their web sites - you'd need a "test" FTP web site where the upload takes place, and an "approval" procedure by which each file's signature is extracted and the web site is copied onto the "production" filesystem, effectively doubling data usage.
On the other hand, stealing FTP credentials as many trojan malware does would be no longer useful: lacking the approval procedure, the compromised files would only be present on http://test.www.mysite.com, which would only be accessible from a few selected networks, not on http://www.mysite.com which is allowed to all.