Example 7 55   MySQL host access data

mysql> select user, host, select_priv, insert_priv from user;

+         +                          +             +             +

| user    | host                     | select_priv | insert_priv |

+         +                          +             +             +

|         | localhost                | N           | N           |

| Michael | %.ibm.com 

______________

 | Y 

_________

 | N           |

| Klaus   | %                        | N           | Y 

_________

 |

| Rakesh 

_

| %.in.ibm.com 

___________

 | Y 

_________

 | Y 

_________

 |

| itsosj  | localhost                | Y 

_________

 | N           |

| itsosj  | %.ibm.com 

______________

 | Y 

_________

 | Y 

_________

 |

+         +                          +             +             +

This information has to be ported into a DB2 table. When a user attempts to 

access the data in the DB2 database, the application will verify every user's 

database access rights along with the host system information where one is 

connecting from.

Two tables are needed for our DB2 conversion: one to store the user privileges 

information ported from MySQL and one is a working table. The table definitions 

and some sample values are shown in Example 7 56.

Example 7 56   Creation of the tables for host authentication

   script for creating the tables used by our example application

   connect to the database

connect to SAMPLE user itsosj using itsosj; 

   table ACCESSLIST

   it stores access rights for specific users connecting from specific hosts

   remark: there should be different access flags for different functions

   fields:

   username, whom access to the function should be granted

   hostname or ip address, from which the user must connect

   select access flag (Y/N), if SELECT is granted

   insert access flag (Y/N), if INSERT is granted

drop table ACCESSLIST;

create table ACCESSLIST (

  USERNAME

varchar(8),

  HOST

varchar(30),

  ACCESS_SEL  char(1),

__

ACCESS_INS  char(1)

);

222 

MySQL to DB2 UDB Conversion Guide