Db access/privileges philosophy

[Federico Marziali]

Hello everybody,

I'm new in the database field / web application development and I'd like to post a question about "db access/privileges" philosophy, to collect some design feedback from more experienced people.

Basically the question is on how it is most secure to manage access to the database for a web application.

I have different roles in mind for the database access, which you can think as categories: some users can read only, some others can write to some tables only, others can write in all tables, others can also delete records, etc...

As a start I thought about managing all this via PHP only, i.e. by displaying the interface differently depending on the category of user that is logged on, so limiting the way the access to the database via the UI only. The connection to the database would always take place as "root".
Is this a common practice? Or should it be avoided?

Shall I create roles in the database corresponding to the users "categories" I mentioned above or is it considered an overkill with no real additional security benefits?
The point is - if I understood MySQL manual correctly - that the privileges cannot be "limited" to some set of tables only, so I'd still need to manage everything via the web interface, distinguish between the users' categories...

Any thoughts or suggestions greatly appreciated.

Fede