Sessions and Database Management

synergypoint

I am writing a MySQL database manager and need some advice on handling user session information.

I have a login script that looks up the users database login information for their MySQL database from a master database table. The master table has a record of all of the other databases on the server and their login information. To access the master database you need a master login password. I have a script to connect to the master database table which has the master password and login in it. It is stored in a php file as a php variable in a directory which has no browsing or execution privileges. You can only include the master password file in another script on the server. So hopefully that part is secure enough. If not please let me know what would be better.

When someone logs in the master table is searched for matches on their login and password. If a match is found I set a session parameter with a crypted hash variable which is the password and the user ip address concatenated. I store the login id in a session variable too. Then on subsequent pages I run a validation script to lookup the password in the master table from the session login id, and crpyt it and compare it to the session hash of the password. If they match I assume they are validated to use the page. I don't like that the password even encrypted is stored on the users system in a session cookie.

Is there a better way to do this? Should I create a databse table of active sessions with session ID's in it and just validate based on session id? If so how should I delete expired sessions?

And then once I know they are logged in, I need to have access to the specific username and password for the database they have permission to manage. Since I have to use the master password to get access to the specific databse passwords, should I just store the specific login password along with the current session Id, store it in a session variable, or just use the master password to look it up each page view?

Here is the login script I currently have...

		if($row = $fetch_array($query("SELECT dbu.id 'UserID', dbu.login 'Login', dbu.passwd 'Pass', dbu.db_id 'DBID', db.name 'DB_Name', dbm.Active 'Active' FROM psa.db_users dbu, psa.data_bases db, dbmanager.tDataBases dbm WHERE  db.id=dbu.db_id AND dbm.DB_ID=dbu.db_id AND dbu.login = '$_POST[LOGIN_ID]' AND dbu.passwd = '$_POST[LOGIN_PASSWORD]'")))
		{
			if (mysql_errno())
			{
				$result=db_error($errnofunc(),$errorfunc(),'','','');
				$message.=$result;
				if($DEBUG){trigger_error($result);}
			}
			if($row['Active']=="N")
			{
				$status_msg.="<SPAN class=\"errormsg\"><B>Your account has been disabled.</B> Please contact the system administrator for help.</SPAN>";
			}
			else
			{
				$addrpw = $row[Pass].getenv("REMOTE_ADDR");
				$c = crypt($addrpw,"Z_");
				$u = $row['UserID'];
				$dbid=$row['DBID'];
				$dbname=$row['DB_Name'];
				$CPALQ1=randomString('126').$c;
				$CPALQ2=randomString('126').$u;

			session_register('CPALQ1');
			session_register('CPALQ2');
			session_register('dbid');
			session_register('dbname');

			header("Location: main.php");
			exit;
		}
	}
	else
	{
		$status_msg.="<SPAN class=\"errormsg\"><B>ERROR: You did not enter a valid username or password.</B><BR>The Password and User Name are both case sensitive.</SPAN>";
	}

keith73

Instead of storing data about the tables in another database table and user info, why not just use their mysql user info.

It would be more secure with no overhead for you.

You don't really need to use sessions then, just every time you open a db connection, you'll know that the user is authorized.

and the user's password is not stored in a cookie, the only thing stored in a cookie is the session ID #. PHP stores all of the session data in a txt file on the server. check your php.ini file for session.save_path which is usually /tmp

keith

synergypoint

I think I have been confused about how sessions work.

Is it true that even if sessions support on the server is set to use cookies, that only the session id is stored in the cookie?

And any other session variables set through session_register() are only stored on the server in the temp file?

I was under the impression that any session variables registered would also be stored in a cookie if cookies are enabled but now I think that is wrong.

keith73

Originally posted by synergypoint
I think I have been confused about how sessions work.

Is it true that even if sessions support on the server is set to use cookies, that only the session id is stored in the cookie?

And any other session variables set through session_register() are only stored on the server in the temp file?

I was under the impression that any session variables registered would also be stored in a cookie if cookies are enabled but now I think that is wrong.

that is wrong. I thought that too the first time I used a session. But then I read more about them in the manual and actually looked at the files on the server.

keith