#!/usr/bin/perl -w
# $Id: user_mgmt.pl 408 2009-05-01 22:32:11Z jonas $
# Copyright (C) 2009 Jonas Genannt <jonas.genannt@brachium-system.net>
#
# This program is free software; you can redistribute it and/or modify it
# under the terms of the GNU General Public License as published by the
# Free Software Foundation; either version 3 of the License, or 
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, see <http://www.gnu.org/licenses/>.

use Crypt::MySQL qw(password password41);
use String::MkPasswd qw(mkpasswd);
use DBI;
use Config::General;
use Getopt::Long;
use strict;

my $VERSION='$Id: user_mgmt.pl 408 2009-05-01 22:32:11Z jonas $';
# subs
sub usage ;
sub mysql_check_username ;
sub mysql_check_hostname ;
sub mysql_drop_all_global_privs ;
sub mysql_drop_all_privs ;
sub mysql_check_if_user_exists ;
sub mysql_create_user ;
sub mysql_set_global_user_priv ;
sub check_username_exists_config ;
sub update_users_password_config ;

my @mysql_priv_global = qw/Select_priv 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 Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv/;

my %mysql_priv_db_rights = (
	'Select_priv'		=> 'N',
	'Insert_priv'		=> 'N',
	'Update_priv'		=> 'N',
	'Delete_priv'		=> 'N',
	'Create_priv'		=> 'N',
	'Drop_priv'		=> 'N',
	'Grant_priv'		=> 'N',
	'References_priv'	=> 'N',
	'Index_priv'		=> 'N',
	'Alter_priv'		=> 'N',
	'Create_tmp_table_priv'	=> 'N',
	'Lock_tables_priv'	=> 'N',
	'Create_view_priv'	=> 'N',
	'Show_view_priv'	=> 'N',
	'Create_routine_priv'	=> 'N',
	'Alter_routine_priv'	=> 'N',
	'Execute_priv'		=> 'N',
);


my ($verbose,$config_filename,$arg_username,$arg_set_password,$arg_mysql_old_hashing,$arg_help);
my $result = GetOptions (
	"verbose|v"       	=> \$verbose,
	"config=s"		=> \$config_filename,
	"user=s"		=> \$arg_username,
	"set-password"  	=> \$arg_set_password,
	"mysql-old-hashing"	=> \$arg_mysql_old_hashing,
	"help|h"		=> \$arg_help
	);
if (defined($arg_help)) {
	usage;
	exit;
}
if (!defined($config_filename) || ! -f $config_filename) {
	usage;
	exit 1;
}
my $cf_perm = sprintf("%04o",(stat $config_filename )[2] & 07777);
if($cf_perm ne "0600" ) {
	usage;
	print STDERR "[E] Your configuration file $config_filename has got the permission $cf_perm\n";
	print STDERR "    But this file needs 0600!\n";
	exit 1;
}

# Initialize configuration file
my $conf   = new Config::General($config_filename);
my %config = $conf->getall();

if (!defined($config{'global'}{'mysql_host'}) || $config{'global'}{'mysql_host'} eq "") {
	usage;
	print "[E] Configuration global->mysql_host not defined\n";
	exit 1;
}
if (!defined($config{'global'}{'mysql_user'}) || $config{'global'}{'mysql_user'} eq "") {
	usage;
	print "[E] Configuration global->mysql_user not defined\n";
	exit 1;
}
if (!defined($config{'global'}{'mysql_password'}) || $config{'global'}{'mysql_password'} eq "") {
	usage;
	print "[E] Configuration global->mysql_password not defined\n";
	exit 1;
}

my $dsn = "DBI:mysql:database=mysql:host=". $config{'global'}{'mysql_host'} ;

my $dbh = DBI->connect($dsn, $config{'global'}{'mysql_user'}, $config{'global'}{'mysql_password'})
	or die ("$0: Can't connect to database: $DBI::errstr\n");

# FLUSH PRIVILEGES first...
$dbh->do("FLUSH PRIVILEGES");

if (defined($arg_set_password)) {
	print "[I] set user password mode\n";
	if (!defined($arg_username) || $arg_username!~m/@/) {
		usage;
		print "[E] Please supply the username!\n";
		exit 1;
	}
	if ( ! -w  $config_filename) {
		usage;
		print "[E] The configuration file $config_filename is not writeable!\n";
		exit 1;
	}
	if (! check_username_exists_config($arg_username)) {
		usage;
		print "[E] The username $arg_username not found at $config_filename!\n";
		exit 1;
	}
	print "Please insert new password for $arg_username:\n";
	my $new_password;
	do {
		system ("stty -echo");
		chomp($new_password = <>);
		system ("stty echo");
		print "\n";
		if (length($new_password) < 4) {
			$new_password ="";
			print "[E] Password too short, please try it again Sam!\n";
		}
	}
	while($new_password eq "");
	if ( ! update_users_password_config($arg_username,password41($new_password))) {
		print "[E] Could not update new password for $arg_username to configuration file!\n";
		exit 1;
	}
	else {
		if ($conf->save_file($config_filename,\%config)) {
			print "[I] Successfully written new password for $arg_username to configuration file!\n";
		}
		else {
			print "[E] Could not update password on file for $arg_username\n";
			exit 1;
		}
	}
	exit;
}

