NATIVE authentication and SQL authorization example

This example consists of the program NativeAuthenticationExample.java, which shows how to use Derby's NATIVE user authentication and SQL authorization with either the embedded or the client driver.

Note: It is strongly recommended that, in addition to using Derby's NATIVE authentication mechanism, LDAP, or a user-defined class for authentication, production systems protect network connections with SSL/TLS.

See Using NATIVE authentication for information on NATIVE authentication. See User authorizations for more information on using SQL authorization, which allows you to use ANSI SQL Standard GRANT and REVOKE statements.

The program does the following:

  1. Uses a system property to set the authentication provider to NATIVE:nativeAuthDB:LOCAL, meaning that nativeAuthDB is the credentials database and that all user credentials are stored there.
  2. If you are running the program using the client driver, starts the Network Server.
  3. Creates a database named nativeAuthDB as the user sysadm, who is therefore the database owner. Only the database owner has the right to set and read database properties.
  4. Calls the SYSCS_UTIL.SYSCS_CREATE_USER system procedure to create several users: noacc, guest, and sqlsam. The user sysadm has already been created automatically.
  5. Creates the roles adder and viewer.
  6. Grants the role adder to sqlsam, and grants the role viewer to guest.
  7. Creates a table, accessibletbl, and inserts a value into it.
  8. Grants SELECT and INSERT privileges on accessibletbl to adder.
  9. Tries to connect to the database without supplying credentials, and fails, as expected.
  10. Connects to the database as a user who has not been granted any privileges. The connection succeeds, but the user does not attempt to perform any operations, since no operations would be permitted.
  11. Connects to the database as guest, who has the role viewer.
  12. Sets the current role to viewer; the user succeeds in executing a SELECT statement on the table, but cannot execute an INSERT statement.
  13. Connects to the database as sqlsam, who has the role adder.
  14. Sets the current role to adder; the user succeeds in executing both a SELECT and an INSERT statement, but is unable to execute a DELETE statement.
  15. Using the connection of the database owner sysadm, deletes the table, the two roles, and the three users created previously.
  16. If you are running the program using the client driver, shuts down the Network Server.
  17. Closes the connection and shuts down Derby, using the database owner's credentials.

The instructions for compiling and running the program are in the comment at the beginning of the program. DERBY_LIB is the directory that contains the Derby jar files, typically DERBY_HOME/lib.

Source code for NativeAuthenticationExample.java

// does not use derby.properties

import java.io.PrintWriter;
import java.sql.*;

import org.apache.derby.drda.NetworkServerControl;

/*
 * <p>
 * This program showcases how SQL authorization is automatically turned
 * on when you run with NATIVE authentication. You can run this program 
 * either embedded or client server.
 * </p>
 *
 * <p>
 * Here's how you compile the program:
 * </p>
 *
 * <pre>
 * javac -cp ${DERBY_LIB}/derbynet.jar NativeAuthenticationExample.java
 * </pre>
 *
 * <p>
 * Here's how you run the program embedded:
 * </p>
 *
 * <pre>
 * java -cp ${DERBY_LIB}/derby.jar:. NativeAuthenticationExample embedded
 * </pre>
 *
 * <p>
 * Here's how you run the program client/server:
 * </p>
 *
 * <pre>
 * java -cp \
 *  ${DERBY_LIB}/derby.jar:${DERBY_LIB}/derbynet.jar:${DERBY_LIB}/derbyclient.jar:. \
 *  NativeAuthenticationExample client
 * </pre>
 */
public class NativeAuthenticationExample
{
    /////////////////////////////////////////////////////////////////////
    //
    //  CONSTANTS
    //
    /////////////////////////////////////////////////////////////////////

    private static  final   String  DB_NAME="nativeAuthDB";

    // stored as SYSADM
    private static  final   String  DB_OWNER="sysadm"; 
    private static  final   String  DB_OWNER_PASSWORD="shh123ihtybb87m";

    private static  final   String  USER_WITHOUT_ROLE="NOACC";
    private static  final   String  
                                  USER_WITHOUT_ROLE_PASSWORD="ajaxj3x9";

    private static  final   String  READER="GUEST";
    private static  final   String  READER_PASSWORD="java5w6x";

    private static  final   String  WRITER="SQLSAM";
    private static  final   String  WRITER_PASSWORD="light8q9bulb";

    private static  final   String  EMBEDDED = "embedded";
    private static  final   String  CLIENT = "client";

    /////////////////////////////////////////////////////////////////////
    //
    //  STATE
    //
    /////////////////////////////////////////////////////////////////////

