On Fri, Sep 24, 2010 at 6:19 AM, Tom Barrett <tom@xxxxxxxxxxxxxxx> wrote: [snip] > I'm not actually that familiar with DB admin to that extent. I have either > app users with lock+crud on specific databases, or root. As a an aside, > would you know if there is a level of permissions for a user between app and > root that would be 'sensibly secure' (it will be MySQL 5)? It depends on the app, but phrases like 'sensibly secure' raise caution flags for me. I tend to go with the principle of least privilege. Where I currently work, the admin functions for a web application are usually on an intranet site that is completely separate from the public site. Because of this, I have a different database user for each site. In this case, these are database-only logins unrelated in any way to the actual machine account used by the web servers. On our newer development, nearly all table access is managed strictly through stored procedures (we use SQL Server, but the same would work for MySQL if you were so inclined), and each database user is only granted execute permission on the specific procedures necessary for that role. The only time we grant access directly to a table is in cases where we just can't get a procedure to do what we need efficiently or effectively. And, in those cases where I do need to grant access to a table, I grant permission to only the columns/operations necessary for that user. If I encountered a case where I needed to allow a user to make schema changes as you mentioned in your original post, I would create a totally separate account -- again with no more permission than necessary for its intended task. Depending on the needs of the application, I'd decide whether that account was used by the web server or via a script scheduled to execute at intervals as several others have suggested in this thread. I've not tried this, but you could probably write the logic needed to create the database objects into a stored procedure. Then, you might only need to grant permission to that procedure and not grant permission to CREATE/ALTER anything. That would pretty well guarantee that the only objects created are the ones you intended. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