foreach my $my_username (keys(%{$config{'users'}})) {
	print "[I] reading username " . $my_username . "...\n" if ($verbose);
	my %userinfos;
	if (! mysql_check_username($my_username)) {
		print STDERR "[E] The username $my_username contains illegal characters or it is too long!\n"; 
		next;
	}
	if (defined($config{'users'}{$my_username}{'host'}) &&
		!mysql_check_hostname($config{'users'}{$my_username}{'host'})) {
	}
	else {
		print STDERR "[E} The hostname of $my_username is not valid!\n";
		next;
	}
	$userinfos{'username'} = $my_username;
	$userinfos{'hostname'} = $config{'users'}{$my_username}{'host'};
	my $mysql_full_user = $userinfos{'username'} . '@' . $userinfos{'hostname'};

	### Check if user should be deleted
	if (defined($config{'users'}{$my_username}{'active'}) &&
		$config{'users'}{$my_username}{'active'}=~m/0/) {

		print "[I] The username $mysql_full_user will be DELETED!\n" if ($verbose);
		if (mysql_check_if_user_exists(\%userinfos) ) { 
			$dbh->do("REVOKE ALL PRIVILEGES, GRANT OPTION FROM ".
				$dbh->quote($userinfos{'username'}) . '@' .
				$dbh->quote($userinfos{'hostname'}) );
			if ( ! $dbh->do("DROP USER " . 
					$dbh->quote($userinfos{'username'}) . '@' .
					$dbh->quote($userinfos{'hostname'}) ) ) {
				print STDERR "[E] Could not delete $mysql_full_user: " . $dbh->errstr . "\n";
			}
			else {
				print "[I] The user $mysql_full_user was deleted.\n" if($verbose);
			}
		}
		else {
			print "[I] The user $mysql_full_user does not exists, so not deleted!\n" if ($verbose);
		}
		# we don't want to continue
		next;
	}

	# first check if user does not exists at mysql.user table
	if (! mysql_check_if_user_exists(\%userinfos)) {
		print "[I] The user $mysql_full_user does not exists - create it!\n" if ($verbose);
		if ( my $ret = mysql_create_user(\%userinfos)) {
			print STDERR "[E] Could not create user $mysql_full_user: $ret\n";
		}
		else {
			print "[I] The user $mysql_full_user successfull created!\n" if ($verbose);	
		}
	}


	# maintain global privs
	if (defined($config{'users'}{$my_username}{'global_privs'}) &&
		$config{'users'}{$my_username}{'global_privs'}=~m/../) {
		foreach my $priv (@mysql_priv_global) {
			my $global_priv_cur = 'N';
			my $priv_reg = $priv;
			$priv_reg=~s/_priv$//;
			if ($config{'users'}{$my_username}{'global_privs'}=~m/$priv_reg/i) {
				$global_priv_cur='Y';
				print "[I] User $mysql_full_user has got global $priv\n" if ($verbose);
				if ( my $ret = mysql_set_global_user_priv(\%userinfos,$priv,$global_priv_cur)) {
					print STDERR "[E] Could not set $priv for $mysql_full_user: $ret\n"; 
				}
			}
		}
	}
	else {
		# user has got no global privs - drop all global rights
		if (my $ret = mysql_drop_all_global_privs(\%userinfos)) {
			print STDERR "[E] Could not drop global privs for user $mysql_full_user: $ret\n";
		}
		else {
			print "[I] Drop all global privs for user $mysql_full_user\n" if ($verbose);
		}
	}
	# Update Password only, if password is set in configuration
	if (defined($config{'users'}{$my_username}{'password'}) &&
		$config{'users'}{$my_username}{'password'}=~m/../) {

		print "[I] Updating password for user $mysql_full_user\n" if ($verbose);
		my $mysql_users_passwd = $dbh->prepare("UPDATE user SET Password=? WHERE User=? AND Host=?");
		if ( ! $mysql_users_passwd->execute($config{'users'}{$my_username}{'password'}, 
				$userinfos{'username'},$userinfos{'hostname'})) {

				print STDERR "[E] Could not update password for $mysql_full_user: " . $dbh->errstr . "\n";
		}
		else {
			print "[I] Successfully updated password for $mysql_full_user\n" if ($verbose);
		}
	}

	# We delete all privileges from db,tables_priv,columns_priv
	if ( my $ret = mysql_drop_all_privs(\%userinfos)) {
		print STDERR "[E] Could not drop privileges: $ret\n";
		next;
	}
	else {
		print "[I] droped all privielges for $mysql_full_user\n" if ($verbose);
	}

	### Read Database privs
	foreach (keys(%{$config{'users'}{$my_username}{'db'}})) {
		if ($_!~/:/) {
			print STDERR "[E] Could not parse line '$_' for $mysql_full_user!\n";
			next;
		}
		my $db_priv_sql = "INSERT INTO db SET Host=?,User=?,Db=?, ";
		my ($database,$db_rights) = split(/:/,$_, 2);
		foreach my $priv (keys(%mysql_priv_db_rights)) {
			my $priv_db = $priv;
			$priv=~s/_priv$//g;
			if ($db_rights=~m/$priv/i) {
				$db_priv_sql .= $priv_db ."='Y',";
			}
			else {
				$db_priv_sql .= $priv_db ."='N',";
			}
		}
		$db_priv_sql=~s/,$//;
		my $update_db_priv = $dbh->prepare($db_priv_sql);
		if ( ! $update_db_priv->execute($userinfos{'hostname'},$userinfos{'username'},$database) ) {
			print STDERR "[E] Could not Grant $mysql_full_user DB: $database with $db_rights!\n";
			print STDERR "[E] MySQL said: ". $dbh->errstr . "\n";
		}
		else {
			print "[I] Grant $mysql_full_user DB: $database with $db_rights\n" if ($verbose);
		}
		$update_db_priv->finish;
	}
}



