Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
I'll give this a +1 because it should encourage others to be more innovative with third-party products using embedded MySQL !! –  RolandoMySQLDBA Sep 15 '11 at 15:13
add comment

1 Answer 1

up vote 3 down vote accepted

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 !!!

share|improve this answer
    
very, very helpful-- the windows service is called ScholasticMySQL and it executes C:\ScholasticEE\SAM_Server\mysql\bin\mysqld-nt.exe --defaults-file=C:\ScholasticEE\SAM_Server\mysql\my.cnf ScholasticMySQL could i stop the database and then restart it by going to the command line and executing the same string, but appending --skip-grant-tables to it? –  Andrew Sep 12 '11 at 20:03
    
That would work in Linux for sure. I have never tried that with MySQL for Windows. Go ahead and try and see if Windows complains or not. In your case, you run net stop ScholasticMySQL. –  RolandoMySQLDBA Sep 12 '11 at 20:39
    
worked perfectly -- modfied the my.cnf file with --skip-grant-tables and was able to then insert a new user. –  Andrew Sep 15 '11 at 15:08
    
Don't forgot to remove or comment out skip-grant-tables !!! –  RolandoMySQLDBA Sep 15 '11 at 15:14
    
yes! only using skip-grant-tables the one time to bring in the new user. then brought it back up w/ grant tables as normal. –  Andrew Sep 19 '11 at 21:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.