Restrict user privileges to single row in table

[RLJ]

Hi, have a few questions to do with adding users/setting privileges etc. on MySQL. Also, I'm new to this stuff so bear with me!

 

What I'm wanting to do for people to be able to create a user account on my website, fill in their profile information and then update/edit this information later.

 

I was thinking of creating a new database user for each account and setting privileges so that each user would only have write access to a particular row in a table, where their information would be stored.

 

1) Am I even on the right track?

2) Is it possible to set row by row user privileges like this? If so, how?

3) Do I need my PHP script to connect to the database as a 'root' user to add new users? Or is it possible & safer to create a new user with restricted privileges with which the PHP script then connects to the database when people want to create a new account?

4) Are cookies neccesary?

5) Anything else important I need to consider?

 

Much appreciated if you could help me out with any of these questions, thanks!

 

 

[requinix]

1) No. The user should have absolutely no direct access to your database. See the response below.

2) No.

3) Very no, and yes.

4) Different subject. Technically speaking, no, but it really helps to use them.

5) Undoubtedly.

 

Your PHP will connect as a simple (and restricted) user, with simple (and restrictive) privileges on the database. SELECT, INSERT, and UPDATE and fundamental; DELETE might be necessary. You should not need anything beyond that.

Then write your code so that only the proper stuff gets affected. It's not like you should (or even can) tell MySQL to update everything in the table and then expect it to only update the rows you had permission to update. You tell it specifically which rows to update.

[fenway]

If you want row-based access restrictions, youl'll need a VIEW.

[RLJ]

Thanks for your help so far.

 

To clarify, I wasn't thinking of giving my users direct access to the database, I was thinking of having my PHP script connect with a different (restricted) username each time, depending on which actual user was logged in to my website at the time. So someone registers on my website with username 'barrywhite1', the PHP script connects to the database as some generic restricted user and creates a new (restricted) database user 'barrywhite1' with access only to the rows/columns relating to this user. Then when 'barrywhite1' logs in to my website and wants to change his profile information, the PHP script connects to the database with username 'barrywhite1'. The reason I thought I would do this is because I thought:

 

A) it would add an extra layer of security, so that not only does my PHP script alter only the rows/columns relating to that particular user (client side), but it is in fact not even possible for that user to alter any other rows/columns in the database (server side).

 

B) it seemed like a convenient way to store user passwords

 

I know user privileges can be restricted by column (but perhaps not by row then?) so is the above a good way of doing things?

 

Thanks.

 

 

[fenway]

Don't generate users or view on-the-fly -- that's not a good approach.