• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

MySQL modify access privileges or create new users

I am attempting to create script installation Wizard, that would guide administrator through installation steps and as one of the steps  configures new MySQL database to be used by the script.
I have no problem in creating a new database, however it is created without any access rights for any user, including root.
I need to modify access privileges for the configured user to allow this new database access from the Web host, where the Wizard was executed.

This is equivalent to  MySQL command:
GRANT ALL ON dbname.* TO root@'webserver' IDENTIFIED BY 'password' WITH GRANT OPTION;      

What DBI or DBD::MySQL command should I use for this?

Thank You.
0
sstouk
Asked:
sstouk
  • 5
  • 2
1 Solution
 
ozoCommented:
$dbh->query(qq(GRANT ALL ON dbname.* TO root@'webserver' IDENTIFIED BY 'password' WITH GRANT OPTION));
0
 
ozoCommented:
#sorry, that's for Net::MySQL, for DBD::mysql it looks like you'd want
$dbh->do(qq(GRANT ALL ON dbname.* TO root@'webserver' IDENTIFIED BY 'password' WITH GRANT OPTION));
0
 
sstoukAuthor Commented:
Wait...
$dbh is generally an example of a database handle.
Do you mean a Driver Handle ($drh) and not a Database Handle ($dbh) ?
I realize that it is just a variable but it makes things clear....
$drh = DBI->install_driver("mysql");
$drh->query(qq(GRANT ALL ON dbname.* TO root@'webserver' IDENTIFIED BY 'password' WITH GRANT OPTION));

???
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sstoukAuthor Commented:
Or the second query ...

$drh = DBI->install_driver("mysql");
$drh->do(qq(GRANT ALL ON dbname.* TO root@'webserver' IDENTIFIED BY 'password' WITH GRANT OPTION));

???
0
 
sstoukAuthor Commented:
#################################
$drh = DBI->install_driver("mysql");
my($Command) = "GRANT ALL ON $main::Config{CPADbDatabaseName}.* TO $main::Config{CPADbUser}@\'*\' IDENTIFIED BY \'$main::Config{CPADbPassword}\' WITH GRANT OPTION";
$drh->query($Command);
#################################
RESULT:
Can't locate object method "query" via package "DBI::dr" (perhaps you forgot to load "DBI::dr"?)


#################################
$drh = DBI->install_driver("mysql");
my($Command) = "GRANT ALL ON $main::Config{CPADbDatabaseName}.* TO $main::Config{CPADbUser}@\'*\' IDENTIFIED BY \'$main::Config{CPADbPassword}\' WITH GRANT OPTION";
$drh->do($Command);
#################################
RESULT:
Can't locate object method "do" via package "DBI::dr" (perhaps you forgot to load "DBI::dr"?)

Perhaps I should connect a Database Handle to mysql Table?
Shouldn't all the schema be located there and it might be as simple as adding a row to the configuration mysql database?

0
 
sstoukAuthor Commented:

I think I did this a few years ago already and you are correct.

I have to create a $dbh Database Handle to "mysql" Database and manipulate the values in tables_priv Table by modifying Rights for Specific User.

Git the idea.
thank You.
0
 
sstoukAuthor Commented:
Yes.
It works well now.
Thank Again.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now