Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Between 9pm and 6am I run automated jobs to update our databases, and I want to disable a specific list of users from logging into MySQL.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Backup your database before attempting anything like this, but you could have two cron jobs. The first one calls a script at 9PM The second one calls a script at 6 AM

The scripts would be in the language of your choice and would connect via mysql to the mysql database (I mean the actual mysql database.)

You could have it modify the user table so that the users you don't want to be able to log in had an allowed host that doesn't exist.

For the second script you would simply reverse the change.

You could then have both of these scripts use a database or file to get a list of people to temporarily blacklist.


Armchair implementation in Perl (provided you have mysql module installed)

use DBI;

$dbServer='';
$user='';
$pass='';
$ident=$ARGV[0];

my $dsn = "dbi:mysql:database=mysql;host=$dbServer;port=3306";
my $dbh = DBI->connect($dsn, "$user","$pass") or die "Can't connet to the Database: $DBI::errstr\n";
my $sth = $dbh->prepare("UPDATE user SET Host='nobody' WHERE User='johndoe'");
$sth -> execute();

The elegant alternative may be implementing it as a mysql plugin provided you are using 5.1+ However, I am unsure as to weather the API allows this much control.

share|improve this answer
    
Great! I was hoping that it was going to be as easy as updating to the mysql.user table. Thanks for the script! –  Edward Nov 5 '10 at 15:30

While you can easily manipulate the users table to prevent users from logging in, how do you deal with users who are already have a session on the database? Or is this not important?

How do your users/applications connect? It might be simpler to update the firewall settings on the server box to block new connections to the MySQL network port (possible with Linux, BSD, but more tricky on MS platforms). Note the important part in the last sentence was new connections (look at the established/related options for iptables). You didn't say what OS you're running this on.

Taking this a step further, you could then spike established connections at, say 9:20pm (note just blocking the ports may not have the desired effect)

However leaving aside how you block access for a while, you've clearly got yourself into batch processing hell if you must do 9 hours of processing every day. Batch processing should be avoided at all cost - asynchronous messaging is marginally better, but life becomes so much simpler if you handle your output synchronously with the input.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.