insert some sample values, according to the MySQL values (see above)

insert into ACCESSLIST values( Michael ,  %.ibm.com ,  Y ,  N );

insert into ACCESSLIST values( Klaus ,  % ,  N ,  Y );

insert into ACCESSLIST values( Rakesh ,  %.in.ibm.com ,  Y ,  Y );

insert into ACCESSLIST values( itsosj ,  localhost ,  Y ,  N );

insert into ACCESSLIST values( itsosj ,  %.ibm.com ,  Y ,  Y );

   table APPLACCESS

   it stores the info about users and their host asking for access

   this table is filled automatically by the sample application

   fields:

   username, who asks for access to the function

   hostname, from which the user connects

   ip address, from which the user connects

   timestamp, when the user asks for access

drop table APPLACCESS;

create table APPLACCESS (

  USERNAME varchar(8),   

  HOSTNAME varchar(30),  

  IPADDR   varchar(30),  

  TS       timestamp     

);

The tables 

ACCESSLIST

 and 

APPLACCESS

 are used by the authentication 

mechanism:

  ACCESSLIST table

This table stores all the combinations of users and hosts (either name or 

IP address) and specific database access privileges are granted to the user 

and host combination. In our example, we control two access rights: 

SELECT

and 

INSERT

, so that we have two access fields 

ACCESS_SEL

 and 

ACCESS_INS

. In 

a real world application more functions would probably be controlled, so this 

table should be expanded to have one 

ACCESS

 field for each function to be 

controlled based on the corresponding MySQL privilege.

We insert some sample values into this table for demonstration purposes.

  APPLACCESS table 

This table is filled by the authentication application during runtime. When a 

user asks for access, the application inserts the user ID, the host name, and 

the IP address from where the user connects. The timestamp is used as a 

key in this table as records are not deleted from it.

The application code is listed in Example 7 57; remember that the code is just for 

demonstration purposes.

 Chapter 7. Application porting 

223