Edit &amp; access rights

Edit & access rights

MBK

I have created a script that allows information to be uploaded to a mysql database.

Now I am trying to work out how I can issue a user name and password to the user, so they can change their data, and no unrestricted users can change their info.

Does anyone have any sample code, or know where I can find some or even better, explain it??

Thanks 😕

mrmufin

This is kind of a tall request for this forum, but what you require is such a fundamental component of so many websites that I'll touch on the basics.

First, you're right in that each user must have a user name and a password to update their data. Which also implies that each 'data record' must be associated with a user. I suspect it would also be preferable that you have a superuser or administrator who can do anything to data records owned by anyone...

There are a variety of 'pre-fabbed' scripts out there which perform the tasks that you require, however, rather than pointing you to www.hotscripts.com or some other URL, I suggest you 'roll your own' solution. (That'll just give you such a warm, fuzzy feeling of pride and accomplishment when you're finished.) But if you're on a tight timeframe, that might not be practical. I'll provide some more information if you'd like...

MBK

Please,

All help is needed and appreciated!

mrmufin

The method that I use to accomplish this is pretty complex, (but has some really cool functionality and is pretty tough to break) and it requires creating four tables: session, user, groups, and user_groups. Before I start getting into the guts of all this let me say that i do not use native PHP sessions; I rolled my own session manager before PHP had built-in session support, and I've used my session manager (in one incarnation or another) ever since.

The logical place to start with any database-intensive application is with the database structure. So here goes...

My session table looks like this:

DROP TABLE IF EXISTS session;
CREATE TABLE session (
session_id char(32) NOT NULL default '',
host char(16) NOT NULL default '',
cookie enum('0','1') NOT NULL default '0',
user_id int(8) NOT NULL default '0',
updated int(12) unsigned NOT NULL default '0',
browser char(128) NOT NULL default '',
created int(12) NOT NULL default '0',
PRIMARY KEY (session_id),
KEY session_matrix (updated)
) TYPE=MyISAM;

