How do I set up user accounts?

Restricting access not to areas but to individual accounts

[LemonFizz]

Hi,
I am building a Lab Information Management system. I am trying to give access to some of the data in my MySQL DB to users.
I would like for a user to be able to log in and view any of the data that pertains to his/her projects only. This needs to be pretty secure as we have some external customer information which is sensitive. (but not top secret).
The specific question I have is, how do I build such limited access? Do I use the user_id (primary key in the user table) stored as a variable and use session management to move from page to page? How secure is that? Would it be better to use secure cookies? Either way I take it that the user_id is stored as a variable and used in the sql query to restrict the dataset? Is this a secure way of doing things or could some unscrupulous hacker come along and steal my data?
Many thanks,
LF

[andylarks]

Hi Lemon

Being as this is the php forum, I assume that you are wanting to write something in php to be accessing you mysql database.

If that's the case, then you can use sessions. I have a similar set-up, with a permissions table. I get the user to log in, storing the result in a session variable. I then do a look-up on the permissions table to see what pages this user has permission to view, and present them with the options.

You can extend it so that each page checks the permission table to see whether the user that is trying to view it has permission to do so. This stops people going directly to a page.

Hope that helps a little. If you want more info, sticky-mail me, and I'll send you some more details.

Andy

[ergophobe]

Do I use the user_id (primary key in the user table) stored as a variable and use session management to move from page to page?

Yes. If it's a matter of privileges, you can assign user levels to each user and to each page. If it's a matter of particular projects, you'll probably want a lookup table that correlates individual users with specific projects.

How secure is that?

Not very. Accd to the manual [us2.php.net]

The session module cannot guarantee that the information you store in a session is only viewed by the user who created the session. You need to take additional measures to actively protect the integrity of the session, depending on the value associated with it.

However, there are things you can do [phpbuilder.com] and things that it might help to know

[webkreator.com...]

[forums.devshed.com...]

Would it be better to use secure cookies?

I'm don't know anything about secure cookies [list.gmu.edu] except what I just read.

[LemonFizz]

Hi,
Thanks for the replies. That is a lot of information to process. I guess I'll be back in a couple of weeks!
Cheers,
LF

[LemonFizz]

If it's a matter of particular projects, you'll probably want a lookup table that correlates individual users with specific projects.

Do you know a tutorial or any resources for getting info on this....this is exactly what I am trying to do.
Thanks,
LF

[LemonFizz]

One more thing, just so I know we are all talking about the same thing. I am not talking about members accessing static pages, but dynamic one which are pulled from the MySQL DB. PHP is generating content delivered from the MySQL DB and the SQL queries are complex join statements pulling data from many related tables and displaying it in dynamic html tables.
In this case does one still use a permissions table in MySQL or are permissions just part of the SQL queries?

[ergophobe]

Do you know a tutorial or any resources for getting info

Just create a table for you users with all the information you want to track about them. Then create a table for projects with all the info you want about them.

Then create a table that has two columns, namely

user_id
project_id

neither will be unique (it will be a many to many relationship), but each pair should be unique, so the *pair* is your primary key. Then you just need the right join conditions, e.g.

WHERE users.user_id = users2projects.user_id AND projects.project_id = users2projects.project_id AND....