Posted by Gordon Burditt on 07/20/05 22:50

>>That, as I understand it, is a *MySQL user* account. It has nothing
>>to do with a UNIX or Windows login account of the same or similar
>>name, except by coincidence.
>
>I just used the term ROOT because that's the first thing that came to
>mind.. I'm not using mySQL for the record, the database itself will be
>most likely running off an AS400 DB2 system.

Ok, but it's still relevant to distinguish between different types
of "users": database (enforced by the database itself), application
or web users (enforced by the PHP application, possibly using data
in the database), and sometimes the privileges of system users (e.g.
the UNIX user id that Apache or PHP runs as).

>>When the web user attempts to use an application, a check should
>>be made whether he's authorized to use it. Depending on your login
>>setup, you may not have to re-verify the password (sessions can
>>handle keeping track of who the web user logged in as) but you need
>>to check "is user mike allowed to use the global_thermonuclear_war
>>application?" If not, display an error message and/or redirect
>>the user to the login page.
>
>Yes, every application will start with verifying if the user has access
>to this particular application and if not will display an error message
>and a link to the login page.

>> What does pre-parsed have to do with it?

pre-parsed: parsed before ... before WHAT?

>I consider the code of my .php file pre-parsed... I probably didn't
>explain it the best in the previous message..

I consider that terminology misleading, and I thought you might
mean "pre-compiled": turned into some binary mess that could be
executed faster because it's already been parsed. In any case, it
doesn't really have much to do with the security issues.

>Say I php file called RunSQLStatement.php and I included this file with
>every other application that required access to the database. In this
>file would be two functions. One to check if the user has permission
>for a specified application and another to actually connect to the DB
>perform a SQL statement and return any results.

I think I'd call this "an include file of functions". It's a perfectly
reasonable way of handling common code.

>Now I could simply have a constant in the
>$connect = odbc_connect ("AS400", "ROOT","MY_ROOT_PSWD");
>
>Now my issue is if anyone somehow accesses RunSQLStatement.php before
>it's sent to PHP to be parased/processed and redirected to the user
>they will have access to the entire database (provided they can connect
>of course.. I'd also limit access to that account to the IP of the web
>server)

I prefer to put database access info in an include file which is
*OUTSIDE* the document tree. It is possible for PHP to break, for
example, for a few seconds while you are upgrading it. (I really
should shut off Apache for this. Sometimes I forget). In that
situation, the .php file gets shown as text. Using my method, if
PHP is broken, the access info is outside the document tree, so the
web server won't serve it, and if it is NOT broken, PHP will interpret
it, and you'll see the output of it. Either way, the access info
is not sent to the user. The code using the access info uses the
variables, which are by convention always the same unless the page
happens to need *TWO* database connections (not unheard of when the
purpose is to migrate or check migration of data from one to another).

If you limit where access can come from, especially if it's localhost
(web server and database on the same box), even revealing the DB
password won't matter much except to someone with an account on the
same web server as you have (not at all uncommon with hosted servers).

<?php
$mysql_host = 'localhost';
$mysql_user = 'me';
$mysql_password = 'drowssap';
$mysql_db = 'database';
?>
Another advantage of this is that just by changing the include file,
or which include file I use, I can switch between the test and the
production database, or move the location of the database, assuming
I avoid cross-database references and use these variables consistently.
I use MySQL as an example, but other databases typically require
the same sort of information: what host is the database on (and
possibly which port), what authority do you have to use the database
(username & password, often) and which database are you using. The
details may vary a bit between different setups, but it's mostly
the same idea.

Note that this technique does NOT defend you against either (a) the
admin of your hosted web server, or (b) other customers (possibly
your competitors or thieves) on the same hosted box, unless the web
server uses different system user ids for each user (see mod_suphp
for Apache, or suEXEC). I believe most hosts don't use this.

>I guess what I'm asking is this method generally acceptable? What are
>other designs for web users? That sort of thing.

Keeping track of what user is logged in is best done with sessions
in most cases, since a lot of work has been done in this area that
is difficult to reproduce manually.

Incidentally, it is possible to use a session handler to store sessions
in a database rather than in little temporary files. This has the
advantage that sessions are available to multiple servers sharing
load via DNS round-robin rather than having them break horribly every
time the client goes to a different server.

Gordon L. Burditt

[Back to original message]