Help for Foxhound 5.0.5500a

Table of Contents     [RisingRoad]

search engine by freefind	advanced

9. Administrator Authentication

9.1 Introduction 9.2 How To Configure Authentication 9.3 Audit Trail 9.4 Emergency Password Reset

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

• Read-Only mode allows multiple web browser users to see all the data, options and settings but not change any of the options and settings.

• Administrator mode allows one web browser user at a time to provide a Foxhound GUI user id and password to gain update access to those options and settings.

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.

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

• The SQL Anywhere user id ADMIN is built in to the Foxhound database.

  It allows you to use ISQL to update the two Foxhound tables that support authentication.

  The default password is SQL... you can change it, but you're not forced to:

  -- Start - Foxhound5 - 11 Admin Update via ISQL ALTER USER ADMIN IDENTIFIED BY 'tHe Qu!cK bR^Wn Fo#';

  Administrative Tip: The shortcut 11 Admin Update via ISQL always prompts for the password, so you don't have the change the underlying Windows command file after changing the password.

• One or more Foxhound GUI user ids are stored in a Foxhound table.

  These user ids are used on the Foxhound Login window to switch from Read-Only mode to Administrator mode.

  By default the Foxhound GUI user id 'admin' is delivered with Foxhound.

  The default password is SQL, and the Login window will force you to change it.

  You can INSERT, UPDATE and DELETE other Foxhound GUI user ids, but you can't delete the admin user id.

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

• The rroad_administrator table contains the user name and password for each administrator.

  CREATE TABLE rroad_administrator ( user_name VARCHAR ( 128 ) NOT NULL DEFAULT 'admin', password_hash VARCHAR ( 64 ) NOT NULL DEFAULT ( HASH ( 'SQL', 'SHA256' ) ), -- one-way encryption PRIMARY KEY ( user_name ) ); GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON rroad_administrator TO ADMIN;

• The rroad_authentication table controls how authentication works.

  CREATE TABLE rroad_authentication ( artificial_id INTEGER NOT NULL DEFAULT 1 CONSTRAINT "Only one row is allowed in rroad_authentication" CHECK ( @artificial_id = 1 ), authentication_enabled VARCHAR ( 1 ) NOT NULL DEFAULT 'N' CONSTRAINT "rroad_authentication.authentication_enabled must be Y or N" CHECK ( @authentication_enabled IN ( 'Y', 'N' ) ), user_name VARCHAR ( 128 ) NULL DEFAULT NULL, cookie_max_age_seconds INTEGER NOT NULL DEFAULT ( 60 * 60 * 12 ) -- DEFAULT 12h CONSTRAINT "rroad_authentication.cookie_max_age_seconds must be between 1m and 12h" CHECK ( @cookie_max_age_seconds BETWEEN 60 AND ( 60 * 60 * 12 ) ), session_timeout_minutes INTEGER NOT NULL DEFAULT ( 60 * 12 ) -- DEFAULT 12h CONSTRAINT "rroad_authentication.session_timeout_minutes must be between 1m and 12h" CHECK ( @cookie_max_age_seconds BETWEEN 1 AND ( 60 * 12 ) ), session_id VARCHAR ( 128 ) NULL, debug_messages VARCHAR ( 3 ) NOT NULL DEFAULT 'OFF' CONSTRAINT "rroad_authentication.debug_messages must be ON or OFF" CHECK ( @debug_messages IN ( 'ON', 'OFF' ) ), PRIMARY KEY ( artificial_id ), CONSTRAINT rroad_administrator FOREIGN KEY ( user_name ) REFERENCES rroad_administrator ( user_name ) ON UPDATE SET NULL ON DELETE SET NULL ); GRANT SELECT ON rroad_authentication TO ADMIN; GRANT UPDATE ( authentication_enabled, user_name, cookie_max_age_seconds, session_timeout_minutes, session_id ) ON rroad_authentication TO ADMIN;

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 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:

• The Foxhound purge process does not delete any rows from logged_administrator or logged_authentication.

• The Mini-Backup process does not include the audit tables, so a Mini-Restore effectively deletes all the old audit table rows.

• The same is true when Foxhound is reinstalled with FOXHOUND5UPGRADE=OPTIONS: all the old rows are deleted.

• However, when Foxhound is reinstalled with FOXHOUND5UPGRADE=ALL, yyyymmdd or nnn, all the old rows are preserved.

• You can manually TRUNCATE the audit tables, or DELETE selected rows, via Start - Foxhound5 - 11 Admin Update via ISQL.

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.