Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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).

share|improve this answer

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.

share|improve this answer
2  
I agree with this in principle but as you point out I think it would be quite difficult to implement practically. One possible solution to this would be to log actions performed by the application account and associate the action with the authenticated users identity. Of course since the logging is done by the application itself, if some user found a way to make the application perform arbitrary actions, they may not be logged but a strict permission set for the application account would minimise damage if that were the case. –  Crippledsmurf Feb 5 '10 at 10:13
    
Oh yeah, it's much more of a "blue-sky" sort of thing. –  mfinni Feb 5 '10 at 14:35

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.