    private boolean _runningEmbedded;
    private NetworkServerControl    _server;

    /////////////////////////////////////////////////////////////////////
    //
    //  ENTRY POINT
    //
    /////////////////////////////////////////////////////////////////////

    public static void main( String... args )
    {
        NativeAuthenticationExample demo = parseArgs( args );

        if ( demo !=  null ) 
        { 
            demo.execute(); 
        }
        else 
        { 
            println( "Bad command line args." ); 
        }
    }
    
    private static NativeAuthenticationExample parseArgs( String... args )
    {
        if ( (args == null) || (args.length != 1) ) 
        { 
            return null; 
        }

        String  mode = args[ 0 ];

        if ( EMBEDDED.equals( mode ) ) 
        { 
            return new NativeAuthenticationExample( true ); 
        }
        else if ( CLIENT.equals( mode ) ) 
        { 
            return new NativeAuthenticationExample( false ); 
        }
        else 
        { 
            return null; 
        }
    }
    
    /////////////////////////////////////////////////////////////////////
    //
    //  CONSTRUCTOR
    //
    /////////////////////////////////////////////////////////////////////

    private NativeAuthenticationExample( boolean runningEmbedded )
    {
        _runningEmbedded = runningEmbedded;
    }

    /////////////////////////////////////////////////////////////////////
    //
    //  FEATURE SHOWCASE
    //
    /////////////////////////////////////////////////////////////////////

    /** 
     * Run all of the experiments 
     */
    private void    execute()
    {
        try
        {
            String  authenticationProvider = 
                                          "NATIVE:" + DB_NAME + ":LOCAL";
            
            // this turns on NATIVE authentication as well as 
            // SQL authorization
            println( "Setting authentication provider to " + 
                authenticationProvider );
            System.setProperty(  "derby.authentication.provider", 
                authenticationProvider );

            if ( !_runningEmbedded ) 
            { 
                startServer(); 
            }
            
            Connection  dboConn = createDatabase();

            createUsers( dboConn );
            createRoles( dboConn );
            createTable( dboConn );

            tryToConnectWithoutCredentials();   //should fail

            // a valid user can connect even if they haven't been 
            // assigned any roles
            getConnection( USER_WITHOUT_ROLE, 
                           USER_WITHOUT_ROLE_PASSWORD );

            verifyReaderPrivileges();
            verifyWriterPrivileges();

            println( "Using database owner connection again" );

            dropTable( dboConn );
            dropRoles( dboConn );
            dropUsers( dboConn );
            
            cleanUpAndShutDown();
            
        } catch (Exception e) 
        { 
            errorPrintAndExit( e ); 
        }
    }

    /**
     * Create more users. Note that the credentials for the database
     * owner were stored in the database automatically when the 
     * database was created.
     */
    public void createUsers( Connection conn ) 
        throws SQLException
    {
        println( "Storing some sample users in the database." );

        PreparedStatement   ps = prepare
            ( conn, "call syscs_util.syscs_create_user( ?, ? )" );

        createUser( ps, USER_WITHOUT_ROLE, USER_WITHOUT_ROLE_PASSWORD );
        createUser( ps, READER, READER_PASSWORD );
        createUser( ps, WRITER, WRITER_PASSWORD );

        ps.close();
    }
    
    private void    createUser( PreparedStatement ps, String userName, 
            String password )
        throws SQLException
    {
        println( "Creating user " + userName );
        ps.setString( 1, userName );
        ps.setString( 2, password );
        ps.execute();
    }

    /** 
     * Create roles and grant them privileges. 
     */
    private void    createRoles( Connection conn )
        throws SQLException
    {
        println( "Creating roles and granting privileges to them..." );
        
        execute( conn, "CREATE ROLE adder" );
        execute( conn, "CREATE ROLE viewer" );
        
        execute( conn, "GRANT adder TO " + WRITER );
        execute( conn, "GRANT viewer TO " + READER );
    }

    /** 
     * Create and populate a table and grant privileges related to it. 
     */
    private void    createTable( Connection conn )
        throws SQLException
    {
        println("Creating table accessibletbl...");
        execute( conn, 
                 "CREATE TABLE accessibletbl(textcol VARCHAR(6))" );
        execute( conn, "INSERT INTO accessibletbl VALUES('hello')" );

        println( "Granting select/insert privileges to adder..." );
        execute( conn, 
                 "GRANT SELECT, INSERT ON accessibletbl TO adder" );

        println( "Granting select privileges to viewer" );
        execute( conn, "GRANT SELECT ON accessibletbl TO viewer" );
    }

