LEHS - Log- and Exception-Handling System for ORACLE®

Download LEHS

  1. Download the latest Release of LEHS here
  2. Unzip the Archive
    unzip lehs_1.1.zip

Create a User for LEHS

  1. Login as a DBA
  2. Create a new User for LEHS
    CREATE USER lehs IDENTIFIED BY lehs DEFAULT TABLESPACE users ACCOUNT UNLOCK;
  3. Grant the needed Privileges
    GRANT CREATE SESSION TO lehs;
    GRANT CREATE TABLE TO lehs;
    GRANT CREATE VIEW TO lehs;
    GRANT CREATE SEQUENCE TO lehs;
    GRANT CREATE PROCEDURE TO lehs;
    GRANT EXECUTE ON SYS.dbms_system TO lehs;
    GRANT SELECT ON dba_role_privs TO lehs;
    GRANT SELECT ON dba_synonyms TO lehs;
    GRANT SELECT ON v_$option TO lehs;
  4. Create a new Role LEHS_ADMIN
    CREATE ROLE lehs_admin;
These Tasks can also be done automatically by the Script sys/create_user.sql.

Install LEHS

  1. Change to the Directory objects
    cd objects
  2. Connect to the Database as the User, where LEHS should be installed to
    sqlplus lehs/lehs
  3. Start the Script lehs_createall.sql
    @lehs_createall
  4. Leave the Database
  5. Change to the Directory plsql
    cd ../plsql
  6. Connect to the Database as the User, where LEHS should be installed to
  7. Start the Script lehs_createall.sql
    @lehs_createall
  8. If you have installed LEHS on a User not named LEHS then you have to change the Value of the Constant PKG_LEHS_SYSTEM.pc_owner to this Installation User!
When you've got LEHS by the SVN-Repository, you can install LEHS by calling ant install
(Prerequisite: You have set the Parameters db.user, db.passwd and db.url at the File user.property)

The following Privileges will be created automatically by the both Installation Scripts (and the Installation-Target of the Ant-Script build.xml):

GRANT EXECUTE ON LEHS.pkg_lehs_system TO lehs_admin;
GRANT SELECT ON LEHS.lehs_applications TO lehs_admin;
GRANT SELECT ON LEHS.lehs_application_privs TO lehs_admin;
GRANT SELECT ON LEHS.lehs_appender TO lehs_admin;
GRANT SELECT ON LEHS.lehs_appender_parameters TO lehs_admin;
GRANT SELECT ON LEHS.lehs_exception_messages TO lehs_admin;
GRANT SELECT ON LEHS.lehs_log_levels TO lehs_admin;
GRANT SELECT ON LEHS.lehs_log_levels_programmable TO lehs_admin;

GRANT EXECUTE ON LEHS.pkg_lehs TO public;
GRANT EXECUTE ON LEHS.pkg_wrapped_lehs TO public;
GRANT EXECUTE ON LEHS.pkg_lehs_format TO public;
GRANT SELECT ON LEHS.lehs_logs_view TO public;
GRANT SELECT ON LEHS.lehs_topics_view TO public;
GRANT SELECT ON LEHS.lehs_transaction_view TO public;
GRANT SELECT ON LEHS.lehs_applications_view TO public;

Do some public Stuff

  1. Login as DBA
  2. Remove the SELECT-Privilege on V$OPTION
    REVOKE SELECT ON v_$option FROM lehs;
  3. Create public Synonyms
    CREATE OR REPLACE PUBLIC SYNONYM pkg_lehs FOR LEHS.pkg_lehs;
    CREATE OR REPLACE PUBLIC SYNONYM pkg_lehs_format FOR LEHS.pkg_lehs_format;
    CREATE OR REPLACE PUBLIC SYNONYM pkg_wrapped_lehs FOR LEHS.pkg_wrapped_lehs;
    CREATE OR REPLACE PUBLIC SYNONYM pkg_lehs_system FOR LEHS.pkg_lehs_system;
    CREATE OR REPLACE PUBLIC SYNONYM pkg_lehs_appender FOR LEHS.pkg_lehs_appender;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_logs FOR LEHS.lehs_logs_view;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_levels FOR LEHS.lehs_log_levels_view;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_topics FOR LEHS.lehs_topics_view;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_transactions FOR LEHS.lehs_transaction_view;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_allowed_applications FOR LEHS.lehs_applications_view;
  4. Create public Synonyms on the Configuration Tables (used by the LEHS Administrator)
    CREATE OR REPLACE PUBLIC SYNONYM lehs_applications FOR LEHS.lehs_applications;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_application_privs FOR LEHS.lehs_application_privs;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_appender FOR LEHS.lehs_appender;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_appender_parameters FOR LEHS.lehs_appender_paramters;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_exception_messages FOR LEHS.lehs_exception_messages;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_log_levels FOR LEHS.lehs_log_levels;
    CREATE OR REPLACE PUBLIC SYNONYM lehs_log_levels_programmable FOR LEHS.lehs_log_levels_programmable;
These Tasks can also be done automatically by the Script sys/create_synonyms.sql