# all changes are mode, flush privileges
$dbh->do("FLUSH PRIVILEGES");
$dbh->disconnect;
### SUBS ####

sub usage {
	print "$0 - MySQL user management\n";
	print " $VERSION\n";
	print "\nUsage:\n";
	print " --verbose             - Verbose mode\n";
	print " --config=             - Configuration file\n";
	print " --user=               - Username for new password\n";
	print " --set-password        - Set an new password\n";
	print " --mysql-old-hashing   - Use the old mysql 3.x hashing\n";
}
sub mysql_check_username {
	my $username = $_[0];
	return 1 if (length($username) < 16 ) ;

	return 0;
}
sub mysql_check_hostname {
	my $hostname = $_[0];
	# FIXME: Add hostname valid check
	
	return 0;
}
sub mysql_check_if_user_exists {
	my %userinfos = %{$_[0]};
	my $ret = 0;
	my $mysql_user_exists = $dbh->prepare("SELECT 1 FROM user WHERE User=? AND Host=?");
	$mysql_user_exists->execute($userinfos{'username'}, $userinfos{'hostname'});
	if ($mysql_user_exists->rows == 1 ) {
		$ret = 1;
	}
	$mysql_user_exists->finish;
	return $ret;

}
sub mysql_create_user {
	my %userinfos = %{$_[0]};
	my $ret = 0;
	my $password = password41(mkpasswd());
	my $mysql_user_create = $dbh->prepare("INSERT INTO user (User,Host,Password) VALUES (?,?,?)");
	if (! $mysql_user_create->execute($userinfos{'username'},$userinfos{'hostname'},$password)) {
		$ret = $dbh->errstr;
	}
	undef $password;
	return $ret;
}
sub mysql_drop_all_global_privs {
	my %userinfos = %{$_[0]};
	my $ret = 0;
	foreach my $gbl_priv (@mysql_priv_global) {
		my $set_gbl_priv = $dbh->prepare("UPDATE user SET $gbl_priv = 'N' WHERE User=? AND Host=?");
		if (! $set_gbl_priv->execute($userinfos{'username'},$userinfos{'hostname'})) {
			$ret = $dbh->errstr;
		}
		$set_gbl_priv->finish;
	}
	return $ret;
}
sub mysql_set_global_user_priv {
	my %userinfos = %{$_[0]};
	my $priv      = $_[1];
	my $value     = $_[2];
	my $ret = 0;

	my $set_mysql_gbl = $dbh->prepare("UPDATE user SET $priv = ? WHERE User=? AND Host=?");
	if (! $set_mysql_gbl->execute($value,$userinfos{'username'},$userinfos{'hostname'})) {
		$ret = $dbh->errstr;
	}
	$set_mysql_gbl->finish;
	return $ret;
}
sub mysql_drop_all_privs {
	my %userinfos = %{$_[0]};
	my $ret = 0;
	my $sth;
	$sth = $dbh->prepare("DELETE FROM db WHERE Host=? AND User=?");
	if ( ! $sth->execute($userinfos{'hostname'}, $userinfos{'username'}) ) {
		$ret = $dbh->errstr;
	}
	$sth->finish;
	$sth = $dbh->prepare("DELETE FROM tables_priv WHERE Host=? AND User=?");
	if ( ! $sth->execute($userinfos{'hostname'}, $userinfos{'username'}) ) {
		$ret .= $dbh->errstr;
	}
	$sth->finish;
	$sth = $dbh->prepare("DELETE FROM columns_priv WHERE Host=? AND User=?");
	if ( ! $sth->execute($userinfos{'hostname'}, $userinfos{'username'}) ) {
		$ret .= $dbh->errstr;
	}
	$sth->finish;
	undef $sth;
	return $ret;
}
sub check_username_exists_config {
	my ( $username, $host)  = split(/@/ , $_[0]);
	foreach my $my_username (keys(%{$config{'users'}})) {
		if ( $my_username eq $username ) {
			if ($config{'users'}{$my_username}{'host'} eq $host ) {
				return 1;
			}
		}
	}

	return 0;
}
sub update_users_password_config {
	my ( $username, $host)  = split(/@/ , $_[0]);
	my $password = $_[1];
	foreach my $my_username (keys(%{$config{'users'}})) {
		if ( $my_username eq $username ) {
			if ($config{'users'}{$my_username}{'host'} eq $host ) {
				$config{'users'}{$my_username}{'password'}= $password;
				return 1;
			}
		}
	}
	return 0;
}