The session_id is a random string from 8-32 characters and is always set PRIOR to 'log in'. I have a nifty function for generating the random string; perhaps we'll get to that later. Without getting to the dirty details of how I manage sessions, let's just say... I try to set a cookie, but since not every one has cookies enabled on their web browser, a session identifier may also be passed as hidden form field data, or URL-appended. (Which is why there's a cookie field in my session table...) The 'host' field corresponds to the $SERVER['REMOTE_ADDR'] global variable (client IP address), and I always check that and the browser ($SERVER['HTTP_USER_AGENT'], or web browser identification string) in addition to the session_id to help prevent people trying to 'hijack' sessions. Remember, the session_id must be passed with every request from client browser to the server. The user_id column represents the users unique integer-based identifier. I suppose you could use the user_name column, but I elect to use integer values since they can be compared more quickly...

My user table looks like this:
DROP TABLE IF EXISTS user;
CREATE TABLE user (
user_id int(8) unsigned NOT NULL auto_increment,
user_name char(32) NOT NULL default '',
password char(32) NOT NULL default '',
last_session char(32) NOT NULL default '',
first_name char(32) NOT NULL default '',
last_name char(32) NOT NULL default '',
address_1 char(64) NOT NULL default '',
address_2 char(64) NOT NULL default '',
city char(64) NOT NULL default '',
state char(48) NOT NULL default '',
postal_code char(14) NOT NULL default '',
country char(48) NOT NULL default '',
home_phone char(24) NOT NULL default '',
bus_phone char(24) NOT NULL default '',
cell_phone char(24) NOT NULL default '',
pager char(24) NOT NULL default '',
fax char(24) NOT NULL default '',
email char(64) NOT NULL default '',
taxpayer_id char(16) NOT NULL default '',
title char(64) NOT NULL default '',
department char(64) NOT NULL default '',
extension char(8) NOT NULL default '',
last_login int(12) unsigned NOT NULL default '0',
last_logout int(12) unsigned NOT NULL default '0',
user_created int(12) unsigned NOT NULL default '0',
PRIMARY KEY (user_id),
UNIQUE KEY user_matrix (user_name)
) TYPE=MyISAM;

Notice there are two unique columns: the user_name and the PRIMARY_KEY user_id. Like I said earlier, it's simply because I choose to use integer-based comparisons rather than string-based comparisons because they're faster. I use the md5() function to hash passwords rather than the MySQL PASSWORD() function because I'm working on PostgreSQL-compatible version of all this and I'd prefer not to have a lot of 'branching' code where database queries are made. So using md5() gives me better cross-database support at the end of the day. Your mileage may vary...

The next table is for 'groups'. Every user needs to be a member of at least 1 'group', so I can have content that can be accessed at a group level; such as 'Administrators' or 'Remote Users', etc... Here's the structure for the groups table that I use:

DROP TABLE IF EXISTS groups;
CREATE TABLE groups (
groups_id int(8) unsigned NOT NULL auto_increment,
groups_name char(32) NOT NULL default '',
groups_description char(128) NOT NULL default '',
groups_dir char(128) NOT NULL default '',
groups_created int(12) unsigned NOT NULL default '0',
PRIMARY KEY (groups_id),
UNIQUE KEY groups_matrix (groups_name)
) TYPE=MyISAM;

Like the users table, each group has a groups_id and groups_name which are UNIQUE. For the same reasons, speed when running queries.

And ultimately, there needs to be a table which performs the 'mapping' of users to groups. Hence, my user_groups table which allows 'users' to mapped to one or more 'groups':

DROP TABLE IF EXISTS user_groups;
CREATE TABLE user_groups (
user_id int(8) unsigned NOT NULL default '0',
groups_id int(8) unsigned NOT NULL default '0',
created int(12) unsigned NOT NULL default '0',
expires int(12) unsigned NOT NULL default '0',
warning int(12) unsigned NOT NULL default '0',
UNIQUE KEY user_groups_matrix (user_id,groups_id)
) TYPE=MyISAM;

Note that the UNIQUE key in this table consists of both the user_id and the groups_id, which ultimately allows any 'user' to be a member of any 'group'... Also, this provides for users membership in specific groups to expire.

If you've noticed that I never use any temporal data types (DATE, TIME, or DATETIME) columns, that's a good catch. Every date since Jan 1, 1970 can be measured in UNIX time (the number of seconds since Jan 1 1970), and since PHP provides infinite formatting utilities for dates, I've elected to use UNIX timestamps rather than temporal data types. They probably compare a bit faster than temporal data types, since conversion to a UNIX timestamp needs to occur within the database engine or PHP intertpreter before a comparison can be made.

Try and digest this much, and we'll take it from there...

MrAlaska

That's some pretty good stuff there, mrmuffin, thanx for sharing.

I have been a proponent of 'rolling your own session' ever since I heard of sessions, I just think the job can be done more efficiently when customized for the application. I am working on my own scripts to accomplish that end and so far it appears I am developing along the same lines as you, although mine is in the embryonic stage.

One question that I have been wondering about, though, is whether the convenience of storing the sessions in the database is worth the extra overhead (if any) compared to storing them in files. I have kind of wondered if it might be more efficient to write the sessions to a file, and if so if it would be enough to be worth the trouble to do so.

Using the session ID for the primary key had me slapping my forehead. DUH. That is one change I am instituting immediately and simplifies a couple issues.

mrmufin

Originally posted by MrAlaska
I have been a proponent of 'rolling your own session' ever since I heard of sessions, I just think the job can be done more efficiently when customized for the application.

One question that I have been wondering about, though, is whether the convenience of storing the sessions in the database is worth the extra overhead (if any) compared to storing them in files. I have kind of wondered if it might be more efficient to write the sessions to a file, and if so if it would be enough to be worth the trouble to do so.

Using the session ID for the primary key had me slapping my forehead. DUH.

I believe the first part of your question answers (at least in part) the second part.

The problems that I have with flat-files include...

Most PHP interpreters run inside the HTTP daemon, which typically has minimal filesystem permissions. (Well, PHP interpreters running on *nix clones and derivatives, anyway. I haven't got the foggiest friggen clue on how the stuff runs on Win32 servers, nor do I have any burning desire to find out.)

While we could argue and debate the efficiency of databases vs. flat files till we're both numb in the jaw-bone, I just think it's easier from both a coding perspective and installation / configuration perspective to use a database.

I personally think that if the balance of your website is using database support, then WTF, make the database connection once at the top of your script, do your session stuff, and carry on.

However, if the session management utilties are the only component using a backend database, then I could be more easily convinced to explore alternative options...

Most stuff that I design (ultimately) ends up using a session table which has many more columns than what I show here. That's because I know each session_id is unique, which makes that table the perfect 'catch-all' stashbox for every other datafield which must be unique for a given user, but doesn't necessarily need to linger after the user logs out and the session gets deleted. So after I update the 'updated' column, I can just run a 'SELECT *' on the session table, store the data in an object or array, and it's available throughout the balance of the application.

As always, your mileage may vary. Have fun...

MrAlaska

Thank you for taking the time to lay it out like that, it makes perfect sense,and affirms my original direction. Every page I am building requires a connection open anyway, so what's one more query?

This discussion in the newbie forum might seem a bit intimidating to a true newbie who drops in wondering how to validate a login screen l This newbie, however, appreciates it.

One other thing I haven't decided the best way to deal with, what is the most elegant means you have found to invalidate/remove the session when the user fails to log out? To trigger it, I mean.

mrmufin

Originally posted by MrAlaska

One other thing I haven't decided the best way to deal with, what is the most elegant means you have found to invalidate/remove the session when the user fails to log out? To trigger it, I mean.

An excellent question, with a very simple answer and implementation. Every 'session' has a time out value, in seconds. This is a static value stored and the class, and may be over-written by passing a configuration array when instantiating the session object...

Prior to every insert of a 'new' session; I just delete any sessions whose updated column is less than time() - $timeout_value. Since most session queries are going to be updates, we keep an index on the 'updated' column. This not only helps the updates happen a bit quicker, but the deletes as well.

MrAlaska

Ok, so I think I am following you. Of course it is spawning a myriad of other questions, but I will try to stay within the context of the topic l

Basically, if I am reading you right, if person A's session has expired, the instantiation of a session for person B will trigger the function to check for expired sessions and remove person A's session. I don't suppose the event of person B and person C logging in simultaneously would be an issue. I am not sure how PHP threads the requests, I need to look into that, but even if it turned out one request was trying to remove a session that another request had just removed I don't suppose any harm would be done.

I was playing with checking on every read of the table which of course would introduce much more activity than desireable. Your method might not be THE perfect trigger for my application, but is better than what I had and it does introduce fodder for more better thinking, and it also might explain some peculiar behavior a friend and I noticed on another site. I left the house without logging out to visit a friend who was still on the site. When I got to her house (two hours away), I was still showing as an active user online, but when she closed her browser and logged back in again it immediately showed me as being offline. It didn't make sense at the time, but it does now if they were using such a trigger.

That is a good point on indexing the update column, one I had actually already invoked. Of course the questions bubbling up regarding efficient searching on multiple columns of which only one contains an index is documented in the manual, I just need to either figure it out or quit trying to make it read the way I want it to. Thank you once again!

mrmufin

Originally posted by MrAlaska
I am not sure how PHP threads the requests, I need to look into that, but even if it turned out one request was trying to remove a session that another request had just removed I don't suppose any harm would be done.

I don't know how PHP threads requests either. Nor I do I want to have to worry about that shtuff, otherwise I'd be a C programmer. Let PHP and MySQL sort those issues out.

Recall what I said a few posts back: the session is being set prior to log in. This is key.

User A logs starts his session (hits a .php page on the site). Suppose the timeout is 600 seconds. Regardless of whether or not User A is logged in (in which case we wouldn't really know* that it's User A now, would we), we already know his unique identifier (session_id), his browser, whether or not he accepts cookies, and his IP address. As long as User A continues to hit the site before 10 minutes are up, he's cool. His session still exists. Even though he hasn't logged in yet.

User B comes along. Her session is set. She immediately logs in. The only thing that changed in her 'session data' is that the user_id column in the session table went from 0 to some non-zero value. While User B is surfing around the site, User A spaces out for a while and then decides to make a donut run. User B continues to surf around the site.

Once User A returns with a box of Bavarian Creme donuts and a large coffee (precisely 13 minutes after his last request to the server), one of two things could've happened. Either User C came along within the last three minutes and deleted User A's session ID (again regardless of whether or not User C actually 'logged in'), or she didn't come along.

Now User A is back and decides to 'log in' and rumple his stuff. If User C did come along within the last three minutes, whoop-de-doo. A new session is set for User A. It didn't really matter since he wasn't logged in anyway. If User C didn't come along, it wouldn't matter.

Now had User A been logged in before he spaced out and made the donut run, he'd have to log in again only if User C came along within the last three minutes, since the time prior to his last request to the server was precisely 13 minutes ago.

Soooo... by isolating the 'session' (which I define as "a series of HTTP requests and responses between a unique client-server pair") from the user data, and then linking a user_id column into the session table, the only thing I need to send back and forth between the client and the server with every request is the session identifier. If the session.user_id column is 0, I don't know who you are. If it's not, I can run a joined select across any table where session.user_id=other_table.user_id and associate the appropriate data with the session 'owner'...

MrAlaska

I guess did sorta miss the part you emphasized. When I was referring to a session I was referring to the portion of the session that is recorded. At this point I only begin recording when they begin the log-in process.

I plant a cookie when they hit the index page, but do not try to read it until they open log-in.php. (unless they try to sneak around) At the log-in page is where the session table gets introduced to their IP, browser, etc. but only if a cookie is present. If the cookie is not present I know they do not have cookies enabled and send them back to the index page with a patronizing little note instructing them how to upgrade their browser. (I have not began coding the cookie in the URL portion yet, that is another reason I am staying tuned)

If a user attempts to access a member page, the mere absense of a properly formatted cookie is enough to alert me he is not logged in and I can give them the boot without bothering the db to check the recorded session. In my case I don't see a need to maintain state with an unlogged visitor, but if I am missing something I am sure I will be illuminated before it is all over.

One thing, though, I did deviate from your methodology on. I duplicate the user name and other constantly needed variables into the session table. Since the table will never contain more rows than there are active sessions (in my case I move the expired unique data to a log file then delete it from the session table) I didn't feel it would contribute greatly to bloat, and I felt reduced load of not needing to constantly make two table joins (pluss two extra queries in my case) paid for a little data redundancy. Breaking the rules of normalization is justified if you can pay for it in performance, and my instincts lead me to believe I can in this case.

Damn, I gotta stop reading your posts. My to-do list is growing faster than my code lol

mrmufin

Originally posted by MrAlaska
I have not began coding the cookie in the URL portion yet, that is another reason I am staying tuned

Actually, that's one of the easier components to write:

function AppendURL($v) {
	// If the client browser accepts cookies, we're cool. There's nothing to do:
	if($this->cookie == 1) {
        	return $v;
        }

// If the URL has already been appended with other get data, append the URL with an '&':
if(ereg("\\?",$v)) {
	return $v .= '&session_get=' .  $this->id;
}

// Otherwise slap the session onto the URL with a '?':
return $v .= '?session_get=' . $this->id;
}

MrAlaska

Cool beenz, my code grew a little!

yikes classes!

hearing cartoon zyllophone boinks
while watching my to-do list expand

By the way, MBK, I didn't mean to leap into your topic and run
away with it. It just happened that your question coincided with
a work in progress and I seized the opportunity to investigate
some questions that have been nagging at me (got more, too)

Feel free to jump back in and get things back to the nitty gritty if
you have any questions so far.

MBK

I read through the posts, and didn't really understand that much of them.
I have looked at the tables you say to create and understood all that, which is now done, whats the next step?

mrmufin

Originally posted by MBK
I read through the posts, and didn't really understand that much of them.
I have looked at the tables you say to create and understood all that, which is now done, whats the next step?

Okay, I'll back up a few steps, MBK. By default, HTTP requests and responses are 'stateless', which essentially means that when a browser makes a request to webserver, the server responds, and the connection closes. Each request to a webserver happens independently of other requests.

By default, the web server doesn't care who is requesting what; it sits there waiting, listening for requests on port 80, gets a request and does its best to respond. This is fine for plain old static HTML.

However, when dealing with anything which requires specific user-access rights (and here's where we start getting back to your original request) it's imperative that the server know who IS requesting what. Or, 'maintenance of state'. Since a 'maintained state' is not a default property of HTTP communications, a maintained state must be deployed.

Hence, session management; where a unique identifier is created on the server and that identifier is passed from the browser with every request. A few posts back, I defined a 'session' as 'a series of requests and responses between a unique client-server pair'. In other words, a specific browsers series of requests to a specific server wherein the browser sends a 'key' (session ID) to the server with every request. This enables the server to know, in essence, know who is requesting what.

Since all of this ties in with your original post, "how I can issue a user name and password to the user, so they can change their data, and no unrestricted users can change their info", it's crucial that you recognize this little factoid about the HTTP protocol. It's also imperative (at least in my humble opinion) that whatever method is used to transport the unique session identifier to the server within every request from the browser, the method be seamless and completely transparent to the user sitting at the browser.

While MrAlaska and I have been discussing various implementations of session management, whether or not to use a database for the task, at what point is the session ID created, whether to send the session ID as a cookie, append it to the URL, send it as a hidden form field, etc, it's imperative that the method be entirely transparent to the user. All of these issues must be understood and solved before we can get back around to any immediate solution regarding a specific user being able to edit only their data, since the server answering the request from the users' browser must first know what user is making the request.

As I also mentioned many posts back, I rolled my own session manager before PHP had native sessions. Since I believe the process of session management needs to be transparent to the user, and cookies can be disabled on many, many browsers, my flavor of session management allows for the unique session identifier to be passed from the browser to the server in any of three methods:

As a cookie (a COOKIE variable);
Tacked on to a URL (a GET variable);
Within a form (a POST variable);

Still with me?

MBK

Yeah I get the jist of everything you have said, and how things must be passed around.

What is the next step of creating this then?

mrmufin

Originally posted by MBK
Yeah I get the jist of everything you have said, and how things must be passed around.

What is the next step of creating this then?

First you need to determine if a session ID has been sent with the request from the browser. Using my home-grown version of session management as the example, the process which I go through involves checking the various global arrays. (BTW, I use distinct names for the session ID based on the means in which it was set.)

Additionally, I wrap all this stuff inside a class named 'Session', so bear in mind this might just change for your actual deployment:

$session_id = ''; // Since the 'name' of the session variable is never sent as 'session_id', we can safely set it to an empty string initially.
$cookie = 0; // 1 or 0, based on whether or not the client browser accepts cookies
$length = 16; // the number of characters in the session ID
if(isset($_COOKIE['session_cookie'])) {
	// a session ID was sent as a cookie, so we can safely assume that the browser *does* accept cookies.
	$session_id = $_COOKIE['session_cookie'];
	$cookie = 1;

} elseif(isset($_GET['session_get'])) {

// The browser sent the session ID as a URL-appended (GET) variable, so we assume the browser does *not* accept cookies:
$session_id = $_COOKIE['session_get'];
$cookie = 0;

} elseif(isset($_POST['session_post'])) {

// The browser sent the cookie as hidden form element data. We'll also assume that the browser doesn't accept cookies in this case:
$session_id = $_POST['session_post'];
$cookie = 0;
} else {

// No session ID was sent.
// This is where we'd create a new session (and insert that record into the database if using a backend database).
// Once a new session has been inserted, we resend the header, setting a cookie named 'session_cookie' AND appending the header URL
// We'll get to this stuff next...
}

alsaffar

Hi mrmufin,

You looks like that you've been in this field from long time ago, so we need your experience and I apreciate your HELP 🙂

Sorry MBK, too many people are bothering you 😃

So mrmufin, let me be in the view with you, is my following conclusion correct?

Your code should be placed at the top of the first page that the user will open, to detect if there was a session id was sent to the user browser before? or what is this code is trying to do? and where it'll be placed?

You know I'm totaly confused with session issue, you can take a look at my last post

Please I need to know how I can manage sessions.

alsaffar

Hi mrmufin,

I know you've been here discussing alot about using sessions and how we can develop our own session system to be secured as max as it can be.

posts in this thread made me confused about how can we develop a strong session system, so please I need from you 3 small scripts (3 PHP pages) using sessions with maximum security:

1) First one, about Signing Up Users.

2) The second one about Login Users.

3) A page that will check if the user is authenticated or not.

