(In reply to comment #0)
> 
> * Implement a static method that returns the identity of the gateway user if
> and only if the request is from a gateway user

This is done (Bug 6449).

> * Provide GRAM code that leverages this new static method 

This is also done:

http://dev.globus.org/wiki/Image:GRAMAuditV1ExtUtil.java

The above code has been tested with Java WS Core 4.0.8.  Usage is fairly
simple:

String name = GRAMAuditV1ExtUtil.getGatewayUserCol();
if (name == null) {
    // Subject name not found
} else {
    // Subject name found
}

(In reply to comment #0)
> 
> * Provide a script that adds the required column to an existing audit table

The ALTER TABLE command can be used for this purpose:

> ALTER TABLE gram_audit_table
    ADD COLUMN gateway_user VARCHAR(256);

MySql supports the AFTER clause

> ALTER TABLE gram_audit_table
    ADD COLUMN gateway_user VARCHAR(256)
      AFTER subject_name;

but Postgres and Derby do not, so we won't use it.  No big deal, since columns
should *always* be referenced by name (*never* by position).

(In reply to comment #2)
> 
> > ALTER TABLE gram_audit_table
>     ADD COLUMN gateway_user VARCHAR(256);

With Martin's help, this command has been tested using PostgreSQL, MySQL, and
Derby:

http://lists.globus.org/pipermail/gram-dev/2008-October/000000.html

The required SQL scripts (with suggested names) are provided inline below. 
Note: 1) the PostgreSQL and MySQL scripts are identical (and may be merged),
and 2) the Derby script is not strictly required for this extension (it is
included for completeness only).

############ Postgres ###############
-- File: gram_audit_alter_table_postgres-8.0.sql

-- create an empty backup table
CREATE TABLE gram_audit_table_backup
   AS
   (SELECT * FROM gram_audit_table);

-- add a column to the database
ALTER TABLE gram_audit_table
   ADD COLUMN gateway_user VARCHAR(256);

############ End Postgres ###############


############ MySQL ###############
-- File: gram_audit_alter_table_mysql.sql

# create an empty backup table
CREATE TABLE gram_audit_table_backup
   AS
   (SELECT * FROM gram_audit_table);

# add a column to the database
ALTER TABLE gram_audit_table
   ADD COLUMN gateway_user VARCHAR(256);

############ End MySQL ###############


########### Derby #############
-- File: gram_audit_alter_table_derby.sql

-- connect to the globus database
connect 'jdbc:derby:globus';

-- create an empty backup table
CREATE TABLE gram_audit_table_backup
   AS
   (SELECT * FROM gram_audit_table)
   WITH NO DATA;

-- fill the backup table with the values from the audit table
INSERT INTO gram_audit_table_backup
   SELECT * FROM gram_audit_table;

-- add a column to the database
ALTER TABLE gram_audit_table
   ADD COLUMN gateway_user VARCHAR(256);

############ End Derby #######

Updated gram_audit_schema*.sql scripts are provided inline below.  These
scripts are for *new* GRAM Audit users.  (A Derby script is not provided since
Derby is not supported in GT 4.0.x.)

############ Postgres ###############
-- File: gram_audit_schema_postgres-8.0.sql

-- --------------------------------------------------------
-- PostgreSQL DDL Statements for gram audit database schema
-- --------------------------------------------------------

create table gram_audit_table (
    "job_grid_id" varchar(256) primary key,
    "local_job_id" varchar(512),
    "subject_name" varchar(256) not null,
    "username" varchar(16) not null,
    "idempotence_id" varchar(128),
    "creation_time" timestamp not null,
    "queued_time" timestamp,
    "stage_in_grid_id" varchar(256),
    "stage_out_grid_id" varchar(256),
    "clean_up_grid_id" varchar(256),
    "globus_toolkit_version" varchar(16) not null,
    "resource_manager_type" varchar(16) not null,
    "job_description" text not null,
    "success_flag" boolean not null,
    "finished_flag" boolean not null,
    "gateway_user" varchar(256));

############ End Postgres ###############


############ MySQL ###############
-- File: gram_audit_schema_mysql.sql

-- --------------------------------------------------------
-- MySQL DDL Statements for gram audit database schema
-- --------------------------------------------------------

create table gram_audit_table (
    job_grid_id varchar(256),
    local_job_id varchar(512),
    subject_name varchar(256) not null,
    username varchar(16) not null,
    idempotence_id varchar(128),
    creation_time varchar(40) not null,
    queued_time varchar(40),
    stage_in_grid_id varchar(256),
    stage_out_grid_id varchar(256),
    clean_up_grid_id varchar(256),
    globus_toolkit_version varchar(16) not null,
    resource_manager_type varchar(16) not null,
    job_description text not null,
    success_flag varchar(5) not null,
    finished_flag varchar(5) not null,
    gateway_user varchar(256),
    PRIMARY KEY(job_grid_id(256)));

############ End MySQL ###############

Note: The scripts in Comment #4 and Comment #5 will be included with version 2
of the Science Gateway Capability Kit for CTSS4.  The scripts will *not* be
included with WS-GRAM in GT 4.0.9+ since there will be no mention of the
TeraGrid extension for GRAM in the standard GT platform.

I committed code to a branch called ws-gram-6450, which includes
GRAMAuditV1ExtUtil, ran the existing audit tests against an audit table
with and without the gateway_user column, and they worked fine.
For sanity i then modified GRAMAuditV1ExtUtil.java temporarily to return
a real string instead of null in case the SAMLUtil is not available, which
is the case in my env, and the string showed up in the table (that contained 
the gateway_user column).

Does anybody want to test this in a real environment? And if so: what should
i provide for these tests: source code, jars?

Tested and merged into globus_4_0_branch

How to install ws-gram with the auditing changes from globus_4_0_branch
and future 4.0.9 into an existing GT v4.0.8:

-------------

# download ws-gram code
cvs co -r globus_4_0_branch ws-gram

# build and deploy java code
cd ws-gram
ant

# build and deploy configuration scripts
cd service/java/setup/common/
./bootstrap
gpt-build --force

# re-install ws-gram's common jndi configuration
# note: you'll overwrite your existing jndi configuration by that,
# but this is required
$GLOBUS_LOCATION/setup/globus/setup-gram-service-common

-------------

Then edit the audit-related sections in
$GLOBUS_LOCATION/etc/gram/service/jndi-config.xml
as described in http://tinyurl.com/3w5ere, and restart your container.

That should do it.

(In reply to comment #1)
> 
> http://dev.globus.org/wiki/Image:GRAMAuditV1ExtUtil.java

I added the above code to SecurityContextEchoService.  So if you request the
EchoService using a trusted gateway credential, something like the following
output is appended:

Gateway user name: globus@aaatest.teragrid.org

I also added the following lines to trusted_authorities_entity_map.txt
distributed with the EchoService:

# A trusted proxy issuer within TeraGrid:
https://saml.teragrid.org/gateway/aaatest "CN=AAA Testbed Community
User,O=National Center for Supercomputing Applications,C=US" "CN=AAA Testbed
Community User,OU=People,O=National Center for Supercomputing
Applications,C=US"

The above entityID is found in tg-gateway-config.properties distributed with
GS-ST so everything just works out of the box.  Simply adjust the EchoService
security descriptor and invoke the EchoClient.

Doru Marcusiu has just reported a successful end-to-end test of the following
platform:

Globus 4.0.8 WSRF R1 + new GRAM JARs + new GS4GT tarball

By "new GRAM JARs", I mean the new GRAM code implemented by Martin, so testing
is complete and this bug is (finally) resolved.

The new GRAM JARs will be incorporated into CTSS4 package "Globus 4.0.8 WSRF
R2".