Perl and MySQL privileges

fpi has asked for the wisdom of the Perl Monks concerning the following question:

I'm finding the MySQL privilege system to be rather confusing when it comes to how my Perl program relates to it.

I have a Perl program accessing MySQL on the same (Unix) machine, and I've already determined that MySQL is running, and that a particular database does NOT exist, so now I am attempting to create it (assume variables are defined accordingly):

my $drh=DBI->install_driver('mysql');
my $rc = $drh->func('createdb',$DATABASE,$HOST,$USER,$PASSWORD,'admin'
+) or die "Can't create\n";
[download]

Now I understand that when MySQL checks authentication against the mysql:user table, the Hostname takes a priority match over User and Password (which drives me nuts). My question is this: does this mean that I can specify whatever $HOST I want, regardless of whatever host I am really using, in order to match an entry in the mysql:user table?

And is connecting to MySQL like this independent of whether or not the Perl program is run as root or with root ownership? I am asking because if I get the program to work on the command line, then I get privilege errors when running the program as a CGI using Apache. I think the confusion may arise when using

use Sys::Hostname;
$HOST=hostname();
[download]

sometimes $HOST defaults to 'localhost', other times it defaults to the actual hostname of the machine. Should I just hard code 'localhost' as my host into the DBI commands?

I've read man DBI, man DBD::mysql, the Perl chapters of O'Reilly's MySQL book, and the MySQL site. I was hoping your experience can explain it to me or give me some insight into the 'proper' way of setting up privileges in running a perl program to do some admin work (create a non-existing db, create and alter tables) in MySQL.

By the way, my Perl program was using MySQL just fine. It wasn't until I installed the program on another machine did I realize these privileges are more complicated that I had expected.... I'm confused.....

Comment on Perl and MySQL privileges Select or Download Code

Replies are listed 'Best First'.
Re: Perl and MySQL privileges by chromatic (Archbishop) on Mar 21, 2001 at 01:41 UTC
I'm unfamiliar with the syntax you're using. As far as I know, $HOST is for the host of the machine on which the MySQL server runs. Also, as far as I know, the server determines your hostname automatically during connection, checking it against the grant table. Privileges to connect to a MySQL server exist independently of the user/group of the script -- they're determined by the hostname from which to connect to the server and the username and password provided when connecting. With all that said, I usually use a connect string something like: `use DBI; my $host = "dbserver"; my $dsn = "DBI:mysql" . $host; my $user = "chromatic"; my $pass = ''; my $dbh = DBI->connect($dsn, $user, $pass);` [download] If this is inappropriate for your purposes, let me know. I am slightly confused by your question, and offer my apologies if I have misunderstood.	[reply] [d/l]
Re: Followup on Perl and MySQL privileges by fpi (Monk) on Mar 21, 2001 at 13:37 UTC
Followup: I was enlightened with the following: Thanks to chromatic and yakko, I learned that you can 'connect' to MySQL using DBI without specifying a database. Nowhere in the DBI man pages or O'Reilly's MySQL book does it say that, so I assumed you had to specify one. However, I modified chromatic's line above to go along with the syntax of the DBI man page: `my $dsn = "DBI:mysql:host=$host";` and it works fine. chromatic's explanation cleared up the heart of my confusion: $HOST is the host of MySQL you want to connect to, NOT the host of the user (which is automatically determined). So yes, it is safe for me to hard-code 'localhost' into a script that will be installed on several machines, each one with their own MySQL database. Previously I had thought that I had to use the `hostname()` function to determine what machine the script was on and send that name to MySQL for login. LD2 mentioned that MySQL should default to 'localhost', so I just tested this - if you change the same line to `my $dsn = "DBI:mysql:";` (leave the trailing colon), it defaults to localhost, and it works just fine. Thanks for your help. Oh, and about my syntax, I am just following the recommendation straight out of the DBI man page. I used the `install_driver` method because, like I said, I didn't know how else to connect to MySQL without connecting to a database...	[reply] [d/l] [select]
Re: Perl and MySQL privileges by yakko (Friar) on Mar 21, 2001 at 11:06 UTC
Supposing your MySQL user is GRANTed the CREATE privilege, and you connect as per chromatic's post above, you can then do something like: `my $sth=$dbh->prepare('CREATE DATABASE IF NOT EXISTS foo'); $sth->execute();` [download] Your MySQL user does need to have had CREATE privileges GRANTed to them on a global level. -- Me spell chucker work grate. Need grandma chicken.	[reply] [d/l]
Re: Perl and MySQL privileges by coreolyn (Parson) on Mar 21, 2001 at 19:19 UTC
There is a file in your mysql/bin file called mysql_setpermission that is a Perl script that has similar functionality to what you are attempting to do. You may want to study that code for ideas coreolyn	[reply]