SQL Quicknotes

: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /var/www/virtual/rlogix/includes/unicode.inc on line 311.

# Mysql interaction subroutine
sub sql($$$$$$)
{
my ($username, $computer, $service, $date, $status, $rawline) = @_;

my $dbh = DBI->connect("DBI:mysql:authlog", "authlog");

# create table LOG (date Timestamp, service Char(40), computer Char(40), username Char(40), status Char(40));
# INSERT into LOG VALUES ({ts '2002-06-04 13:25:45'}, 'ssh', 'maxwell', 'bhatt', 'logon');
# select * FROM LOG where {ts '2002-06-05 00:00:00'} < {ts '2002-06-04 00:00:00'};
# delete from LOG where username = 'lawrence';

# Parse date
# Jun 4 13:07:25 -> 2002-06-04 13:07:25
my ($month, $day, $time) = ($date =~ /(\w+)\s+(\d+)\s+(.*)/);
my @m = ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
for (my $i; $m[$i]; $i++)
{
if ($m[$i] eq $month)
{
$month=$i + 1;
last;
}
}
my $year = (localtime(time))[5]+1900;
$date = "$year-$month-$day $time";
$dbh->do("insert into LOG values ({ts \'$date'}, \'$service\', \'$computer\', \'$username\', \'$status\', \'$rawline\')");

$dbh->disconnect();
}

Creating a user with
- the right to create dbs
- no access to dbs from other users
is easy. This user should have no super-user privileges.

select * from user where user='apache';
Host = localhost
user = apache
Password = I ain´t tellin´
Select_priv = Y
Insert_priv = Update_priv = Delete_priv = create_priv = Drop_priv = Reload_priv = Shutdown_priv = Process_priv = File_priv = Grant_priv = References_priv = Index_priv = Alter_priv = N

As you can see all apache is allowed is to run SELECT queries. Even that might be too much on this global level.

Creating a user with the right to
- browse and drop its own dbs
is a bit harder but can be done as well as a careful read of the documentation reveals:

The wildcard characters '%' and '_' can be used in the Host and Db fields of either table.

[...]

The server looks in the db table for a match on the Host, Db, and user fields. The Host and user fields are matched to the connecting user's hostname and mysql user name. The Db field is matched to the database the user wants to access. If there is no entry for the Host and user, access is denied.
If there is a matching db table entry and its Host field is not blank, that entry defines the user's database-specific privileges.
If the matching db table entry's Host field is blank, it signifies that the host table enumerates which hosts should be allowed access to the database.

http://www.mysql.com/doc/en/Request_access.html

With this knowledge we insert a new row into the db table:

insert into db set Host='localhost', Db='aaron%', user='apache', Select_priv ='Y', Insert_priv ='Y', Update_priv ='Y', Delete_priv ='Y', create_priv ='Y', Drop_priv ='Y', Grant_priv ='Y', References_priv ='Y', Index_priv ='Y', Alter_priv='Y';

As you can see the Db field contains the percent sign wildcard character. This row will apply to all databases whose name begins with aaron. Let´s see this setting in action (Be sure to flush privileges before testing this!):

mysql> create database apachetest;
ERROR 1044: Access denied for user: 'apache@localhost' to database 'apachetest'
mysql> create database aarontest;
Query OK, 1 row affected (0.02 sec)
mysql> drop database aarontest;
Query OK, 0 rows affected (0.00 sec)