MySQL - connect/disconnect to db twice in a script - bad?

daymobrew

I have 2 db user accounts, one can only SELECT, the other can read and write.
My CGI script currently connects with the read-only account.
I want to, in the same script, accept input from a user and add a record to the db.

Should I, for security, read with read-only account, disconnect and reconnect with the read/write account to add the record? Will the reconnect be overly expensive timewise?
I know that I can do it all with the read/write account. I'm just trying to be as proper and secure as I can.

Cake Fiend

You should use the lowest necessary privilege per script of course, but in this case, I'm not sure I could see a security advantage in doing it this particular way to be honest. Can you give a code example?

theexis

If you're doing things by the book, create a stored procedure for the 1 "write" operation and give execute permission only on that to the "read only user". Also, what do you mean by "the user account can only SELECT" - that could be a security hole as well depending on what they can actually SELECT from.

daymobrew

The database is on SourceForge.net who host my BusSched app. They provide MySQL 4.1.10a and I have 3 accounts. The MySQL docs for 4.1 don't mention stored procedures, except in relation to Java connectivity , and even that reference mentions MySQL 5.0. The 5.0 docs have a large section on stored procedures. Maybe not in 4.1?

theexis wrote:

that could be a security hole as well depending on what they can actually SELECT from.

When I poke around with phpMyAdmin I only see my database and processes, so I assume they've limited what my 3 accounts can see.

theexis wrote:

create a stored procedure for the 1 "write" operation and give execute permission only on that to the "read only user".

I don't think I have permission (even the 3rd, admin account) to grant permissions. Admin account has "SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, CREATE TEMPORARY TABLE, DROP, INDEX and LOCK TABLES" privileges so I might be able to create one but not restrict its use.

You can read the full code. It's in perl.
See the

# TODO: Store data in database. Decide on user to use.

line for where I would think about putting the "write" code. I would probably move the later SELECT statement to earlier, and store the results in an array of arrays.

Here is part of the perl code that retrieves some data.

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$server",
                       $dbuser, $dbpw, {'RaiseError' => 1});

# Retrieve some stats.
my $sth = $dbh->prepare( 'SELECT FORMAT( (MAX( odometer ) / SUM( litres ) ), 2), FORMAT( (MAX( odometer ) / SUM( litres ) * '.LITRE2GALLON.' ), 2), MAX(mpl),MAX(mpg) FROM `car_mileage`');
$sth->execute();
my @stats = $sth->fetchrow_array();
# Retrieve min values, excluding the first fill where mileage is obviously zero.
$sth = $dbh->prepare( 'SELECT MIN(mpl),MIN(mpg),MAX(odometer) FROM `car_mileage` WHERE mpg!=0');
$sth->execute();
push( @stats, $sth->fetchrow_array() );

Cake Fiend

Well make sure you don't put your read/write account and password in a world-readable script, of course. Read it in from a local file (out of the scope of the webserver if possible).

Stored procedures aren't available in MySQL 4.1, but a workaround could be to code a user function, although that would probably be overkill and I'm not sure whether you can assign permissions.

But still, although I'm no MySQL expert, I don't see a reason to do it the way you're asking about. The risky part is submitting the inputted data, and that has to be done with a write-enabled account (obviously). So if you're going to be using a write-enabled account anyway, I don't see how using a read-only account earlier in the same script is any more secure.

daymobrew

Sico wrote:

Well make sure you don't put your read/write account and password in a world-readable script, of course. Read it in from a local file (out of the scope of the webserver if possible).

Good point. I thought about that when I posted the link to the script.