Monday Apr 23, 2007

JDBCRealm in GlassFish with MySQL

In these few months, there were several discussions of using GlassFish JDBCRealm with MySQL. In this blog, I will share my experience about using GlassFish JDBCRealm with MySQL.

  1. Download the MySQL Community Server. I have downloaded the Solaris 10 (x86, 32 bit TAR package), version 5.0.37, of the "MySQL Community Server".

  2. Expand the download file.

    gunzip mysql-5.0.37-solaris10-i386.tar.gz
    tar xf mysql-5.0.37-solaris10-i386.tar

  3. cd mysql-5.0.37-solaris10-i386

    and read INSTALL-BINARY.

  4. Set up the grant table.

    scripts/mysql_install_db

  5. Start the MySQL server.

    bin/mysqld_safe

  6. Set a password for the MySQL "root" user

    bin/mysqladmin -u root password YOUR_PASSWORD

  7. Create database and table. The following is a sample command.
    bin/mysql -u root --password=YOUR_PASSWORD
    
    create database database01;
    use database01;
    create table usertable(userid varchar(10) primary key, password varchar(32) not null);
    create table grouptable(userid varchar(10) not null, groupid varchar(20) not null, primary key (userid, groupid));
    alter table grouptable add constraint FK_USERID foreign key(userid) references usertable(userid);
    commit;
    grant all privileges on *.* to 'root'@'YOUR_HOST' identified by 'YOUR_PASSWORD' with grant option;
    
    Note that you may like to replace YOUR_PASSWORD and YOUR_HOST in above.

  8. Populate user, group and passwor data. For the purpose of testing the database, you may try to use clear text password first as follows:
    insert into usertable values ('webuser', 'webuser');
    insert into grouptable values ('webuser', 'employee');
    
    For MD5, please take a look at another blog on JDBCRealm.

  9. Download the JDBC driver from Connectors > Connector/J . I have downloaded mysql-connector-java-5.0.5-bin.zip

  10. Unpack the package and copy the JDBC driver to $GLASS_HOME/lib.

    unzip mysql-connector-java-5.0.5-bin.jar
    cd mysql-connector-java-5.0.5
    cp mysql-connector-java-5.0.5-bin.jar $GLASSFISH_HOME/lib

  11. Restart the GlassFish server in order to pick up the JBDC driver.

  12. Create a Connector pool in Admin Console as follows:

    NameMySQLPool
    Resource Typejavax.sql.DataSource
    Database VendorMySQL

    then click "Next" and add the following properties:

    serverNameYOUR_HOST
    port3306
    databaseNamedatabase01
    userroot
    passwordYOUR_PASSWORD

    Note that different versions of the JDBC driver may have different properties. You may need to check the readme file there. Furthermore, you may need to remove extra default properties from Admin Console.

  13. Create a DataSource jdbc/mysql associated with the above pool.

  14. Create a JDBCRealm, named jdbcrealm with the following properties:

    datasource-jndijdbc/mysql
    user-tableusertable
    user-name-columnuserid
    password-name-columnpassword
    group-tablegrouptable
    group-name-columngroupid
    jaas-contextjdbcRealm
    digest-algorithmnone

    Note that if you are using MD5 for password data, then you need to set value of digest-algorithm to MD5.

  15. Now a JDBCRealm is ready and it can be used by specifying it in deployment descriptors. If there is anything wrong and cannot authenticate, then one can turn on security log to FINE level and check if there is any exception in server.log.
About

Shing Wai Chan

Search

Categories
Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today