Mysql – How to create thesql user with CREATE USER grant but without access to new databases

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

It would be something like this :

GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

To see a list of the privileges that have been granted to a specific user:

select * from mysql.user where User='username';

OR

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';