Help for Foxhound 5.0.5474a

Table of Contents     [RisingRoad]
search engine by freefind advanced


Foxhound 5 

9. Administrator Authentication

9.1 Introduction

9.2 How To Configure Authentication

9.3 Audit Trail

9.4 Emergency Password Reset


Foxhound 5 9. Administrator Authentication 
9.1 Introduction

Foxhound Administrator Authentication is an optional feature that enables two modes of operation:

Foxhound Administrator Authentication is disabled by default, and everyone can use all of the features Foxhound has to offer.

In other words, if you don't want to use Administration Authentication, you don't have to.

Also, Foxhound Administrator Authentication doesn't have anything to do with authenticated databases, or Windows integrated logins, or TLS... it only affects which Foxhound features are available via web browser sessions.

To enable Administrator Authentication, use Start - Foxhound5 - 11 Admin Update via ISQL to run these commands:

UPDATE rroad_authentication SET authentication_enabled = 'Y';
COMMIT;

When Administrator Authentication is enabled, all the Foxhound pages will display a Login / Logout menu item at the right end of the menu line.

When Login is displayed, you are in Read-Only mode and you can click on Login to open the Login window.

When Logout is displayed, you are in Administrator mode and you can click on Logout to return to Read-Only mode.


Foxhound 5 9. Administrator Authentication 
9.2 How To Configure Authentication

There are two different kinds of user ids involved in Foxhound Administrator Authentication:

Here are the two tables you can modify with Start - Foxhound5 - 11 Admin Update via ISQL:

Administrative Tip: Other than changing authentication_enabled to 'Y', you might not have to make any changes to these tables; the defaults were all carefully chosen :)

Having said that, here's a "How To" list of updates you can make with Start - Foxhound5 - 11 Admin Update via ISQL:

1. Turn the Administrator Authentication feature on and off

UPDATE rroad_authentication SET authentication_enabled = 'Y';
COMMIT;

UPDATE rroad_authentication SET authentication_enabled = 'N';
COMMIT;

2. Force an immediate logout of whoever's logged in as Administrator.

UPDATE rroad_authentication SET session_id = NULL;
COMMIT;

3. Reset one Foxhound GUI password back to the default to force a change on the next Login

UPDATE rroad_administrator SET password_hash = HASH ( 'SQL', 'SHA256' ) WHERE user_name = 'admin';
COMMIT;

4. Reset all Foxhound GUI passwords back to the default to force changes on the next logins

UPDATE rroad_administrator SET password_hash = HASH ( 'SQL', 'SHA256' );
COMMIT;

5. Create a new Foxhound GUI user id with the default password which must be changed

INSERT rroad_administrator ( user_name ) VALUES ( 'jorge.ramirez' );
COMMIT;

6. Change a Foxhound GUI password

UPDATE rroad_administrator SET password_hash = HASH ( 'tHe QuIcK bRoWn FoX', 'SHA256' ) WHERE user_name = 'jorge.ramirez';
COMMIT;

7. Change a Foxhound GUI user name (but not 'admin')

UPDATE rroad_administrator SET user_name = 'jorgeb.ramirez' WHERE user_name = 'jorge.ramirez';
COMMIT;

8. Delete an existing Foxhound GUI user id

DELETE rroad_administrator WHERE user_name = 'jorgeb.ramirez';
COMMIT;

9. Change the timeout via HTTP header Set-Cookie ... max_age=...

UPDATE rroad_authentication SET cookie_max_age_seconds = 60; -- 1m
COMMIT;

10. Change the timeout via HTTP option SessionTimeout

UPDATE rroad_authentication SET session_timeout_minutes = 1; -- 1m
COMMIT;

-- Note: Both methods seem to work on the browsers tested.

11. Reset the Cookie max_age and SessionTimeout to the defaults.

UPDATE rroad_authentication SET cookie_max_age_seconds = 60 * 60 * 12;  -- 12h
UPDATE rroad_authentication SET session_timeout_minutes = 60 * 12;      -- 12h
COMMIT;

12. Here are some easy mistakes to make, and the resulting error messages...

INSERT rroad_administrator ( user_name ) VALUES ( 'admin' );
Primary key for table 'rroad_administrator' is not unique: Primary key value (''admin'')  SQLCODE=-193,

DELETE rroad_administrator WHERE user_name = 'admin';
DELETE rroad_administrator;
RAISERROR executed: Do not attempt to DELETE rroad_administrator WHERE user_name = admin SQLCODE=-99999

UPDATE rroad_administrator SET user_name = 'fred' WHERE user_name = 'admin';
RAISERROR executed: Do not attempt to UPDATE rroad_administrator.user_name WHERE user_name = admin SQLCODE=-99999

DELETE rroad_authentication;
Permission denied: you do not have permission to delete from "rroad_authentication" SQLCODE=-121


Foxhound 5 9. Administrator Authentication 
9.3 Audit Trail

Foxhound maintains an audit trail of changes to the rroad_administrator and rroad_authentication tables in these two Foxhound tables and associated views for adhoc queries:

CREATE TABLE logged_rroad_administrator (
   log_id         UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   logged_action  VARCHAR ( 50 ) NOT NULL CHECK ( logged_action IN ( 
                     'after INSERT', 'before UPDATE', 'after UPDATE', 'before DELETE' ) ),
   logged_at      TIMESTAMP NOT NULL DEFAULT TIMESTAMP, 
   logged_by      VARCHAR ( 20 ) NULL DEFAULT current user,
   -- Columns from table to be logged...
   user_name                VARCHAR ( 128 ),
   password_hash            VARCHAR ( 64 ) );

