[M-ords-developers] JDBC, databases, and what probably needs fixed.

[David D. <da...@is...>]

Ricardo,

I'm glad you're looking forward to this.  I hope there's enough variety 
in the work here that will keep your interest and let you stretch out in 
your capabilities for a good while.

If you're most comfortable with the basic JDBC stuff, take a look at the 
CVS or SVN repository; go to the mords-server folder and you'll find the 
server systems where all the JDBC code is located.  Under there, the 
code is basically separated as follows:

    --Sources with [Something]Server as the name are the RMI 
interfaces--very straightforward method definitions, but not code.
    --Sources with [Something]Db are the concrete implementations--they 
have the database code (prepared statements, coded SELECT statements, 
etc) that deal with the database interaction.
    --The RxDb class is a mess.  I was trying to restrict write access 
to the prescription database (since "real" fake prescriptions are a hot 
black market item---and worth a LOT of time in jail if you get caught.)  
The reason it's a mess is I tried to use the java security model to have 
access based on Privileged code--checked against a security "database".  
In short--it's crap.  I'm seriously considering having all 
read/write/access permissions handled by the underlying database (since 
most modern databases have an underlying table permission system).
    The problem with this is that the user will have to log in with 
their database access account (username, login).  This is not a problem, 
because username/password is normally used to create a JDBC connection 
in many forms.  The problem is that the code will have to be re-written 
from the ground up to do 3 things:  1) The client or server will have to 
"remember" the username/password combo and pass it to the server for 
each request. 2) The concrete RMI implementation will have to take that 
to create a connection (which slows down the system UNLESS there is some 
kind of stashed/cached connection-pool standing by.  And 3) there 
_should_ be some form of database-management interface to control what 
privileges each user has.
    The problem with 3) is that each database uses different tables and 
controls (and values) to determine access--an interface hard-coded for 
MySQL probably wouldn't work with anything else--same for Postgres, 
Oracle, or even HSQLDB (I say "probably" because I haven't delved too 
deeply into the others.)
    Another option for 3) is to have a set of user tables custom-made 
for access control.  Again, not a big deal (and it would be database 
independent) but I haven't had time to work out the verbage.

    If you (or any of the others) have some ideas on how to do this in a 
simple, maintainable way, please let me know.  It's one of the basic 
problems the needs to be addresses.  As you work on it, try to remember 
two things:  First, this system will be used in places where technology 
is _not_ everyone's forte (so weird password requirements, dongles, or 
"Mission Impossible" eyeball scanners are probably out of the 
question).  At each location, there will probably be only 1-3 people who 
have a clue about how the system really works, and how to recover from 
any errors.
    Second, you have to assume that someone, somewhere will try to use 
the system to create illicit prescriptions.  While nothing is absolutely 
secure, it should be strong enough to make it obvious that one or more 
people had to break all the rules to cheat, and that someone who is 
"framed" (by having their password stolen) has a reasonable chance of 
proving that he didn't do it.  For example, if access time is logged, 
then the poor innocent can say "I wasn't there!  Check the security 
video to prove it!"
    (Along these lines, the system produces an automatic time stamp on 
the prescriptions created (as well as a hash of the information).  This 
makes it fairly easy to track down who was working at that time.)

    If anyone has any thoughts about this, please feel free to speak 
up.  I've got some ideas, but they are not necessarily the best ones--or 
the easiest.

Sincerely,
David Days
happyslayer

Ricardo Torres wrote:
> Wow, this is my first sort of real project ever!!! Mostly
> because uni projects are relatively easy compared to this.
>
> Thanks again and id like to ask what should i be learning
> now, i mean i've just started getting familiar with
> Netbeans, not even sure if that's what im supposed to know or if using 
> Eclipse is also ok.
>
> I know a bit of JAVA right now, mostly SWING and
> Certification knowledge, i took the SCJP exam about 10 days ago and 
> passed with 83%, i also know some OOP basic concepts, the rest not so 
> good at, i do know how to do prepared statements and standard queries, 
> but anyways just would like to know what should i be learning right 
> now, i really want to start writing some code for the project ASAP :P.