Check if database exists while trying to grant permissions

Question

I am trying to build a few functions into a perl script to create, delete and grant permissions on mysql databases.

At the moment, I try to grant permissions for a user to specified database, like this:

use DBI;
my $dbname=$_[1];
my $dbh = DBI->connect("dbi:mysql:", $sqluser,$sqlpass) or die "Unable to connect: $DBI::errstr\n";
.....
my $username=@unp[0];
my $dbname=@unp[1];         
my $db_com="GRANT ALL ON ".$dbname.".* TO '".$username."'\@'localhost'";
my $sth = $dbh->prepare($db_com);
$sth->execute 
or die "SQL Error: $DBI::errstr\n";
print "Permissions granted\n";

This works if the user exists.

While trying similiar code for CREATE USER, and DROP USER, it works efficiently, creating, or deleting them as requested, or throwing mysql syntax errors if the user doesnt exist. But while granting privileges, I am set back by the fact that the command executes even if the database does not exist.

In fact on mysql prompt, if the sql query is executed, it returns:

Query OK, 0 rows affected (0.00 sec)

I need a way to discover whether the database does not exist, and prompt the user to provide a correct database name.

Have you looked at this? http://stackoverflow.com/questions/7364709/bash-script-check-if-mysql-database-exists-peform-action-based-on-result — Populus, Apr 16 '13 at 13:52
I have included a few more lines to explain what I'm doing. I'd like to use the perl DBD::mysql driver to do this. — Joel G Mathew, Apr 16 '13 at 13:56
Have you taken a look here: http://stackoverflow.com/questions/838978/how-to-check-if-mysql-database-exists#838993 — Axeman, Apr 16 '13 at 14:02
@Axeman, I did. But CREATE DATABASE is fine in my script. It returns an error if the database exists. But GRANT does not, even if the database does not exist. — Joel G Mathew, Apr 16 '13 at 14:04
@Droidzone, did you see the SELECT statement above it? If you ask me, creating an empty database so that the permissions are correct is a little backward. — Axeman, Apr 16 '13 at 14:55

score 1 · Answer 1 · answered Apr 16 '13 at 14:25

1

do show databases as a query first and look for the name in it before doing the GRANT

It's perfectly acceptable to grant privileges on a non existent database, all that's happening is that tables in the mysql database are being written to

answered Apr 16 '13 at 14:25

Vorsprung

32,923
5
39
63

Indeed, it seems to be the way mysql does things. GRANT and REVOKE always seem to return 0 rows affected even if databases did exist. – Joel G Mathew Apr 16 '13 at 14:27

Check if database exists while trying to grant permissions

1 Answers1