Please I don't want you really to hate me 😃 but if you see that my demand is too hard to be achieved, then be as brief as you can so none of us will get board :o

I have one problem with my WebHost, they turned off the session.use_trans_sid so i have to hardcode the SID in every single page if the cookies are disabled in user's browsers. But this in mind so it'll work with 100%.

I apreciate your help and please take your time 🙂

mrmufin

Originally posted by alsaffar

posts in this thread made me confused about how can we develop a strong session system, so please I need from you 3 small scripts (3 PHP pages) using sessions with maximum security:

Let's make one thing clear from the get-go: I won't write your application for free. We've all gotta eat, ya know?

I will provide snippets, examples, examplanations, philosophies, pitfalls, stuff to consider, etc. You may elect to absorb or ignore as little or as much as you deem appropriate, and apply anything or everything that's useful for your application. But I will not spend my time writing your application without monetary compensation.

Session management can be a fairly complex process. As I said earlier in this thread, there are many ways to perform the task of maintaining state between client browser and web server. Since I had a need for session management before PHP had native sessions, I rolled my own session management component. The previous posts in this thread represent the database structure which I use at the core of my session management, as well as the general components and 'philosophy' behind it. But do bear in mind, that why I do things this way is largely becuase of situations which I've encountered, such as client browsers not accepting and the need for very clear distinctions about what users should be granted the ability to do what (INSERT, DELETE, SELECT, UPDATE) to specific content in a backend database.

