-- ----------------------------------------------------------------------------
-- Script Name : st_status.sql
-- Author      : Pete Finnigan
-- Date        : November 2023
-- ----------------------------------------------------------------------------
-- Description : SQL Firewall
--                  Checks the status of the SQL firewall
-- ----------------------------------------------------------------------------
-- Maintainer  : Pete Finnigan (http://www.petefinnigan.com)
-- Copyright   : Copyright (C) 2003 - 2023 PeteFinnigan.com Limited. All rights
--               reserved. All registered trademarks are the property of their
--               respective owners and are hereby acknowledged.
-- ----------------------------------------------------------------------------
-- License     : This software is licensed to you by PeteFinnigan.com Limited. All
--               rights and ownership and copyright in the software are retained by
--               PeteFinnigan.com Limited in all possible cases. Posession of this
--               software does not infer any additonal rights. If you receive this
--               software without these copyright notices and license text, this text,
--               license and copyright still applies. This text, copyright and license
--               must not be removed under any circumstances. This applies to all 
--               text displayed as comments up until and including the version history,
--               This text also applies to any copyright, banner or other text 
--               refering to PeteFinnigan.com Limited ownership that is output by
--               the program.
--
--               This software is free to use BUT it is NOT open source and NOT GPL 
--               or any similar license and it is NOT in the public domain.
--
--               You are permitted to use this software commercially or privately 
--               provided these notices or banners as described are not removed. You
--               may modify the software and use it internally but this does not
--               infer any additonal rights in the software. i.e. if you delete
--               some of our code or change variable names or add features that does 
--               not make it your code and does not give you the right to remove 
--               our ownership in this software shown in these notices. This software must
--               not be made available or published in anyway, any language, any 
--               modified form or original form except by PeteFinnigan.com Limited. 
--               You must not incorporate this software into any free or commercial 
--               product or software and you must not sell or give away any software that 
--               includes this sofware. 
--               
--
--               In short this text is not written by a lawyer so please respect the 
--               intent that you can use or modify it freely but not give it away 
--               yourself or take away our right to attribution.
--               If someone else needs a copy please ask them to come to PeteFinnigan.com
--               Limited and we will happily let them also have a free copy. Also
--               as you may expect freely use does not include adding this software to 
--               a commercial or free product (without our permission) but you
--               can use it internally in projects. We put our time into the free scripts
--               on our website or training courses and give these tools away for free and 
--               in return we expect our copyright and ownership to always remain. 
--               We like to help people but we also want to benefit from the fact
--               our name becomes known through these scripts and tools and software 
--               that we make. We hope this makes sense.
--
-- ----------------------------------------------------------------------------
-- TODO
--
-- Version History
-- ==================
--
-- version  who desc
-- =======  === =========================
-- 0.1.1    pxf first issue

-- ----------------------------------------------------------------------------
-- spoof.sql

set lines 220

spool sf_status.lis

set echo on

-- ----------------------------------------------------------------------------
-- Connect to SYSDBA and check the status
-- ----------------------------------------------------------------------------

set echo off
prompt - connect to SYS and check the status...
prompt press any key to continue....
pause

-- connect as sys
@@cs.sql
set echo on
sho user


select status
from    dba_sql_firewall_status
/

set echo off
prompt - Check the SQL Firewall views...
prompt press any key to continue....
pause

col username for a10
col ip_address for a15

select  username,
        ip_address
from dba_sql_firewall_allowed_ip_addr
/

col os_program for a20

select  username,
        os_program
from dba_sql_firewall_allowed_os_prog
/

col os_user for a10

select  username,
        os_user
from dba_sql_firewall_allowed_os_user
/

col allowed_sql_id for 999999
col sql_text for a30
col accessed_objects for a30
col top_level for a9

select  username,
        allowed_sql_id,
        substr(sql_text,1,30) sql_text,
        substr(accessed_objects,1,30) accessed_objects,
        top_level
from dba_sql_firewall_allowed_sql
/

col generated_on for a20
col status for a8
col top_level_only for a14
col enforce for a14
col block for a15

select  username,
        to_char(generated_on,'DD-MON-YYYY HH24:MI:SS') generated_on,
        status,
        top_level_only,
        enforce,
        block
from dba_sql_firewall_allow_lists
/

col last_started_on for a20
col last_stopped_on for a20

select  username,
        top_level_only,
        status,
        to_char(last_started_on,'DD-MON-YYYY HH24:MI:SS') last_started_on,
        to_char(last_stopped_on,'DD-MON-YYYY HH24:MI:SS') last_stopped_on
from dba_sql_firewall_captures
/

col session_id for 9999999999999999999
col command_type for a15
col current_user for a10
col client_program for a15
col os_user for a10
col ip_address for a12


select  username,
        session_id,
        command_type,
        substr(sql_text,1,30) sql_text,
        substr(accessed_objects,1,30) accessed_objects,
        current_user,
        top_level,
        client_program,
        os_user,
        ip_address
from dba_sql_firewall_capture_logs
/

col login_time for a20

select  session_id,
        username,
        to_char(login_time,'DD-MON-YYYY HH24:MI:SS') login_time,
        ip_address,
        client_program,
        os_user
from dba_sql_firewall_session_logs
/

select  command_type,
        substr(sql_text,1,30) sql_text,
        substr(accessed_objects,1,30) accessed_objects
from dba_sql_firewall_sql_logs
/

col status_updated_on for a20
col exclude_jobs for a12

select  status,
        to_char(status_updated_on,'DD-MON-YYYY HH24:MI:SS') status_updated_on,
        exclude_jobs
from dba_sql_firewall_status
/

col cause for a10
col firewall_action for a7
col occurred_at for a20

select  username,
        command_type,
        substr(sql_text,1,30) sql_text,
        substr(accessed_objects,1,30) accessed_objects,
        top_level,
        ip_address,
        os_user,
        client_program,
        cause,
        firewall_action,
        to_char(occurred_at,'DD-MON-YYYY HH24:MI:SS') occurred_at
from dba_sql_firewall_violations
/

spool off