Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Next »

Unable to render {include} The included page could not be found.

By default, the Pentaho distribution comes with the "in-memory" security data access object (DAO) enabled. Because this is only recommended for testing or small user populations, you'll want to switch over to either a relational database back-end or an LDAP back-end. This page shows you how to switch to the "JDBC" DAO. The instructions below describe a sample security database using HSQLDB.

  1. Edit web.xml
    Change the Spring XML files to use the JDBC DAOs instead of the in-memory ones. Open pentaho.war/WEB-INF/web.xml and look for the following section:
    web.xml (before)
    <context-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>
        /WEB-INF/applicationContext-acegi-security.xml 
        /WEB-INF/applicationContext-common-authorization.xml 
        /WEB-INF/applicationContext-acegi-security-memory.xml 
        /WEB-INF/applicationContext-pentaho-security-memory.xml
      </param-value>
    </context-param>
    
    Change that section to look like:
    web.xml (after)
    <context-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>
        /WEB-INF/applicationContext-acegi-security.xml 
        /WEB-INF/applicationContext-common-authorization.xml 
        /WEB-INF/applicationContext-acegi-security-jdbc.xml 
        /WEB-INF/applicationContext-pentaho-security-jdbc.xml
      </param-value>
    </context-param>
    
  2. Create the security tables
    Copy the below SQL into a file called userdb.script.
    The sample Spring XML files (i.e. pentaho.war/WEB-INF/applicationContext-acegi-security-jdbc.xml and pentaho.war/WEB-INF/applicationContext-pentaho-security-jdbc.xml) assume the tables below. If you already have security tables setup, or you wish to alter the sample, you'll need to adjust your SQL queries in the aforementioned Spring XML files.
    Sample SQL for HSQLDB to create security tables

    CREATE SCHEMA PUBLIC AUTHORIZATION DBA
    CREATE MEMORY TABLE USERS(USERNAME VARCHAR(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR(50) NOT NULL,ENABLED BOOLEAN NOT NULL)
    CREATE MEMORY TABLE AUTHORITIES(AUTHORITY VARCHAR(50) NOT NULL PRIMARY KEY,DESCRIPTION VARCHAR(100))
    CREATE MEMORY TABLE GRANTED_AUTHORITIES(USERNAME VARCHAR(50) NOT NULL,AUTHORITY VARCHAR(50) NOT NULL,CONSTRAINT FK_GRANTED_AUTHORITIES_USERS FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME),CONSTRAINT FK_GRANTED_AUTHORITIES_AUTHORITIES FOREIGN KEY(AUTHORITY) REFERENCES AUTHORITIES(AUTHORITY))
    CREATE USER SA PASSWORD ""
    GRANT DBA TO SA
    SET WRITE_DELAY 10
    SET SCHEMA PUBLIC
    INSERT INTO USERS VALUES('admin','secret',TRUE)
    INSERT INTO USERS VALUES('joe','password',TRUE)
    INSERT INTO USERS VALUES('pat','password',TRUE)
    INSERT INTO USERS VALUES('suzy','password',TRUE)
    INSERT INTO USERS VALUES('tiffany','password',TRUE)
    INSERT INTO AUTHORITIES VALUES('Admin','Super User')
    INSERT INTO AUTHORITIES VALUES('ROLE_ANONYMOUS','User has not logged in')
    INSERT INTO AUTHORITIES VALUES('Authenticated','User has logged in')
    INSERT INTO AUTHORITIES VALUES('ceo','Chief Executive Officer')
    INSERT INTO AUTHORITIES VALUES('cto','Chief Technology Officer')
    INSERT INTO AUTHORITIES VALUES('dev','Developer')
    INSERT INTO AUTHORITIES VALUES('devmgr','Development Manager')
    INSERT INTO AUTHORITIES VALUES('is','Information Services')
    INSERT INTO GRANTED_AUTHORITIES VALUES('joe','Admin')
    INSERT INTO GRANTED_AUTHORITIES VALUES('joe','ceo')
    INSERT INTO GRANTED_AUTHORITIES VALUES('joe','Authenticated')
    INSERT INTO GRANTED_AUTHORITIES VALUES('suzy','cto')
    INSERT INTO GRANTED_AUTHORITIES VALUES('suzy','is')
    INSERT INTO GRANTED_AUTHORITIES VALUES('suzy','Authenticated')
    INSERT INTO GRANTED_AUTHORITIES VALUES('pat','dev')
    INSERT INTO GRANTED_AUTHORITIES VALUES('pat','Authenticated')
    INSERT INTO GRANTED_AUTHORITIES VALUES('tiffany','dev')
    INSERT INTO GRANTED_AUTHORITIES VALUES('tiffany','devmgr')
    INSERT INTO GRANTED_AUTHORITIES VALUES('tiffany','Authenticated')
    INSERT INTO GRANTED_AUTHORITIES VALUES('admin','Admin')
    INSERT INTO GRANTED_AUTHORITIES VALUES('admin','Authenticated')

  3. Start the database
    The security database will need to be running before the first user logs in.
    Command to start HSQLDB

    java -cp lib\hsqldb.jar org.hsqldb.Server -database.0 userdb -dbname.0 userdb -port 9002
    exit

  4. Start the application server
    Now that the database is running and the security tables have been created, start the application server.
  5. Stop the database
    When you shutdown your application server, you'll want to shutdown the security database as well. The command to do that is below.
    Command to stop HSQLDB

    java -cp lib\hsqldb.jar org.hsqldb.util.ShutdownServer -url "jdbc:hsqldb:hsql://localhost:9002/userdb" -user "sa" -password ""
    exit

  • No labels