appserver/tests/appserv-tests/devtests/jdbc/v3/SetDerbyAuthentication.java

/*
 * Copyright (c) 2017, 2018 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0, which is available at
 * http://www.eclipse.org/legal/epl-2.0.
 *
 * This Source Code may also be made available under the following Secondary
 * Licenses when the conditions for such availability set forth in the
 * Eclipse Public License v. 2.0 are satisfied: GNU General Public License,
 * version 2 with the GNU Classpath Exception, which is available at
 * https://www.gnu.org/software/classpath/license.html.
 *
 * SPDX-License-Identifier: EPL-2.0 OR GPL-2.0 WITH Classpath-exception-2.0
 */

import javax.sql.PooledConnection;
import java.sql.Connection;
import javax.sql.DataSource;
import org.apache.derby.jdbc.EmbeddedDataSource;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Enables Authentication for Embedded Derby Mode.
 * Enables Authentication of certain users for use in the JDBC devtests.
 *
 * @author shalini
 */
public class SetDerbyAuthentication {

    private static String DATABASES_HOME = "/tmp/jdbc_devtests/databases";
    private static String RECONFIG_DB_NAME = DATABASES_HOME + "/reconfig-db";
    private static String SAMPLE_DB_NAME = DATABASES_HOME + "/sample-db";
    private static String SUN_APPSERV_SAMPLES_DB = DATABASES_HOME + "/sun-appserv-samples";

    public static void main(String[] args) {
        setCommonDBAuthentication();
        setReconfigDBAuthentication();
        createReconfigTables();
    }

    private static void setCommonDBAuthentication() {
        Statement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        try {
            DataSource ds = initializeDataSource();
            con = (Connection) ds.getConnection();
            stmt = con.createStatement();
            //Execute CallableStatement to enable authentication
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.connection.requireAuthentication', 'true')");
               rs = stmt.executeQuery(
                    "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" +
                    "'derby.connection.requireAuthentication')");
            rs.next();
            System.out.println("Authentication set to " + rs.getString(1));

            //Setting Usernames and passwords
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.user.dbuser', 'dbpassword')");
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.user.DBUSER', 'DBPASSWORD')");
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.user.APP', 'APP')");
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.user.DERBYUSER', 'DERBYPASSWORD')");
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static void setReconfigDBAuthentication() {
        Statement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        try {
            DataSource ds = initializeReconfigDataSource();
            con = (Connection) ds.getConnection();
            stmt = con.createStatement();
            //Execute CallableStatement to enable authentication
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.connection.requireAuthentication', 'true')");
               rs = stmt.executeQuery(
                    "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" +
                    "'derby.connection.requireAuthentication')");
            rs.next();
            System.out.println("Authentication set to " + rs.getString(1));

            //Setting Usernames and passwords
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.user.ruser', 'rpassword')");
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.user.RUSER', 'RPASSWORD')");
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }

    private static void setSampleDBAuthentication() {
        Statement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        try {
            DataSource ds = initializeSampleDataSource();
            con = (Connection) ds.getConnection();
            stmt = con.createStatement();
            //Execute CallableStatement to enable authentication
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.connection.requireAuthentication', 'true')");
               rs = stmt.executeQuery(
                    "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" +
                    "'derby.connection.requireAuthentication')");
            rs.next();
            System.out.println("Authentication set to " + rs.getString(1));

            //Setting Usernames and passwords
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.user.APP', 'APP')");
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                                                    "'derby.user.app', 'app')");
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }

    private static void createReconfigTables() {
        Statement stmt = null;
        Statement stmt1= null;
        String reconfigTable = "reconfigTestTable";
        String commonTable = "sampleTable";
        String columnName = "ID";
        Connection con = null;
        Connection con1 = null;
        try {
            DataSource ds = initializeReconfigDataSource();
            DataSource ds1 = initializeSampleDataSource();
            con = (Connection) ds.getConnection();
            con1 = (Connection) ds1.getConnection();
            stmt = con.createStatement();
            stmt1 = con1.createStatement();
            String query = "create table " + reconfigTable + "(" + columnName + " char(50))";
            stmt.executeUpdate(query);
            query = "create table " + commonTable + "(" + columnName + " char(50))";
            stmt1.executeUpdate(query);
            System.out.println("Created tables : reconfigTestTable and sampleTable");
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if(stmt1 != null) {
                    stmt1.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

            try {
                if (con != null) {
                    con.close();
                }
                if(con1 != null) {
                    con1.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * Sets the EmbeddedDataSource properties corresponding to the pool configuration
     * created for running the JDBC reconfig devtests.
     */
    private static org.apache.derby.jdbc.EmbeddedDataSource initializeReconfigDataSource() {
        EmbeddedDataSource ds = new EmbeddedDataSource();
        ds.setUser("ruser");
        ds.setPassword("rpassword");
        ds.setDatabaseName(RECONFIG_DB_NAME);
        ds.setConnectionAttributes("create=true");
        return ds;
    }

    /**
     * Sets the EmbeddedDataSource properties corresponding to the pool configuration
     * created for running the JDBC reconfig devtests.
     */
    private static org.apache.derby.jdbc.EmbeddedDataSource initializeSampleDataSource() {
        EmbeddedDataSource ds = new EmbeddedDataSource();
        ds.setUser("APP");
        ds.setPassword("APP");
        ds.setDatabaseName(SAMPLE_DB_NAME);
        ds.setConnectionAttributes("create=true");
        return ds;
    }

    /**
     * Sets the EmbeddedDataSource properties corresponding to the pool configuration
     * created for running the JDBC devtests.
     */
    private static org.apache.derby.jdbc.EmbeddedDataSource initializeDataSource() {
        EmbeddedDataSource ds = new EmbeddedDataSource();
        ds.setUser("APP");
        ds.setPassword("APP");
        ds.setDatabaseName(SUN_APPSERV_SAMPLES_DB);
        ds.setConnectionAttributes("create=true");
        return ds;
    }
}