What I currently use is a method wherein each 'user' is a member of one (or zero, I suppose) or more 'groups'. Assignment of permissions means placing a 'user' into a 'group' via the 'user_groups' table. 'Content', or functionality pertaining to specific actions on data within specified tables is checked 'assigned' to one or more 'groups'.

For example, you may have an 'Administrators' group whose members are allowed to delete users. In the request passed to the server to delete a specific user, there are (at least) three variables sent with the request (either via the POST variables in a form or URL-appended GET variables). Those variables would include:

1 - a delete key, which tells the application you're trying to delete a record;

2 - a table key, which tells the application what table you're deleting the record from;

3 - a record key, which tells the application what specific record to delete;

And possibly (if you're browser doesn't accept cookies) a fourth component, the session identifier.

When the server receives the request, it verifies all of the following, most of which are handled by various utilities in the Session class that I use:

Was a session identifier sent with the request?
Is there a user associated with the session?
Is the requested action limited to certain user groups?
Is the user a member of (at least one of) the groups allowed to the perform the requested action?
Was the request coming from the same IP address associated with the session?
Does the User-agent string sent with the request match that which is associated with the session?

If any of these tests fail, the request from the client the request to delete the record from the user table is denied. How 'unauthorized actions' are handled may include one or more of the following:

1 - Deleting the session from the session table (if one exists). This is always a good idea if your site contains data which is 'tempting' to hackers;

2 - Prompting for a log in;

3 - If you want to try to get extreme, you could probably set up a 'banned IP' table, and reject all requests originating from that remote address. But if you do, you'll probably screw up matters for users that access the site from behind a firewall.

I have one problem with my WebHost, they turned off the session.use_trans_sid so i have to hardcode the SID in every single page if the cookies are disabled in user's browsers. But this in mind so it'll work with 100%.

Bummer.