The path to achieving improved security on IBM i is sometimes a winding road. SQL can help to get you there on time. In this installment, FRCA is raised from obscurity to something understandable. #SQLcandoit,

frca_info.sql

--
-- Subject: HTTP Servers and FRCA
-- Author: Scott Forstie  
-- Date  : February, 2024
-- Features Used : This Gist uses SQL PL, qsys2.object_statistics, qsys2.syspartitionstat, 
--                 SQL aliases, SQL built-in functions, UDTF, qsys2.ifs_read, and pipe
--
-- HTTP Server configuration detail resides in the IFS.
-- The HTTP Server instance file (QUSRSYS/QATMHINSTC *FILE)
-- points to the IFS path to use for each specific server.
--
-- The member name corresponds to the HTTP Server name
--
-- Resources:
-- https://www.ibm.com/support/pages/how-manually-create-http-server
-- https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/rzaie/rzaiemod_core.html
-- https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/rzaie/rzaiemod_as_cache.html
-- https://www.ibm.com/docs/en/i/7.5?topic=performance-fast-response-cache-accelerator-frca
--

--
-- Does the QUSRSYS/QATMHINSTC *FILE exist?
--
select *
  from table (
      qsys2.object_statistics('QUSRSYS', '*FILE', 'QATMHINSTC')
    );
stop;

--
-- List all of the HTTP Server instances
--
select table_partition
  from qsys2.syspartitionstat a
  where table_schema = 'QUSRSYS' and
        TABLE_NAME = 'QATMHINSTC'
  order by create_timestamp desc;
stop;
  
--
-- Examination of a specific HTTP Server instance
--
create or replace alias qtemp.http_server_mbr for QUSRSYS.QATMHINSTC(AJSP);
select * from  qtemp.http_server_mbr;
stop;

--
-- Examination of a specific HTTP Server instance - Construct the IFS path
--
select rtrim(
    substr(
      charfield, locate_in_string(charfield, '-d', 1, 1) + 3, locate_in_string(
        charfield, ' ', locate_in_string(charfield, '-d', 1, 1) + 3, 1) -
        (locate_in_string(charfield, '-d', 1, 1) + 3))) concat '/' concat
    rtrim(
      substr(
        charfield, locate_in_string(charfield, '-f', 1, 1) + 3, locate_in_string(
          charfield, ' ', locate_in_string(charfield, '-f', 1, 1) + 3, 1) -
          (locate_in_string(charfield, '-f', 1, 1) + 3))) as server_instance_path
  from qtemp.http_server_mbr;
  
stop;

--
-- Examination of a specific HTTP Server instance - Configuration detail
--
-- https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/rzaie/rzaiemod_core.html
--
select substr(line, 1, locate_in_string(line, ' ', 1, 1)) as attribute_name, 
       rtrim(substr(line, locate_in_string(line, ' ', 1, 1) + 1, 10000)) as attribute_value
  from table(
      qsys2.ifs_read('/www/ajsp/conf/httpd.conf')
  )
  order by line_number asc;
stop;                         
                          
--
-- Fast Response Cache Accelerator (FRCA) information
-- https://www.ibm.com/docs/en/i/7.5?topic=performance-fast-response-cache-accelerator-frca
--
-- Note 1: FRCA is used to potentially improve server performance via the use of a local cache.
--       The HTTP Server does not check for authorization on content served from FRCA. 
--       Therefore, it is advised to find servers that have FRCA established and 
--       change the configuration to no longer use FRCA.
--
-- Note 2: FRCA does not support SSL. 
--         Therefore, do not specify FRCA option for IP addresses 
--         and ports that are used for SSL connections. 
--
-- Goal:   Zero rows returned. Any use of FRCA has security concerns.
--
create or replace function systools.frca_info()
  returns table (
    server_name     varchar(10) for sbcs data,
    attribute_name  varchar(100) for sbcs data,
    attribute_value varchar(100) for sbcs data,
    server_config_path varchar(5000) for sbcs data
  )
  specific systools.frca_info
  modifies sql data
  not deterministic
  no external action
  not fenced
  disallow parallel
  set option commit = *NONE
  begin
    declare error_hit integer default 0;
    declare sql_stmt_text varchar(10000) for sbcs data;
    declare server_config_path varchar(10000) for sbcs data;
    declare v_member varchar(10) for sbcs data;
    declare v_attribute_name  varchar(100) for sbcs data;
    declare v_attribute_value varchar(100) for sbcs data;
    declare not_found int default 0;
    declare at_end int default 0;
    declare at_end2 int default 0;
    declare FRCA_setup_csr cursor for FRCA_setup_stmt;
    declare HTTP_servers_csr cursor for
      select table_partition
        from qsys2.syspartitionstat a
        where table_schema = 'QUSRSYS' and
              TABLE_NAME = 'QATMHINSTC'
        order by create_timestamp desc;
          
    declare continue handler for not found set at_end = 1;
    open HTTP_servers_csr;   
    set at_end = 0;
    fetch from HTTP_servers_csr into v_member;

    while (at_end = 0) do
    call systools.lprintf('Processing server: ' concat v_member);
    execute immediate 'create or replace alias qtemp.http_server_mbr for QUSRSYS.QATMHINSTC(' concat v_member concat ')';
    
    select rtrim(
    substr(
      charfield, locate_in_string(charfield, '-d', 1, 1) + 3, locate_in_string(
        charfield, ' ', locate_in_string(charfield, '-d', 1, 1) + 3, 1) -
        (locate_in_string(charfield, '-d', 1, 1) + 3))) concat '/' concat
    rtrim(substr(charfield, locate_in_string(charfield, '-f', 1, 1) + 3,  locate_in_string(
        charfield, ' ', locate_in_string(charfield, '-f', 1, 1) + 3, 1) -
        (locate_in_string(charfield, '-f', 1, 1) + 3))) into server_config_path
      from qtemp.http_server_mbr;
   
    set sql_stmt_text = 'select substr(line, 1, locate_in_string(line, '' '', 1, 1)) ,
                                rtrim(substr(line, locate_in_string(line, '' '', 1, 1) + 1, 10000)) 
                           from table(qsys2.ifs_read(''' concat server_config_path concat '''))
                         where upper(line) like ''%FRCA%'' order by line_number asc';
    -- call systools.lprintf('Stmt: ' concat sql_stmt_text);
    begin
      declare continue handler for not found set at_end2 = 1;
      prepare FRCA_setup_stmt from sql_stmt_text;
      open FRCA_setup_csr;
      set at_end2 = 0;
      fetch from FRCA_setup_csr into v_attribute_name, v_attribute_value;
      while (at_end2 = 0) do
        pipe(v_member, v_attribute_name, v_attribute_value, server_config_path);
        fetch from FRCA_setup_csr into v_attribute_name, v_attribute_value;
      end while;
      close FRCA_setup_csr;
    end;

    set at_end = 0;
    fetch from HTTP_servers_csr into v_member;
    end while;
    close HTTP_servers_csr;       
    return;
end; 
stop;

--
-- Do we have any HTTP Servers that are configured to use FRCA?
--
-- https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/rzaie/rzaiemod_as_cache.html
--
select *
  from table (
      systools.frca_info()
    );
stop;