Best Practices For Managing application interaction with the database server

Question

This is my first question on ServerFault and I am more familiar with software development than administration so I'm not sure if this is a valid question or not.

I am creating a web application as a learning excercise and I am uncertain as to whether I am taking the correct approach with regards to security. My current plan is to create a user account for the application in the database and remove all permissions except those necessary to execute a set of stored procedures which act on behalf of the web application.

1. Is this a recommended approach
2. Are there any considerations that I should be aware of?

For reference the application will be written in PHP and the database used is MySQL

Answer 1

Generally speaking it is best to authenticate/authorize through the interface and then allow the interface to interact with the repository using an application account. Give the application account(s) the minimum amount of access that is necessary for the application to function to limit the impact of a compromised account.

I would also recommend avoiding the use of plain text credentials in the source code or in configuration files. Consider encrypting the credentials or quite possibly having the application run under a single account such that all database requests are done under the context of that account. That way the server admin is responsible for maintaining the credentials, not the developer.

Finally, I would suggest that you add the application account to a role in the database and grant that role the necessary permissions you need. This makes it easier to deal with changes to the account in the future (i.e. add/remove from role rather than granting/denying 100 individual permissions).

Answer 2

yes this a recommended approach. one thing is also possible, when your application is getting more complex and you have different parts which have to do different work on your database you could create more accounts, e.g. one with rw access and one with only read access.

but be sure to not make it more complex as needed. this will revert all security considerations, because at some point in time you don't know which user to use what for and every user will get more permissions than he needs.

Answer 3

Yup you've got it right. Even if users log into the system, it's probably best for that login just to be an authentication to use the program while the database access is handled by a single user.

The downside to this is that when you go to check your SQL logs if something bad happened, all changes will have been made by the same user making it hard to narrow down a source. Good application design should be able to prevent this for the most part but just keep it in mind.

Answer 4

A better practice, but kind-of "in an ideal world", would be to have the same IDs in the database as you have in the application, and the DB user accounts only have the permissions within the DB that match their permissions within the app. Then you can get around the situation that Dynamo rightly pointed out - you can see within the DB the same users that are in the application.

I don't know how realistic that is to implement (I certainly haven't seen it done that way), but it's better than a single application user that has all rights to the application DB - which itself is much better than using the 'sa' account or similar for the app's connection.