Page 1 of 1

DB Users and Security

Posted: Wed Dec 21, 2005 10:48 pm
by Zoram
i was reading onion2K's comments on a different topic (viewtopic.php?t=42126) about having three different db users...
On a live production site you should have at least 3 seperate database users .. one that only has select privs for the general pages that just get data from the database, one that has insert and update privs on the tables that are modified through the front end (registration pages, checkout pages, etc), and one from back end admin pages.
What's more of the details / reasons for this?

Posted: Wed Dec 21, 2005 11:11 pm
by josh
Defense in depth, if a page get's compromised and all that page is doing is selecting data. and the mysql user that page is using only has select privileges, the worst the malicious user could do is select stuff. Although your scripts should already prevent against things that would even allow someone to cause a delete statement on a page that is selecting data, tell me this:

what is the disadvantage to multiple users?

it can only help..

Posted: Thu Dec 22, 2005 2:58 pm
by Zoram
My question isn't so much why you would have them, more of the application of them... i don't understand exactly how they would be employed...

Would you use a different user for the pages that process form information and a separate one for the main pages that don't do form processing? and the third wouldn't show up except in backend maintenance?

Posted: Thu Dec 22, 2005 4:43 pm
by onion2k
Zoram wrote:My question isn't so much why you would have them, more of the application of them... i don't understand exactly how they would be employed...

Would you use a different user for the pages that process form information and a separate one for the main pages that don't do form processing? and the third wouldn't show up except in backend maintenance?
Imagine your database is called "shop", and you set up one user called "shop_user". To connect to the db you'd use:

Code: Select all

$database = mysql_connect("localhost","shop_user","password");
mysql_select_db($database,"shop");
If there was a bug in your application that allowed users to issue their own SQL commands on the database then they'd be able to do anything that "shop_user" had the privs for .. if there was only one user then that would be just about anything. Imagine it was "delete from products" command .. uh oh .. where's all the products gone!

If you created three database users then any bug that someone found would still be able to issue commands to the database but only with the privs held by the database user of the script the bug exploited. In a general listing page you'd use a user called, for example, "shop_general":

Code: Select all

$database_general = mysql_connect("localhost","shop_general","password");
mysql_select_db($database_general,"shop");
Now if the exploit was run with "delete from products" it would fail as the shop_general user doesn't have the delete priviledge. You would have a shop_insert user that could select, insert, and update data, and a shop_admin user that could also delete data. That way you're covering yourself from malicious exploits even if you've not found them yet.

If you use more than one database user and your script is connected with two users in the same page you need to remember to specify the database connection in your mysql_query() commands:

Code: Select all

mysql_query("select * from table",$database_general);
It's just an extra layer of security.

Posted: Sat Dec 31, 2005 10:13 am
by Zoram
Is there anything special that you have to do in order to use multiple connectiions to one database?

I tried to establish two separate connections to the same db but it won't execute when i do so.

Code: Select all

// Select Connection
$dbSelect = mysql_connect($dbHost, $dbUserSelect, $dbPassSelect) or die("Could not connect to Database, try again later.");
mysql_select_db($dbName, $dbSelect) or die("Could not select to Database, try again later.");

// Edit Connection
$dbEdit = mysql_pconnect($dbHost, $dbUserEdit, $dbPassEdit) or die("Could not connect to Database, try again later.");
mysql_select_db($dbName, $dbEdit) or die("Could not select to Database, try again later.");
But when i try to use the $dbSelect Connection it fails what do you need to do in order to use the same db with multiple connections?

Posted: Sat Dec 31, 2005 10:41 am
by neophyte
When you query you have to specify your connection handle:
resource mysql_query ( string query [, resource link_identifier] )
You could also specify a new link in your connection call:

resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] )
hope that helps.....

Posted: Sat Dec 31, 2005 1:42 pm
by Zoram
Thanks so much... i guess i skipped over that argument. It seems to work now though.