Jump to content
Larry Ullman's Book Forums

How To Limit Privileges To One Table Only


Recommended Posts

I'm using the scripts in chapter 16 to register users on a site.  Because I was concerned about granting privileges to Select, Insert, and Update on the entire DB, I created a separate DB just for "external users".  Everything works just great, except now I want to access this external user DB in the Admin site which uses a different DB (the general site content DB).  I tried adding two connection scripts in my admin.php file, and while it works fine on my MAMP Pro server, I'm getting errors on my production server.  My connection scripts setup different connection variables, so there should be no conflict there, and I use those connection variables only in the include files accessing the respective DB's.  Here are some samples of my connections:

 

This first connection script is for the general site content DB

if (stristr($_SERVER['HTTP_HOST'], DOMAIN) || (substr($_SERVER['HTTP_HOST'], 0, 7) == '192.168')) {
	$local = TRUE;
} else {
	$local = FALSE;
}
if ($local) {
	$hostname_siteadmin = "localhost";
	$database_siteadmin = "localgeneralsiteDB";
	$username_siteadmin = "root";
	$password_siteadmin = "localPWD";
	$siteadmin = mysql_pconnect($hostname_siteadmin, $username_siteadmin, $password_siteadmin) or trigger_error(mysql_error(),E_USER_ERROR);
	} else {
	$hostname_siteadmin = "localhost";
	$database_siteadmin = "productionsiteDB";
	$username_siteadmin = "productionsiteUSER";
	$password_siteadmin = "productionsitePWD";
	$siteadmin = mysql_pconnect($hostname_siteadmin, $username_siteadmin, $password_siteadmin) or trigger_error(mysql_error(),E_USER_ERROR); 
}
?>

This next one is for the external user DB

if (stristr($_SERVER['HTTP_HOST'], DOMAIN) || (substr($_SERVER['HTTP_HOST'], 0, 7) == '192.168')) {
	$local = TRUE;
} else {
	$local = FALSE;
}

if ($local) {
	$hostname_vusradmin = "localhost";
	$database_vusradmin = "localextuserDB";
	$username_vusradmin = "root";
	$password_vusradmin = "localPWD";
	$vusradmin = mysql_pconnect($hostname_vusradmin, $username_vusradmin, $password_vusradmin) or trigger_error(mysql_error(),E_USER_ERROR);
	} else {
	$hostname_vusradmin = "localhost";
	$database_vusradmin = "productionsiteDB";
	$username_vusradmin = "productionsiteUSER";
	$password_vusradmin = "productionsitePWD";
	$vusradmin = mysql_pconnect($hostname_vusradmin, $username_vusradmin, $password_vusradmin) or trigger_error(mysql_error(),E_USER_ERROR); 
}
?>

Then in my admin.php file I call require_once() for each connection script. As I said, on my testing server this works with no problems, but on the production server only the connection script which is loaded last works.  This would seem to indicate a variable conflict, but again, the variables are different for each connection script. 

mysql_select_db($database_vusradmin, $vusradmin);
$query = "SELECT user_id FROM ext_users";
$result = mysql_query($query);
mysql_select_db($database_siteadmin, $siteadmin);
$query = "SELECT user_id FROM users WHERE hidden = 'n'";
$result = mysql_query($query);

Now if someone can tell me what I'm doing wrong - why it works on my dev. server but not the production server - I would be most grateful.   Otherwise...

 

I searched the forum for threads related to accessing two databases at a time, and the replies were pretty consistent - use one database if possible.  Larry suggests creating different users with different privileges - I have.  The public side user for general site content has only Select privileges, admin side user for general site content has Select, Insert, Update and Delete privileges.  But the privileges required for registering an external user are Select, Insert, and Update, isn't it risky giving such broad privileges to unknown visitors?  So then my question is the same as the topic title, how can I limit the public side user (soon to be registered user) privileges to one table only?  If I can do that, I will bring the ext_users table into the general DB and then one connection script will work.

 

I sure would appreciate comments.

Link to comment
Share on other sites

Sorry for the BUMP, but can anyone offer a suggestion as to why this works on my dev. server but not my live server?  I have looked at the email output of my_error_handler, and I see the two different connection variables each of which has been assigned a Resource id. I also see the username and password for each connection which are both correct.  The error I'm receiving is:

mysql_num_rows(): supplied argument is not a valid MySQL result resource

and the error is pointing to line 33 which is this:

$totalrecords = mysql_num_rows($result);

which indicates that the query is not producing anything, which is borne out by the error message content:

[query] => SELECT user_id FROM ext_users
[result] => 

the result is empty.

 

To sum up, this works perfectly on my development server, but not on my production server.  The error report shows both connections being made, each having a correct username and password.  A separate Resource id has been assigned to each connection, but the first DB interaction with ext_users, the Select query, fails.  What could this be?  Is there a server setting that might be affecting this?

 

Any helpful suggestions would be most welcome.

Link to comment
Share on other sites

Resolved

 

I recreated both the connection script and DB query and now it is working fine on both the dev server and live server.  I must confess, I don't see the difference between the original version and the new version, but who cares as long as it works.

Link to comment
Share on other sites

 Share

×
×
  • Create New...