...
Note: Changing to the JDBC Security DAO means that you will no longer be able to use the user/role administration functionality found in the Pentaho Admin Console.
- Edit webpentaho-spring-beans.xml
Change the Spring XML files to use the JDBC DAOs instead of the Hibernate ones. Openpentaho-solutions/system/pentaho-spring-beans.xmland look for the following section:Code Block xml xml title pentaho-spring-beans.xml <beans> <!-- some lines omitted --> <import resource="applicationContext-acegi-security.xml" /> <import resource="applicationContext-common-authorization.xml" /> <import resource="applicationContext-acegi-security-jdbc.xml" /> <import resource="applicationContext-pentaho-security-jdbc.xml" /> </beans>
- Create the security tables
Copy the below SQL into a file calleduserdb.script.
The sample Spring XML files (i.e.applicationContext-acegi-security-jdbc.xmlandapplicationContext-pentaho-security-jdbc.xml) assume a HSQLDB database and 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.Panel title 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,
DESCRIPTION VARCHAR(100))
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('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') - Start the database
The security database will need to be running before the first user logs in.Panel title Command to start HSQLDB java -cp lib\hsqldb.jar org.hsqldb.Server -database.0 userdb -dbname.0 userdb -port 9002
exit - Start the application server
Now that the database is running and the security tables have been created, start the application server. - 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.Panel title Command to stop HSQLDB java -cp lib\hsqldb.jar org.hsqldb.util.ShutdownServer -url "jdbc:hsqldb:hsql://localhost:9002/userdb" -user "sa" -password ""
exit