Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Authentication database

  1. #1
    Join Date
    Feb 2011
    Posts
    240

    Default Authentication database

    Hi,

    Can you have a database of authentication other than Pentaho?

    Thanks

    Mariarita

  2. #2
    Join Date
    Mar 2009
    Posts
    112

    Default

    If you mean something like another database instead of the included HSQL for the user tables, the answer is yes.
    Take a look at the pentaho-spring-beans.xml on the pentaho-solutions/system folder
    It will point to the files
    - applicationContext-spring-security-hibernate.xml
    - applicationContext-pentaho-security-hibernate.xml
    on the same folder.

    You can change them to use the jdbc files
    - applicationContext-spring-security-jdbc.xml
    - applicationContext-pentaho-security-jdbc.xml

    Adjusting the settings on those files should not be very hard. Just take care that in the database you point you have an user database model similar to the one on the HSQL hibernate database.
    Here's the SQL for the "important" tables (generated by Squirrel SQL), it may need some tweaking:
    Code:
    CREATE TABLE "PUBLIC"."AUTHORITIES"
    (
       AUTHORITY varchar PRIMARY KEY NOT NULL,
       DESCRIPTION varchar
    )
    ;
    CREATE TABLE "PUBLIC"."GRANTED_AUTHORITIES"
    (
       USERNAME varchar NOT NULL,
       AUTHORITY varchar NOT NULL,
       CONSTRAINT SYS_IDX_50 PRIMARY KEY (USERNAME,AUTHORITY)
    )
    ;
    CREATE TABLE "PUBLIC"."USER_SETTINGS"
    (
       USER_SETTINGS_ID bigint PRIMARY KEY NOT NULL,
       USERNAME varchar NOT NULL,
       SETTING_NAME varchar NOT NULL,
       SETTING_VALUE varchar NOT NULL
    )
    ;
    CREATE TABLE "PUBLIC"."USERS"
    (
       USERNAME varchar PRIMARY KEY NOT NULL,
       PASSWORD varchar NOT NULL,
       DESCRIPTION varchar,
       ENABLED boolean NOT NULL
    )
    ;
    CREATE UNIQUE INDEX SYS_IDX_48 ON "PUBLIC"."AUTHORITIES"(AUTHORITY)
    ;
    ALTER TABLE "PUBLIC"."GRANTED_AUTHORITIES"
    ADD CONSTRAINT FK7471775DD9EDC77F
    FOREIGN KEY (USERNAME)
    REFERENCES "PUBLIC"."USERS"(USERNAME)
    ;
    ALTER TABLE "PUBLIC"."GRANTED_AUTHORITIES"
    ADD CONSTRAINT FK7471775D41B6DA97
    FOREIGN KEY (AUTHORITY)
    REFERENCES "PUBLIC"."AUTHORITIES"(AUTHORITY)
    ;
    CREATE INDEX SYS_IDX_52 ON "PUBLIC"."GRANTED_AUTHORITIES"(AUTHORITY)
    ;
    CREATE INDEX SYS_IDX_138 ON "PUBLIC"."GRANTED_AUTHORITIES"(USERNAME)
    ;
    CREATE UNIQUE INDEX SYS_IDX_50 ON "PUBLIC"."GRANTED_AUTHORITIES"
    (
      USERNAME,
      AUTHORITY
    )
    ;
    CREATE UNIQUE INDEX SYS_IDX_128 ON "PUBLIC"."USER_SETTINGS"(USER_SETTINGS_ID)
    ;
    CREATE UNIQUE INDEX SYS_IDX_54 ON "PUBLIC"."USERS"(USERNAME)
    ;
    Hope it helps. Best regards,
    José Ignacio Santa Cruz G.

  3. #3

    Default

    Dear You!

    I'm a newbie, but trie to learn as I can.
    My first problem is:
    We have a Debian Server for playing and I have to collect data from Progress and Postgres DB-s.

    Therefore I download the sql scripts for hibernate,quartz and sampledata.
    I created these databases using phpmyadmin.
    After it I used data-integration spoon to replicate localhost hsqldb hibernate,qurtz and sampladata.

    I've got errors: The Problem was (ex. IS_VIOLATING VarChar(1) too small ) the fields length for Boolena fields. After I've changed to VarChar(12) them I could finish the replication.

    Next step was getting information about following files:

    1. /usr/local/pentaho/server/biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
    2. /usr/local/pentaho/server/biserver-ce/pentaho-solutions/system/
      1. applicationContext-pentaho-security-hibernate-jdbc.xml
      2. applicationContext-spring-security-hibernate-jdbc.xml
      3. directory hibernate
      4. pentaho-spring-beans.xml
      5. applicationContext-spring-security-jdbc.xml
      6. applicationContext-spring-security-jdbc_orig.xml
      7. publisher_config.xml
    3. /usr/local/pentaho/server/biserver-ce/pentaho-solutions/system/hibernate/
      1. mysql5.hibernate.cfg.xml
      2. mysql5.hibernate.cfg_orig.xml
      3. hibernate-settings.xml
    4. /usr/local/pentaho/server/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml
    5. /usr/local/pentaho/server/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
    6. /usr/local/pentaho/server/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml

    I changed them and I tested my DataSource from Windows Client Administration Console and Test result was OK.

    I cannot start my BI Server (CE) after my changes.

    Here are some lines from catalina.out:
    INFO: Deploying configuration descriptor pentaho.xml
    22:10:28,978 ERROR [JDBCExceptionReporter] socket creation error
    22:10:29,502 ERROR [JDBCExceptionReporter] socket creation error
    22:10:29,503 ERROR [SchemaUpdate] could not get database metadata
    java.sql.SQLException: socket creation error
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source)
    at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
    at org.hsqldb.jdbcDriver.connect(Unknown Source)

    Can somebody teach me ?

    By,
    Töci

  4. #4
    Join Date
    Mar 2009
    Posts
    112

    Default

    Töci:
    Check the configuration files, you're still pointing to the HSQL configiration and you want it to point to your new MySQL config.

    Take things easy, patience, there's no magic here.
    Read AND understand the documentation.
    Search in the forum, many of the begginers questions have been answered.
    In case of doubt provide all the valuable info you can.

    Best regards,
    José Ignacio Santa Cruz G.

  5. #5

    Default Dear Jsanta

    Than you for your answer.
    I found my mistake or unchanged file (last one) under webaps/system/application...xml where i overwrited HSQLDialect to MYSQLDialect but i left database source unchanged.

    If I'm honest I'm beginer in TomCat,Java area, but my leaders prefer not to code everithing if there are several existing tool like Pentaho.

    I've just finshed a Cache Ensemble projekt and this is my new "near future".
    I prefer Pentaho and I don't mind learning new things again and again.

    Best Regards,
    Töci

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.