Grabbing a userid: shifting the work

Real world case

Authentication done by application
Perl script snippet:

## Perform validation via complicated external calls
## $username has been set, get $userid

1.  $SQL = "SELECT id FROM users WHERE lower(username)=?";
2.  $sth = $dbh->prepare($SQL);
3.  $count = $sth->execute(lc $username);
4.  if ($count eq '0E0') {
5.    $sth->finish();
6.    $userid = &AddNewUser($username);
7.  }
8.  else {
9.    $userid = $sth->fetchall_arrayref()->[0][0];
10. }
11. $dbh->commit();
12.
13. sub AddNewUser {
14.  my $username = shift;
15.  $SQL = "INSERT INTO users(username) VALUES (?)";
16.  $sth = $dbh->prepare($SQL);
17.  $count = $sth->execute($username);
18.  $SQL = "SELECT currval('userid_seq')";
19.  return $dbh->selectall_arrayref($SQL)->[0][0];
20. }

What are we really doing?

IN: username OUT: userid
We do not really care if the user already exists!
Tread carefully


## Perform validation via complicated external calls
## $username has been set, get $userid

1. $SQL = sprintf("SELECT getuser(%s)", $dbh->quote($username));
2. $userid = $dbh->selectall_arrayref($SQL)->[0][0];

Benefits:

All atomic - we handle concurrency issues, locking, etc.
Application does not care if it has been created or not
User creation happens close to the data
The database can easily audit things (how many users?)
User creation is centralized


## Do not have to worry about lowercase - and getting it right:

1.  $SQL = "SELECT id FROM users WHERE lower(username)=?";
2.  $sth = $dbh->prepare($SQL);
3.  $count = $sth->execute(lc $username);


## Do not have to worry about knowing the sequence name:

18.  $SQL = "SELECT currval('userid_seq')";


## Application independence:

SELECT getuser('Flintstone', 'rubblerouser');

Caveats:

Full external validation would be ideal
Real-world constraints
Documentation must mention the underlying assumptions
The ideal case:

Ideal

1. $sth = $dbh->prepare("SELECT login(?,?)");
2. $sth->execute($username, $password);
3. $userid = $sth->fetch();

Everyone is happy:

DBA has greater control, more trust
Application can focus on more important things
Refactoring on a larger scale

Who makes the change?

DBA vs. developer
Who can the DBA trust?

How do we discover the need for this change?

Design specification: pseudo-code
Code review - DBA or dev
DBA analysis of the system
Refactoring