__END__

=head1 NAME

user_mysql_mgmt.pl - MySQL user management script

=head1 SYNOPSIS

 user_mysql_mgmt.pl
 user_mysql_mgmt.pl --config=

=head1 DESCRIPTION

You have to invoke user_mysql_mgmt.pl always with B<--config=>. The script will parse the
configuration file and add/delete or modify the MySQL user tables in the B<mysql> database.

=head1 OPTIONS

 The options which apply to the user_mysql_mgmt.pl command are:

=over 8

=item B<--verbose>

 Verbose: print information messages about all actions.


=item B<--help>,B<-h>

 Help: print help and exit.


=item B<--config=>

 Config: Supply an configuration to the script.


=item B<--set-password>

 Set-Password: Set an new hashed password in the configuration file.
 You have to supply with B<--user=> the username.


=item B<--user=>

 User: Supply an username for changing the password. Please see B<--set-password>.


=item B<--mysql-old-hashing>

 MySQL-Old-Hashing: Use the old MySQL 3.x password hashing.

=back

=head1 CONFIGURATION

The script needs one MySQL user with global privileges: Reload,Grant,Create user. This user needs also SELECT,INSERT,UPDATE,DELETE privileges at database mysql.*.


You can use the following GRANT statement:

  GRANT RELOAD , CREATE USER ON * . * TO 'myadmin'@'localhost' IDENTIFIED BY 'your-password'  WITH GRANT OPTION ;



  GRANT SELECT,INSERT,UPDATE,DELETE ON `mysql` . * TO 'myadmin'@'localhost';


The username and password of your control user needs to be stored in your configuration file.


=head1 SAMPLE COMMANDS


=over 8


=item B<./user_mysql_mgmt.pl --config=user_mysql_mgmt.conf --set-password --user=foo@locahost>

   This command will update the hashed password for user foo@localhost in the configuration file.
   Please note, you have run after this update password command the normal run with:

      ./user_mysql_mgmt.pl --config=user_mysql_mgmt.conf

   This will update the new password into MySQL tables.

=back

=head1 SAMPLE CONFIGURATION FILE

   # global settings
   <global>
       # username,password for the control user
       mysql_host   localhost
       mysql_password   your-password
       mysql_user   admin
   </global>
   <users>
       <foo>
           host   localhost
	   # If password is not supplied, password will not be updated by the script!
           password   *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5
           <db>
                   foo:select,update,insert
		   one_more_db:delete,drop,show_view
           </db>
       </foo>
       <jonas>
	   # put active 0: user will be deleted on the next run!
           active   1
           <db>
           </db>
           global_privs   select,drop
           host   %
           password
       </jonas>
   </users>


=head1 ENVIRONMENT

 No environment variables are used.

=head1 LICENSE AND COPYRIGHT

 Copyright (c) 2009 Jonas Genannt

 This script is free software; you can redistribute it and/or
 modify it under the terms of GPL.

=head1 AUTHOR

 Jonas Genannt <jonas@brachium-system.net>

=cut