GRANT SELECT, DELETE, TRUNCATE ON logged_rroad_administrator TO ADMIN;

CREATE VIEW logged_administrator AS 
SELECT * 
  FROM logged_rroad_administrator -- PRIMARY KEY ( log_id )
;

GRANT SELECT ON logged_administrator TO ADHOC;

CREATE TABLE logged_rroad_authentication (
   log_id         UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   logged_action  VARCHAR ( 50 ) NOT NULL CHECK ( logged_action IN ( 
                     'after INSERT', 'before UPDATE', 'after UPDATE', 'before DELETE' ) ),
   logged_at      TIMESTAMP NOT NULL DEFAULT TIMESTAMP, 
   logged_by      VARCHAR ( 20 ) NULL DEFAULT current user,
   -- Columns from table to be logged...
   artificial_id            INTEGER,
   authentication_enabled   VARCHAR ( 1 ),
   user_name                VARCHAR ( 128 ),
   cookie_max_age_seconds   INTEGER,
   session_timeout_minutes  INTEGER,
   session_id               VARCHAR ( 128 ),
   debug_messages           VARCHAR ( 3 ) );

GRANT SELECT, DELETE, TRUNCATE ON logged_rroad_authentication TO ADMIN;

CREATE VIEW logged_authentication AS 
SELECT * 
  FROM logged_rroad_authentication -- PRIMARY KEY ( log_id )
;

GRANT SELECT ON logged_authentication TO ADHOC;

You can use Start - Foxhound5 - 2 Adhoc Query via ISQL to display the audit trail data, and 11 Admin Update via ISQL to both display and delete that data.

Here are two examples:

  1. Audit trail of changes to the rroad_administrator table.

    -- Changes made via SQL Anywhere ADMIN user id.
    
    UPDATE rroad_administrator SET password_hash = HASH ('Hello', 'SHA256' ) WHERE user_name = 'admin';
    COMMIT;
    UPDATE rroad_administrator SET password_hash = HASH ('World', 'SHA256' ) WHERE user_name = 'admin';
    COMMIT;
    
    -- Audit trail query via SQL Anywhere ADHOC user id.
    
    SELECT * FROM logged_administrator ORDER BY log_id;
    
    log_id, logged_action,   logged_at,               logged_by, user_name, password_hash
    1,      'before UPDATE', 2019-10-18 14:01:37.85,  'ADMIN',   'admin',   'a7056a455639d1c7deec82ee787db24a0c1878e2792b4597709f0facf7cc7b35'
    2,      'after UPDATE',  2019-10-18 14:01:37.85,  'ADMIN',   'admin',   '185f8db32271fe25f561a6fc938b2e264306ec304eda518007d1764826381969'
    3,      'before UPDATE', 2019-10-18 14:01:37.853, 'ADMIN',   'admin',   '185f8db32271fe25f561a6fc938b2e264306ec304eda518007d1764826381969'
    4,      'after UPDATE',  2019-10-18 14:01:37.853, 'ADMIN',   'admin',   '78ae647dc5544d227130a0682a51e30bc7777fbb6d8a8f17007463a3ecd1d524'
    

  2. Audit trail of changes to the rroad_authentication table.

    -- Changes made via SQL Anywhere ADMIN user id.
    
    UPDATE rroad_authentication SET authentication_enabled = 'Y';
    COMMIT;
    
    -- Audit trail query via SQL Anywhere ADHOC user id.
    
    SELECT * FROM logged_authentication ORDER BY log_id;
    
    log_id, logged_action,   logged_at,               logged_by, artificial_id, authentication_enabled, user_name, cookie_max_age_seconds, session_timeout_minutes, session_id, debug_messages
    1,      'before UPDATE', 2019-10-18 14:28:31.119, 'ADMIN',   1,             'N',                    (NULL),    43200,                  720,                     (NULL),     'OFF'
    2,      'after UPDATE',  2019-10-18 14:28:31.119, 'ADMIN',   1,             'Y',                    (NULL),    43200,                  720,                     (NULL),     'OFF'
    

Administrative Tip: No audit trail is maintained for Login and Logout operations, or for any changes made to other Foxhound tables.

Administrative Tip: Here's how old audit trail data is handled:


Foxhound 5 9. Administrator Authentication 
9.4 Emergency Password Reset
  1. If you lose the password for the SQL Anywhere user id ADMIN you can reset it by reinstalling Foxhound.

    You can choose whether you want preserve some or all of the existing data by specifying which value of FOXHOUND5UPGRADE is to be used: ALL, OPTIONS (the default), yyyymmdd, nnn or NOTHING as described in Introduction and Setup Environment Variables FOXHOUND5UPGRADE.

    Whenever Foxhound is installed or reinstalled, even if an existing Foxhound database is upgraded, even if it's a reinstallation of the same version of Foxhound, the ADMIN password is reset to SQL.

    Administrative Tip: The statement the ADMIN password is reset to SQL applies to the SQL Anywhere user id ADMIN, not to any of Foxhound GUI user ids like 'admin'.

    For that other kind of user id, see the next point.

  2. If you lose the password for a Foxhound GUI user id you can reset it by using Start - Foxhound5 - 11 Admin Update via ISQL:

    UPDATE rroad_administrator SET password_hash = HASH ( 'SQL', 'SHA256' ) WHERE user_name = 'admin';
    COMMIT;
    

    Administrative Tip: You can do this for the 'admin' Foxhound GUI user id or any other.


Foxhound 5 9. Administrator Authentication