    /**
     * Drop users except for database owner.
     */
    public void dropUsers( Connection conn ) 
        throws SQLException
    {
        println( "Dropping sample users from the database..." );

        PreparedStatement   ps = prepare
            ( conn, "call syscs_util.syscs_drop_user( ? )" );

        dropUser( ps, USER_WITHOUT_ROLE );
        dropUser( ps, READER );
        dropUser( ps, WRITER );

        ps.close();
    }
    
    private void    dropUser( PreparedStatement ps, String userName )
        throws SQLException
    {
        println( "Dropping user " + userName );
        ps.setString( 1, userName );
        ps.execute();
    }

    /** 
     * Drop roles. 
     */
    private void    dropRoles( Connection conn )
        throws SQLException
    {
        println( "Dropping roles..." );
        
        execute( conn, "DROP ROLE adder" );
        execute( conn, "DROP ROLE viewer" );
    }

    /** 
     * Drop the table. 
     */
    private void    dropTable( Connection conn )
        throws SQLException
    {
        execute( conn, "DROP TABLE accessibletbl" );
    }

    /**
     * Try to connect without supplying credentials 
     */
    private void    tryToConnectWithoutCredentials()
        throws Exception
    {
        println( "Trying to connect without supplying credentials..." );

        try {
            getConnection( null, null );
            println( "ERROR: Unexpectedly connected to database " + 
                     DB_NAME );
            cleanUpAndShutDown();
        } catch (SQLException e) 
        {
            if ( e.getSQLState().equals("08004") )
            {
                println
                    (
                     "As expected, could not get a connection without " +
                     "supplying credentials."
                     );
            } else
            {
                errorPrintAndExit( e );
            }
        }
    }

    /** 
     * Verify that the READER user can select but not insert 
     */
    private void    verifyReaderPrivileges()
        throws Exception
    {
        Connection  readerConn = getConnection( READER, 
                                                READER_PASSWORD );

        println( "Setting role to VIEWER" );
        execute( readerConn, "SET ROLE VIEWER" );

        readRow( readerConn );    // should succeed
            
        try {
            writeRow( readerConn );
            println( "ERROR: Unexpectedly allowed to insert into table" );
            cleanUpAndShutDown();
        } catch (SQLException e) 
        {
            if ( e.getSQLState().equals("42500") ) 
            { 
                println( "As expected, failed to insert row." ); 
            }
            else 
            { 
                errorPrintAndExit(e); 
            }
        }

        readerConn.close();
    }

    /** 
     * Verify that the WRITER can read and write but not delete 
     */
    private void    verifyWriterPrivileges()
        throws Exception
    {
        Connection  writerConn = getConnection( WRITER, 
                                                WRITER_PASSWORD );

        // set role to ADDER
        println( "Setting role to ADDER" );
        execute( writerConn, "SET ROLE ADDER" );

        // should succeed
        readRow( writerConn );
        writeRow( writerConn );
            
        try {
            deleteRow( writerConn );    // should fail
        
            println( "ERROR: Unexpectedly allowed to DELETE." );
            cleanUpAndShutDown();
        } catch (SQLException e) 
        {
            if ( e.getSQLState().equals("42500") ) 
            {
                println( "As expected, failed to delete rows." ); 
            }
            else 
            { 
                errorPrintAndExit(e); 
            }
        }

        writerConn.close();
    }
    
    private void    readRow( Connection conn ) throws SQLException
    {
        PreparedStatement   ps = prepare
            ( conn, "SELECT * FROM sysadm.accessibletbl" );
        ResultSet   rs = ps.executeQuery();
        while( rs.next() )
        {
            println
                ( "Value of sysadm.accessibletbl/textcol = " + 
                    rs.getString( 1 ) );
        }
        rs.close();
        ps.close();
    }
    
    private void    writeRow( Connection conn ) throws SQLException
    {
        execute( conn, 
                 "INSERT INTO sysadm.accessibletbl VALUES('guest')" );
    }
    
    private void    deleteRow( Connection conn ) throws SQLException
    {
        execute( conn, "DELETE FROM sysadm.accessibletbl" );
    }
    
    /////////////////////////////////////////////////////////////////////
    //
    //  SQL HELPERS
    //
    /////////////////////////////////////////////////////////////////////

    /** 
     * Execute a statement 
     */
    private void    execute( Connection conn, String text )
        throws SQLException
    {
        PreparedStatement   ps = prepare( conn, text );

        ps.execute();
        ps.close();
    }

    /** 
     * Prepare a statement 
     */
    private PreparedStatement   prepare( Connection conn, String text )
        throws SQLException
    {
        println( "    Preparing: " + text );
        return conn.prepareStatement( text );
    }
    
