[Courses] Web Database Security (was Re: knock knock?)

[Kai MacTane]

At 5/1/02 03:52 PM , Raven, corporate courtesan wrote:
> > So if its not a bad time, could I possibly kick things off again by 
> trying the firewalling exercise that Raven set a bit ago, only this time 
> have a go writing the rules with iptables?
>
>         That sounds great -- have at it, and I'll come up with another
>scenario for us to pit our collective wits against too.  (Tomorrow -- I
>have nearly 500 messages to wade through to catch up.)

For that matter, I've got that scenario I was mentioning to you on the 
phone a few days back. This is a real-world situation (luckily not 
time-critical!), I'd love to see what suggestions people can give.

I've been building a Web-based database for tracking words, 
prefixes/suffixes, declensions, conjugations, and various other grammatical 
constructs in JRR Tolkien's constructed Quenya language ("High-Elven", as 
opposed to Sindarin, the "Low-Elven"). At this point, the software 
framework is in place, using HTML::Mason to communicate with a MySQL 
database that holds the actual linguistic data in one big table.

Now, I need to populate the database. One good way to do that would be to 
get other Tolkienian linguistics fans involved -- just the ones that I've 
seen displaying good scholarship online, of course, since I want to make 
sure the data in here is of high quality. So I want to be able to allow 
certain other individuals to edit the database.

(At the moment, both the contents of the table and its structure can be 
edited using the Web interface. Links to the pages to do so are only 
displayed in the nav bar if you're coming from the IP of my house's local 
network, but someone who knew the appropriate URLs could enter them by hand 
from another location.)

So, I need to make the various editing components deny access to anyone 
who's not approved. I can certainly set up a second database table for 
authentication information (probably just username and password) and have 
any component that needs to access that database for authentication purposes.

Once someone is authenticated, I can hand them a cookie, but I'd like to 
ensure that said cookie couldn't just be copied and pasted to some other 
machine. (It's not that I trust people that little, especially the ones I'd 
be trusting with access to the database. It's partly just for the security 
exercise, and partly "But what if one of their roommates sneaks onto their 
computer, or the thing gets stolen or hacked?")

Or, instead of a cookie, I could maybe do something else. I could set up 
Apache .htaccess protection on the required files, I think (they're in the 
same directory as the pages everyone needs to be able to see, but I might 
be able to do a carefully-crafted <Files> or <FilesMatch> directive...), 
and I could certainly get a Mason component or two to write information to 
the .htpasswd file if someone wants to change their own password. But then, 
I can't expire someone's access until they close their browser -- I'd like 
to be able to auto-expire their login after something like 20 or 30 minutes 
of inactivity.

Any ideas? What would be the most secure, and yet most convenient, way to 
do this?

                                                 --Kai MacTane
----------------------------------------------------------------------
"Uh-oh... Gravity works."
                                                 --Batty Koda, "Ferngully"
                                                   (Hey, bats are gothic.)