Is a simple MySql Database possible?

Question

Steps:

1. I want a registered user to be able to insert values into a table.
2. Those values would only be able to be seen or edited by the user. (a few rows)

I have a registration/login page and insert form page complete and they can add do their respective jobs.

Here's the problem and i realize it probably a super simple answer:

How do I link the registration/login username to the values that I'm entering so that only that username has access to it?

Thanks,

Michael

Answer 1

Assuming you have "user" like:

id    name   ...

You can make "value" like:

id   value  user_id

where user_id is the id of the user owning the value (could be FK-constrained or not). Then have your edit script check for user_id to see if the user can edit or not.

Edit: id is the primary key in both tables

Answer 2

You already have a login page so you have done almost all it is needed. I assume you have PHP and have a $_SESSION going on (since you say the login works). There must be a `$_SESSION['userid'] variable or equivalent then. The rest is just connecting that page to the data view.

Assuming this view is mydata.php, the queries there should be similar to:

$sql = sprintf("SELECT * FROM table WHERE user_id = %s", mysql_real_escape_string($_SESSION['userid']);

This is a very basic example. You might want to look further here:

http://articles.sitepoint.com/article/php-mysql-tutorial

You need to prevent non-owners viewing information by redirecting them via header("location: someotherpage.php")

Answer 3

You can create a MySQL user for each registered user and protect their data at the DB level. That's usually overkill for a web application.

What you probably want here is enforcing data owner at the data access layer. Associate the data to the user and restrict any data queries or updates to that user, i.e. any insert, update, select SQL statements would include the user id as a parameter.