MySQL Forums
Forum List  »  Perl

Script automated administration og DB's and Users.
Posted by: Kevin Rasmussen
Date: November 05, 2008 02:20AM

Hi all.
Im new here, so ahead of this post I will start by apologizing for not reading through alot of posts to see if maybe there is an answer to my question.

Now to the serious part.
I have four questions in one.

FIRST QUESTION:
---------------

Via a perl script, have I added a user(actually two, with the same name, just attached to different addresses). Below here you can se my code:

$con->query("CREATE USER '".$NEWADMIN."'\@'".$server."' IDENTIFIED BY '".$DBADMINPASS."';");
$con->query("GRANT SELECT,INSERT,UPDATE,DELETE ON ".$dbname.".* TO '".$NEWADMIN."'\@'".$server."';");
$con->query("GRANT SELECT,INSERT,UPDATE,DELETE ON ".$dbname.".* TO '".$NEWADMIN."'\@'%';");

Now. Another part of my code is building the username from an E-Mail replacing both @ and dot to underscore(name_domain_tld(.com etc)). But this code didnt work(unfortunately). So now I have two users with usernames xx@xxx.xx. They are created but havent got any privileges to any db because when I try to do:
REVOKE ALL PRIVILEGES ON *.* FROM 'xx@xxx.xx'; it says query ok, 0 rows affected.
I then trys to drop user, but I cant.
Can u help me?


SECOND QUESTION:
----------------

I don't know if anyone knows about IDM but it is via IDM Scriptdriver that I executes the PERL script.
The scenario is like this:

The customer will be using the MySQL database with homepages as they will be both ISP and Webhotel providers. So when a new customer is created in eDir(Novell), IDM will automatically invoke the script that will add a new domain and database. On the database server it meens that one database with the name of the domain but with underscore instead of dot will be created. Next a new Database administrator will be created with the code as shown above in question number one.
So actually my real need is to be verifyed that the code is correct.

THIRD QUESTION:
---------------

When there is just the smallest modification on anything that have influende on MySQL(new db admin, etc except password. This has its own script), a modify command will be sent to the mysql server and a modify script will be invoked. Now. I cant just ask for the paramters been modified only, so I have update all the information in one time. That unfortunately meens that I have to remove the old admin and create him again with his privileges. Another action COULD be to disable access to the database(revoke the privileges for the db admin, but not drop him). So I have made a code looking like this:

my $con = Mysql->connect($host, "mysql", $admin, $adminpass);

if($DISABLED eq "true")
{
my $query = $con->query("SELECT User FROM db WHERE Db = '".$dbname."';");
while(my %hash = $query->fetchhash)
{
my $user = $hash{0};
$con->query("REVOKE ALL PRIVILEGES ON ".$dbname.".* FROM '".$OLDADMIN."'\@'%';'");
$con->query("REVOKE ALL PRIVILEGES ON ".$dbname.".* FROM '".$OLDADMIN."'\@'".$server."';'");
$con->query("FLUSH PRIVILEGES;");
}
}
else
{
my $query2 = $con->query("SELECT User FROM db WHERE Db = '".$dbname."'");
while(my %hash = $query2->fetchhash)
{
my $user = $hash{0};
$con->query("DROP USER ".$user.";");
$con->query("FLUSH PRIVILEGES;");
}
$con->query("CREATE USER '".$NEWADMIN."'\@'".$server."' IDENTIFIED BY '".$DBADMINPASS."';");
$con->query("GRANT SELECT,INSERT,UPDATE,DELETE ON ".$dbname.".* TO '".$NEWADMIN."'\@'".$server."';");
$con->query("GRANT SELECT,INSERT,UPDATE,DELETE ON ".$dbname.".* TO '".$NEWADMIN."'\@'%';");
}

Now. This code doesnt seem to work 100%, because no database is created, but it sure creates an admin.
Can you verify for me that I am doing this right? Or even if I am thinking right but doing wrong?


FOURTH QUESTION:
----------------

Deletion of a database(inclusive the admin).
A simple question so i just post the code right away:

my $con = Mysql->connect($host, "mysql", $admin, $adminpass);
my $query = $con->query("SELECT User FROM Db WHERE Db = '".$dbname."';");
while(my %hash = $query->fetchhash)
{
my $user = $hash{0};
$con->query("REVOKE ALL PRIVILEGES ON ".$dbname.".* FROM '".$user."'\@'%';");
$con->query("REVOKE ALL PRIVILEGES ON ".$dbname.".* FROM '".$user."'\@'".$server."';");
$con->query("FLUSH PRIVILEGES;");
$con->query("DROP USER ".$user.";");
}

Again, can you verify for me that this SHOULD work?

Options: ReplyQuote


Subject
Written By
Posted
Script automated administration og DB's and Users.
November 05, 2008 02:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.