    /////////////////////////////////////////////////////////////////////
    //
    //  CONNECTION MANAGEMENT
    //
    /////////////////////////////////////////////////////////////////////

    /** 
     * Create the database 
     */
    private Connection  createDatabase()
        throws SQLException
    {
        String  connectionURL = getConnectionURL
            ( DB_NAME, DB_OWNER, DB_OWNER_PASSWORD, true, false );

        println( "Creating database via this URL: " + connectionURL );

        return DriverManager.getConnection( connectionURL );
    }
    
    /** 
     * Shut down the engine and exit. 
     */
    private void cleanUpAndShutDown()
        throws Exception
    {
        // Shut down the server before the engine. this is so that
        // we can authenticate the shutdown credentials in the
        // booted database.
        if ( _server != null )
        { 
            stopServer(); 
        }

        // the engine should only be brought down locally
        _runningEmbedded = true;
        shutdownEngine();
        
        System.exit(1);
    }
    
    private void    shutdownEngine()
    {
        String shutdownURL = getConnectionURL
            ( null, DB_OWNER, DB_OWNER_PASSWORD, false, true );

        try 
        {
            println( "Shutting down engine via this URL: " + 
                     shutdownURL );
            DriverManager.getConnection(  shutdownURL );
        } catch (SQLException se) 
        {
            if ( se.getSQLState().equals("XJ015") ) 
            { 
                println( "Derby engine shut down normally" ); 
            }
            else 
            { 
                printSQLException( se ); 
            }
        }
    }
    
    /** 
     * Get a connection to the database 
     */
    private Connection  getConnection( String userName, String password )
        throws SQLException
    {
        String  connectionURL = getConnectionURL
            ( DB_NAME, userName, password, false, false );

        println( "Getting connection via this URL: " + connectionURL );

        return DriverManager.getConnection( connectionURL );
    }
    
    private String  getConnectionURL( String dbName, String userName, 
        String password, boolean createDB, boolean shutdownDB )
    {
        String  connectionURL = _runningEmbedded ?
            "jdbc:derby:" : 
            "jdbc:derby://localhost:1527/";

        if ( dbName != null ) 
        { 
            connectionURL = connectionURL + DB_NAME; 
        }
        if ( userName != null ) 
        { 
            connectionURL = connectionURL + ";user=" + userName; 
        }
        if ( password != null) 
        { 
            connectionURL = connectionURL + ";password=" + password;
        }
        if ( createDB ) 
        { 
            connectionURL = connectionURL + ";create=true"; 
        }
        if ( shutdownDB ) 
        { 
            connectionURL = connectionURL + ";shutdown=true"; 
        }

        return connectionURL;
    }

    /////////////////////////////////////////////////////////////////////
    //
    //  SERVER MANAGEMENT
    //
    /////////////////////////////////////////////////////////////////////

    /** 
     * Start the Derby server 
     */
    private void    startServer()
        throws Exception
    {
        _server = new NetworkServerControl( DB_OWNER, 
                                            DB_OWNER_PASSWORD );

        println( "Starting the Derby server..." );
        _server.start( new PrintWriter( System.out ) );

        // pause to let the server come up
        Thread.sleep( 5000L );
    }

    /** 
     * Shut down the Derby server 
     */
    private void    stopServer()
        throws Exception
    {
        println( "Stopping the Derby server..." );
        _server.shutdown();

        // pause to let the server come down
        Thread.sleep( 5000L );
    }

    /////////////////////////////////////////////////////////////////////
    //
    //  DIAGNOSTIC PRINTING
    //
    /////////////////////////////////////////////////////////////////////

    /** 
     * Report exceptions and exit. 
     */
    private void errorPrintAndExit( Throwable e )
    {
        if ( e instanceof SQLException ) 
        { 
            printSQLException((SQLException) e); 
        }
        else
        {
            println("A non-SQL error occurred.");
            e.printStackTrace();
        }
        
        System.exit(1);
    }

    /** 
     * Print a list of SQLExceptions. 
     */
    private void printSQLException( SQLException sqle )
    {
        while (sqle != null)
        {
            println("\n---SQLException Caught---\n");
            println("    SQLState:   " + (sqle).getSQLState());
            println("    Severity: " + (sqle).getErrorCode());
            println("    Message:  " + (sqle).getMessage());

            sqle.printStackTrace();

            sqle = sqle.getNextException();
        }
    }

    /** 
     * Print a diagnostic line to the console 
     */
    private static  void    println( String text ) 
    { 
        System.out.println( text ); 
    }
}