Mysql – How to gain access to embedded MySQL database via localhost command line

MySQLpermissions

We run an application (Windows 2003 server) and would like to have database connectivity — contacted the company and they do not have any documentation on how to do so. A quick glance at the installation and the services running shows that the data lives in an embedded MySQL installation. Looking at some of the installation scripts reveals a login and password to some of the database tables…but no credential that gives full permissions to create/modify users.

Ultimate goal is to:

  1. modfiy the my.cnf file to allow remote connections, then
  2. grant access to a user who could connection into the database
  3. make a dblink in our primary reporting database (Oracle 11g) to this MySQL installation

I see some writeups on how to reset root:
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

but I am hesitant to do so given that I don't want to run any risk of disrupting the application that uses this MySQL installation as a data store.

So, question: Is there any reason why I couldn't use a similar approach as in the documentation above (especially option C – connecting with skip grant) but create a new user with the permissions I need (as opposed to altering the root user, as they do above?)

If it's relevant, mysql shows a server version of 4.1.18-pro-nt.

Thanks!

Best Answer

WOW that's a very old version of MySQL.

Here is mysql.user from version 4.1.20:

mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | varchar(60)                       |      | PRI |         |       |
| User                  | varchar(16)                       |      | PRI |         |       |
| Password              | varchar(41)                       |      |     |         |       |
| Select_priv           | enum('N','Y')                     |      |     | N       |       |
| Insert_priv           | enum('N','Y')                     |      |     | N       |       |
| Update_priv           | enum('N','Y')                     |      |     | N       |       |
| Delete_priv           | enum('N','Y')                     |      |     | N       |       |
| Create_priv           | enum('N','Y')                     |      |     | N       |       |
| Drop_priv             | enum('N','Y')                     |      |     | N       |       |
| Reload_priv           | enum('N','Y')                     |      |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     |      |     | N       |       |
| Process_priv          | enum('N','Y')                     |      |     | N       |       |
| File_priv             | enum('N','Y')                     |      |     | N       |       |
| Grant_priv            | enum('N','Y')                     |      |     | N       |       |
| References_priv       | enum('N','Y')                     |      |     | N       |       |
| Index_priv            | enum('N','Y')                     |      |     | N       |       |
| Alter_priv            | enum('N','Y')                     |      |     | N       |       |
| Show_db_priv          | enum('N','Y')                     |      |     | N       |       |
| Super_priv            | enum('N','Y')                     |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     |      |     | N       |       |
| Execute_priv          | enum('N','Y')                     |      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     |      |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     |      |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob                              |      |     |         |       |
| x509_issuer           | blob                              |      |     |         |       |
| x509_subject          | blob                              |      |     |         |       |
| max_questions         | int(11) unsigned                  |      |     | 0       |       |
| max_updates           | int(11) unsigned                  |      |     | 0       |       |
| max_connections       | int(11) unsigned                  |      |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.00 sec)

Since we are talking Windows, there should be a service called MySQL or some other name pointing to C:\Program Files\MySQL. If that's not the folder, search the entire disk for my.ini. Once you found my.ini, here is a sure fire way to insert a new user called 'oracleclient':

Step 01) Shutdown Application

Step 02) net stop mysql

Step 03) Add skip-grant-tables to my.ini

Step 04) net start mysql

Step 05) run 'mysql' at the DOS prompt (no password needed)

Step 06) From mysql prompt, run this INSERT statement

INSERT INTO mysql.user SET
Host='IP of Oracle Server',
User='oracleclient',
Password=PASSWORD('whateverpassword'),
Select_priv='Y',
Insert_priv='Y',
Update_priv='Y',
Delete_priv='Y',
Create_priv='Y',
Drop_priv='Y',
Reload_priv='Y',
Shutdown_priv='Y',
Process_priv='Y',
File_priv='Y',
Grant_priv='Y',
References_priv='Y',
Index_priv='Y',
Alter_priv='Y',
Show_db_priv='Y',
Super_priv='Y',
Create_tmp_table_priv='Y',
Lock_tables_priv='Y',
Execute_priv='Y',
Repl_slave_priv='Y',
Repl_client_priv='Y';

Step 07) exit mysql

Step 08) net stop mysql

Step 09) net start mysql (close your eyes and hit enter)

Step 10) See if everything works !!!

You should be able to connect from the oracle server.

BUT WAIT !!!

What if there is no my.ini and the application has the setting only ???

Here is something a little more daring:

Step 01) Install the same version (MySQL 4.1.18) onto another PC (Server2)

Using the wizard, this should place the MySQL binaries: C:\Program Files\MySQL\MySQL 4.1. This would be considered the basedir. The subfolder data\mysql would be the home of the mysql schema.

Step 02) Create my.ini in basedir of Server2

Step 03) Copy user.frm, user.MYI, user.MYD from the mysql schema of Server1 into the same place in Server2

Step 04) Perform Steps 3-8 from the first plan

Step 05) Make sure you have a backup copy of the mysql schema of Server1

Step 06) Copy user.frm, user.MYI, user.MYD from the mysql schema of Server2 into the same place in Server1

Step 07) net start mysql on Server1 and the application (close your eyes and hit enter)

Step 08) See if everything works !!!

Give it a Try !!!

Related Question