Pete Finnigan's Oracle Security Weblog

This is a short blog on Unified auditing and the so called Mixed Mode. If you use Oracle from 12c to 21c then by default Unified Auditing starts in Mixed Mode. Unified Auditing needs to be re-linked to enable unified auditing pure mode where only unified auditing is working and enabled.

We can check the status of Unified auditing as follows:

SQL> select value from v$option where parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

SQL>

FALSE means in my database that Unified Auditing is in Mixed mode. This means it works and generates audit records for policies that are enabled. We can do an action that is caught by my audit trail:

SQL> create user aud3 identified by aud3;

User created.

SQL>

Checking the unified audit trail we can find:

SQL> @sc_print 'select * from unified_audit_trail where action_name=''''CREATE USER'''''
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from unified_audit_trail where action_name=''CREATE USER''','''','''''');
Executing Query [select * from unified_audit_trail where action_name='CREATE
USER']
AUDIT_TYPE                    : Standard
SESSIONID                     : 1801490805
PROXY_SESSIONID               : 0
OS_USERNAME                   : pete
USERHOST                      : WORKGROUP\OFFICE-HACKER
TERMINAL                      : OFFICE-HACKER
INSTANCE_ID                   : 1
DBID                          : 254274359
AUTHENTICATION_TYPE           : (TYPE=(DATABASE));(CLIENT
ADDRESS=((PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=64925)));
DBUSERNAME                    : SYS
DBPROXY_USERNAME              :
EXTERNAL_USERID               :
GLOBAL_USERID                 :
CLIENT_PROGRAM_NAME           : sqlplus.exe
DBLINK_INFO                   :
XS_USER_NAME                  :
XS_SESSIONID                  :
ENTRY_ID                      : 4
STATEMENT_ID                  : 75
EVENT_TIMESTAMP               : 21-JUL-25 14.39.32.654441
EVENT_TIMESTAMP_UTC           : 21-JUL-25 13.39.32.654441
ACTION_NAME                   : CREATE USER
RETURN_CODE                   : 0
OS_PROCESS                    : 21361
TRANSACTION_ID                : 0000000000000000
SCN                           : 41014126
EXECUTION_ID                  :
OBJECT_SCHEMA                 :
OBJECT_NAME                   : AUD3
SQL_TEXT                      : create user aud3 identified by *
SQL_BINDS                     :
APPLICATION_CONTEXTS          :
CLIENT_IDENTIFIER             :
NEW_SCHEMA                    :
NEW_NAME                      :
OBJECT_EDITION                :
SYSTEM_PRIVILEGE_USED         : SYSDBA, CREATE USER
SYSTEM_PRIVILEGE              :
AUDIT_OPTION                  :
OBJECT_PRIVILEGES             :
ROLE                          :
TARGET_USER                   :
EXCLUDED_USER                 :
EXCLUDED_SCHEMA               :
EXCLUDED_OBJECT               :
CURRENT_USER                  : SYS
ADDITIONAL_INFO               :
UNIFIED_AUDIT_POLICIES        : ORA_SECURECONFIG, EVE_1_5
FGA_POLICY_NAME               :
XS_INACTIVITY_TIMEOUT         :
XS_ENTITY_TYPE                :
XS_TARGET_PRINCIPAL_NAME      :
XS_PROXY_USER_NAME            :
XS_DATASEC_POLICY_NAME        :
XS_SCHEMA_NAME                :
XS_CALLBACK_EVENT_TYPE        :
XS_PACKAGE_NAME               :
XS_PROCEDURE_NAME             :
XS_ENABLED_ROLE               :
XS_COOKIE                     :
XS_NS_NAME                    :
XS_NS_ATTRIBUTE               :
XS_NS_ATTRIBUTE_OLD_VAL       :
XS_NS_ATTRIBUTE_NEW_VAL       :
DV_ACTION_CODE                :
DV_ACTION_NAME                :
DV_EXTENDED_ACTION_CODE       :
DV_GRANTEE                    :
DV_RETURN_CODE                :
DV_ACTION_OBJECT_NAME         :
DV_RULE_SET_NAME              :
DV_COMMENT                    :
DV_FACTOR_CONTEXT             :
DV_OBJECT_STATUS              :
OLS_POLICY_NAME               :
OLS_GRANTEE                   :
OLS_MAX_READ_LABEL            :
OLS_MAX_WRITE_LABEL           :
OLS_MIN_WRITE_LABEL           :
OLS_PRIVILEGES_GRANTED        :
OLS_PROGRAM_UNIT_NAME         :
OLS_PRIVILEGES_USED           :
OLS_STRING_LABEL              :
OLS_LABEL_COMPONENT_TYPE      :
OLS_LABEL_COMPONENT_NAME      :
OLS_PARENT_GROUP_NAME         :
OLS_OLD_VALUE                 :
OLS_NEW_VALUE                 :
RMAN_SESSION_RECID            :
RMAN_SESSION_STAMP            :
RMAN_OPERATION                :
RMAN_OBJECT_TYPE              :
RMAN_DEVICE_TYPE              :
DP_TEXT_PARAMETERS1           :
DP_BOOLEAN_PARAMETERS1        :
DP_WARNINGS1                  :
DIRECT_PATH_NUM_COLUMNS_LOADED:
RLS_INFO                      :
KSACL_USER_NAME               :
KSACL_SERVICE_NAME            :
KSACL_SOURCE_LOCATION         :
PROTOCOL_SESSION_ID           :
PROTOCOL_RETURN_CODE          :
PROTOCOL_ACTION_NAME          :
PROTOCOL_USERHOST             :
PROTOCOL_MESSAGE              :
DB_UNIQUE_NAME                : XE
OBJECT_TYPE                   :
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, a unified audit record was created for the CREATE USER command that I issued.

Now check the standard audit settings:

SQL> sho parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /opt/oracle/admin/XE/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_common_systemlog       string
unified_audit_systemlog              string
SQL>

As we can see SYSDBA audit is OFF but the standard audit trail setting audit_trail is set to DB, so is turned on. Are there any standard audit rules:

SQL> select count(*) from dba_stmt_audit_opts;

  COUNT(*)
----------
       257

SQL> select count(*) from dba_priv_audit_opts;

  COUNT(*)
----------
       242

SQL> select count(*) from dba_obj_audit_opts;

  COUNT(*)
----------
        25

SQL>

In summary, yes, quite a lot of standard audit as well as the around 30 unified audit policies we have set up in this database. If we turn off standard audit what happens to unified audit. First lets turn off standard audit:

SQL> alter session set container=cdb$root;

Session altered.

SQL>
SQL> alter system set audit_trail=none scope=spfile;

System altered.

SQL>

After restart of the database check the audit_trail parameter again:

SQL> sho parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /opt/oracle/admin/XE/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE
unified_audit_common_systemlog       string
unified_audit_systemlog              string
SQL>

Now the standard audit trail is turned off. First truncate the unified audit trail:

SQL> get del
  1  select count(*) from unified_audit_trail
  2  /
  3  begin
  4  dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified
  5  ,use_last_arch_timestamp=>false);
  6  end;
  7  /
  8* select count(*) from unified_audit_trail
  9  .
SQL> @del

  COUNT(*)
----------
         1


PL/SQL procedure successfully completed.


  COUNT(*)
----------
         0

SQL>

This immediately is interesting as when we truncate the UNIFIED AUDIT TRAIL there should be an audit record created there that captures this clear event. i.e. unified audit automatically generates an audit record if the audit trail is cleared. We removed one record from UNIFIED_AUDIT_TRAIL with DBMS_AUDIT_MGMT but the delete/clean record was not created. Now lets try and create a database user as before:

SQL> create user aud4 identified by aud4;

User created.

SQL>

Now, check the unified audit trail to see if the CREATE USER was captured.

SQL> set serveroutput on
SQL> @sc_print 'select * from unified_audit_trail where action_name=''''CREATE USER'''''
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from unified_audit_trail where action_name=''CREATE USER''','''','''''');
Executing Query [select * from unified_audit_trail where action_name='CREATE
USER']

PL/SQL procedure successfully completed.

SQL> select count(*) from unified_audit_trail;

  COUNT(*)
----------
         0

SQL>

No audit record.

So, yes by default Oracle is in Mixed Mode after 12c to 23c when standard audit is removed BUT UNIFIED AUDIT only works in Mixed Mode if the existing standard audit is still turned on.

Be aware of this if you create unified policies without enabling pure mode. If the standard audit is disabled then so is your Unified audit trail!

#oracleace #sym_42 #unified #audit #oracle #database #security #audittrail #audit #

We have a product PFCLATK that is now version 5.0.64.1506 but started out back in 2009 as a way to get customers to deploy useful audit trails quickly. It was perceived then and still now that adding audit trails or activity monitoring to an Oracle database is a big and onerous task and a threat to performance of the database so often the easy answer is to not bother. Hmmm.

Back in 2009 we are asked to help a customer who needed a comprehensive audit trail but it must be a one-click install (one script in SQL*Plus) and must be managed by itself and send out alerts only when necessary whilst not growing too large. They had no staff to design or manage an audit trail. We created PFCLATK as SQL and PL/SQL toolkit at the time to satisfy this requirement and it has been used in many sites to implement audit trails without too much resources in terms of people or machines/storage etc.

PFCATK has a lot of features but the main one is still the first that it can be configured in minutes and deployed with one script and be useful straight away. It detects all sorts of issues from SQL Injection to security changes to privilege escalation and more. The PFCLATK features page has more details as well as the PFCLATK home page linked above.

Above shows some sample alerts from my 21c database.

If your database is breached then often in our experience it happened a long time ago and without an audit trail it is difficult to perform a live response or forensic analysis with tools such as PFCLForensics. This is why it is important to get an audit trail up and running in every database to detect abuse or potential hacks or breaches.

PFCLATK has some additional extra features. One special features is the ability to SCORE the database for security. We can also SCORE the alerts as well. The security of the database needs to stay high and the alerts SCORE needs to stay low:

PFCLATK can be deployed quickly and it can be installed and forgotten and it just sits there doing its thing sending out alerts.

Because we can score the database security and alerts security adaptive security and adaptive auditing can be used. Think of defcon 5 going to defcon 1. We detect the change and can turn on more auditing automatically. The power of this means that under normal quiet circumstances sufficient audit is collected but is something is deemed to be going on the audit can be increased to collect more. This means we can detect an attack and collect more details to help understand the attack.

Another feature of PFCLATK is the ability to act as a Black Box Flight Recorder so that a snapshot of the audit trails and alerts can be downloaded or written on a regular basis and also on detection of a serious even. This can then be used to aid forensic analysis of a database.

#oraclace #sym_42 #oracle #database #security #audit #databreach #hacking #forensics #liveresponse #audittrail #sqlinjection #privilege #escalation #activity #monitoring

Emad just made a blog post - Oracle 23ai Privilege Escalation From GRANT ANY ROLE to DBA Role - that shows how he escalated from GRANT ANY ROLE to DBA.

There are some issues with his example but I will come back to that in a minute.

Emad states in the post that Oracle do not accept his example as a security exploit. I agree with Oracle; it is just the way Oracle works and I will explain why in a minute.

The issue is that people think the goal is DBA but its lots of roles and permissions, even single ones are just as dangerous as DBA as I show in my Oracle Security training. A good example is ALTER USER. If i have ALTER USER I can change another users password, connect as that user and then use their access and rights. This is an escalation BUT its not an exploit as I gave a user ALTER USER.

A good example would be if i gave someone a key to my house and then they entered my house using the key and stole everything; the real problem is that I gave them the key.

Another example could be that I have CREATE ANY PROCEDURE and i can overwrite a procedure in another schema and then in my new procedure I can use privileges of the owner, i.e. steal them. There are protections to limit this and if we are in a PDB we cannot exploit a common user.

A real world example of this is i give a car key to someone and in that car there is the key to my house. If the person with the car key accesses the car then they can find the house key and access my house.

In general in Oracle the issue is not escalation to DBA; there are so many individual rights that could be exploited. Maybe an attacker does not need DBA but just needs SELECT on a specific table. In general every user should have exactly the privileges necessary and no more; i.e. least privilege.

GRANT ANY ROLE is an example of a potential privilege which I also cover in my Oracle Security training. If a user has a privilege (for example GRANT ANY ROLE) then he also potentially has all the privileges he can grant himself by granting himself every role in the database. This is the way Oracle works; we need a privilege to grant any role and there are serious implications with this being granted to anyone!

On to Emad's example. Let us re-run his example:

C:\del_xps\c\mac_nov_2019\____atk\5_0_64_1506>sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 3 08:52:49 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

SQL> @cs
Connected.
USER is "SYS"
SQL> create user tom identified by tom123;

User created.

SQL> grant create session, grant any role to tom;

Grant succeeded.

SQL> alter user tom default role all;

User altered.

SQL> connect tom/tom123@//192.168.56.33:1539/xepdb1
Connected.

SQL> grant dba to tom;
grant dba to tom
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> grant dba to hr;
grant dba to hr
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create user tom2 identified by tom123;
create user tom2 identified by tom123
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> select * from orablog.bof_pay_details;
select * from orablog.bof_pay_details
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> grant imp_full_database to tom;

Grant succeeded.

SQL>
SQL> set role all
  2  /

Role set.

SQL> select * from orablog.bof_pay_details;

        ID PAYMENT_ID
---------- ----------
NAME_ON_CARD
--------------------------------------------------------------------------------
CC34
--------------------------------------------------------------------------------
START_DAT END_DATE  LAST
--------- --------- ----
         1          1
Mr David Bentley
C795E9199A78988F3D375D5297AED40342AAF4A32FE28A2D
01-FEB-11 01-AUG-16 3457


        ID PAYMENT_ID
---------- ----------
NAME_ON_CARD
--------------------------------------------------------------------------------
CC34
--------------------------------------------------------------------------------
START_DAT END_DATE  LAST
--------- --------- ----
         2          2
Mr Martin Chisholm
E634E4CF55C484B4E8924F5CF3C79D29D68ACDD2FC06F8BC
01-APR-12 01-OCT-16 6678


SQL>
SQL> create user tom identified by tom123;

User created.

SQL>
SQL> grant dba to tom;

Grant succeeded.

SQL>

problem 1 - So, we did not need to grant DBA to TOM as we could read the data in the ORABLOG.BOF_PAY_DETAILS table and create TOM2 after we granted IMP_FULL_DATABASE as this role has CREATE USER and SELECT ANY TABLE. We didn't need DBA to do the things.

As you can see we did grant DBA as Emad did but why? This is the real question. What does IMP_FULL_DATABASE have that allows us to grant DBA when GRANT ANY ROLE did not allow us to GRANT DBA?

Problem 2 - This is easy; the extra privilege needed is GRANT ANY PRIVILEGE as some privileges in DBA need this to be granted. So run the example again after dropping the user TOM:

SQL> grant create session, grant any role, grant any privilege to tom;

Grant succeeded.

SQL>

SQL> connect tom/tom123@//192.168.56.33:1539/xepdb1
Connected.
SQL> grant dba to tom;

Grant succeeded.

SQL>

So there are two points, we didn't need DBA to select and create users; IMP_FULL_DATABASE allowed that and the reason we could not grant DBA when we had GRANT ANY ROLE is that we also needed GRANT ANY PRIVILEGE.

This is the way Oracle works; we must understand least privileges and not grant any sweeping rights that allow privilege propagation. I have been teaching this to attendees of my Oracle security classes for years. Don't get hung up on the DBA role; IMP_FULL_DATABASE is just as good for an attacker

#oracleace @oracleace #sym_42 #oracle #security #privilege #escalation #roles #grants #databreach #hacking #training

Some brief news on Oracle and Security and personal news!!

I was emailed a couple of weeks or so ago that I was awarded membership of the Oracle ACE program for now until end of May 2026 and yesterday I received the email/pdf version of my ACE certificate. Great to be part of the ACE program again for another.

Today as I write this its the 26th June 2025; the 29th June is coming up on Sunday and that is a day that has two events for me.

The first connection to June 29th was 30 years ago and is that I passed my degree in Electronic and electrical systems on June 29th 1995 and got a first class honours. I got an average mark in all subjects of 79.xx % over the whole degree course. in Some subjects in some years I got 100% mark; for instance in Math in the first year. My final year project was writing a multi-tasking operating system in C and some intel assembler for low level tasks. The OS also had a TUI (Text User Interface) that was similar in style to products like Borland C++ or Turbo Pascal. So windows and menus and dialogs and status bars etc but in direct memory access and using text characters written direct to the screen. The OS also had obviously multi-tasking and also inter process communication with semaphores, task blocks, task switching, system calls, waits, messages between applications and more. It was possible to create a demo program that ran 4 or more separate programs running each in separate TUI windows and using sync and messaging between processes.

I started to write the OS on 13th June 1994 as the first entry in my log book one showed and the last entry in log book 2 showed the 11th May 1995:

The photo shows my two log books and my actual degree project / thesis that was handed in. The thesis was 294 pages long and I was told by the printers mine was way bigger than anyone else's.

I got a first class honours degree in electronics and also won the IEEE prize for the best student in this category across the country for 1995. BUT, i have not used the details of the degree since I completed it as I was made redundant from my job as a designer in the railway and I moved into IT and programming and eventually into Oracle security which of course I still do today.

My second connection to June 29th was 49 years years ago when my friend Paul and I were at Selby railway station and we watched Deltic 55004 on the 17:00 from London Kings Cross pass Selby at 19:15 and a half:

As the report shows and as I remember. We saw something big dragging on the floor from one of the carriages (numbered E6111). We decided to report this to the station manager who didn't believe us at first as we were 13 years old. We showed him how the dragging piece had ripped big chunks of wood from the crossing next to the swing bridge and then he believed us. He telephoned the signal man and had the train stopped. The side of the bogie frame was dragging on the floor as the axle end had sheared off. The train was stopped about 6 miles north of Selby and was not yet at full speed. We stopped an express train from almost certainly crashing

We became big news and there were articles in every major newspaper and also local ones. The press came to see us at home and other places. Here is a couple of articles from the time. First the Daily Mail

And an article after one of the trips:

This picture shows Paul and I after we went on a free trip from York to London Paddington and then on a brand new (at the time) HST to Bristol and back to York. We had a man from York railway PR accompany us on the trip. The photo was taken by the press when we got back. We were given some brochures for the HST and the railway museum in York. I still have these:

We also had a trip before that with British Railways who took us in a cab ride to London in a class 47 and then for lunch in the post office tower revolving restaurant:

One thing that stands out now is why did the press make so many mistakes (names wrong, spelled wrong, ages wrong and more) and also why did they print our addresses in national newspapers.

Some time later Selby council also awarded us a silver limited Selby Abbey Festival medal:


It was nice to get a medal and have free trips but the best thing was saving peoples lives at the time.

Sorry, not much Oracle security in this post; Some posts coming soon though on new Oracle Security research; the key vault for DBMS_CRYPTO and more on an interpreter written in PL/SQL and a new subject. I have quite a lot of blog materials ready to post when i get a chance over the coming weeks.

June the 29th seems to be a lucky day for me, passing my 1st class honours degree 30 years ago on that date and stopping a train crash 49 years ago

#oracleace #oracle #security #traincrash #degree #electronics #june29th

This is a short history of our PFCLScan product and therefore also a history of the other apps now built on top of PFCLScan such as PFCLObfuscate, PFCLCode, PFCLForensics and more.

Obviously after such a long time and without keeping detailed notes of the development some of this is based on hazy long ago memories. No one else probably can contradict any of this so please bear in mind some of the detail may not be 100% accurate but its still a valid history of how it was developed.

I might come back and enhance this in the future with more details and I might move it to the PFCLScan product pages but for now it is a blog.

I started PeteFinnigan.com Limited on the 12th February 2003 and my goal was to provide services, training and eventually products to help customers secure data in their Oracle databases. Of course I also wanted to help the public generally as well through blog and other posts on my website to secure data in their Oracle databases - so I have posted a huge amount of free content and papers, talks and of course tools written in SQL and PL/SQL. One of my key goals in creating software was to make it easier for me personally review customers databases by having tools that I could easily use BUT the goal was to also create commercial tools to do the same and sell licenses for these.

So just after I started PeteFinnigan.com Limited on the 24th April 2003 I started a C program called oscan.exe that would be the core engine of my database scanner. The main C file has a comment for version 0.0.1 on the 24 April 2003 and is shown here:

/*============================================================================*/
/*   File Name   : oscan.c                                                    */
/*   Author      : Pete Finnigan                                              */
/*   Date        : April 2003                                                 */
/*   Name        : OSCAN                                                      */
/*============================================================================*/
/*   Description : This program is the PeteFinnigan.com Limited Oracle        */
/*                 database security scanner. The project is conceived to     */
/*                 replace the current PL/SQL scripts and shell scripts and   */
/*                 may form part of a commercial scanner offering.            */
/*============================================================================*/
/*   maintainer  : Pete Finnigan (http://www.petefinnigan.com)                */
/*   Copyright   : Copyright (C) 2003, 2008, 2009, 2010 PeteFinnigan.com      */
/*                 Limited                                                    */
/*                 All rights reserved. All registered trademarks recognised  */
/*                                                                            */
/*                 The contents of this file are protected by copyright. Any  */
/*                 unauthorised copying, duplication of its contents are in   */
/*                 breach of the copyright.                                   */
/*                                                                            */
/*============================================================================*/
/*   History                                                                  */
/*                                                                            */
/*   Wh	    Ver			Date            Description                           */
/*   ===    ===			==========      ===========                           */
/*   PF     0.0.1       24-Apr-2003     First release                         */
/*   PF	    0.1.1		26-Jun-????	    **previous updates removed***         */
/*   PF     0.1.2	    29-Dec-????     Move to VCC EXP                       */
/*   PF     0.1.3       28-Mar-????     Added code access functions           */
/*   PF     0.1.6       28-Jun-????     Work to add in command loop           */
/*   PF     0.1.7       31-Oct-2007     General progress                      */
/*   PF     0.1.9       01-Apr-2008     Move to OSCAN                         */
/*   PF     0.1.10      02-Apr-2008     Test compile with cleaned structure   */
...

The file was clearly updated in 2010 and as the header did not contain comments after this time, this is when I started to add changed to release notes instead but the earlier comments are valid.

As writing in C is much slower than writing in SQL and PL/SQL I quickly put the C program to one side and decided to create a SQL - PL/SQL scanner for me to use in customer engagements and not to sell. The SQL*Plus script was a wrapper around calls to specific scripts and these scripts acted as internal functions. This allowed the main script to be a container for hundreds of checks and reports with just define statements to set names/values etc and then call each separate script (like a function call). This set of scripts grew to 40-50,000 lines or more of code in SQL and PL/SQL but was useful for me to perform security audits. But, it was not useful to sell though as a product as it was just scripts and no reports/words/advice.

At some point I decided to go back to my C based scanner oscan.exe and started work on it again so that eventually I could sell it and of course use it internally on consulting engagements.

This restart, from the comments above happened sometime in 2007 but i do not remember exactly when. I completed the first version of oscan.exe to load a policy of checks and run it against an Oracle database. Already it had some sophistication in that it could execute any SQL or PL/SQL based check in one policy. It also had built in trace and logging to help with support. The whole program has its own config file that can be used to control it. The policies were stored in XML files and as part of that development I created an XML parser in C to load the policies. The output from processing a policy was also stored in XML files.

I made a decision to not use a database as the repository of the tool as then that database would need to be locked down and protected.

I also decided to use OCI (Oracle Call Interface - not cloud) to connect to the database and do SQL and PL/SQL and some other bits such as breaking a running session.

I made a blog post (C code API to encapsulate OCI) showing oscan.exe running for the first time publicly on t on April 7th 2008. So, the core engine was done and running checks in policies then:

C:\oscan -c oscan.conf -v

OSCAN: Release 0.1.12 - Alpha on Mon Apr 07 11:18:26 2008

Copyright (c) 2003, 2008, PeteFinnigan.com Limited. All rights reserved.

[2008 Apr 07 10:18:26] Logger: Starting OSCAN...
[2008 Apr 07 10:18:26] Logger: Running Scanner
....
[2008 Apr 07 10:27:32] Logger: Closing Down OSCAN

Sometime after as we had only a C program to run a single policy passed in and it was a Windows command line program I needed to make it a proper Windows application. At this time it was not called PFCLScan. As i registered the domain on the 6th July 2009 then I assume that on that day I came up with the name PFCLScan.

The GUI originally started as a C++ / MFC program and whilst I could probably find the exact date of that development I wont as it was a 2 week detour. I started on C++ / MFC as the engine was in C and the plan originally was to move oscan.exe into the Windows program as C and C++ can play together. The learning curve in MFC was high and whilst fairly good looking GUI could be created quickly it was much harder to then change, tweak, add things to. Probably I should have stayed with MFC but i wanted progress faster so switched the GUI to vb.net.

This switch meant that we needed to call oscan.exe from the GUI as a command line tool. This was a good choice as it meant that PFCLScan can also be run from the command line very simply and indeed it still can be.

At this point I needed to create projects that could contain any number of policies that are then run by oscan.exe. So the idea of projects was born where a project is also an XML file and it contains any number of policies and each of these policies contain any number of actual checks. This meant we can define a project in the GUI and then run each policy through oscan.exe. This also meant I had the opportunity to create more sophistication. I realised that I could create a check in SQL or PL/SQL that took input from a previous policy or check and fed the values from any column of data to the new check. This allows checks to be written where we reduce database trips. A good example is that we have a policy/check that gets a list of default users or default roles and these are fed into subsequent checks that need a list of default users and roles as text to be added to SQL or PL/SQL.

We also added loop checks; a variation on the static dependencies above. This means that the results of one check - i.e. rows of result data can be fed line by line into a subsequent check.

I also saw the opportunity to add libraries where a check is used more than once it is a library and the code for the library can be loaded in and executed. We can of course mix and match and libraries / static dependencies (think #define in C) and loops all work with libraries as well. A library is just a policy / check so can be written and edited through the GUI.

The programs used to process the static dependencies and loops and libraries are lock.exe and load.exe so I know roughly when these ideas/features were added; these programs were started on the 10th and 11th January 2010 so this development at least started then.

At this time I also recognised that PFCLScan needed a reporting language so I invented one. I also for the first time saw the advantage of recursion and re-use. So the first job for the report language (PFCLReport) was to create projects from the passed in data obtained from the user via a Windows wizard - name, file name, location and type of project and a template for the project. The repo.exe tool was started on 15th January 2010. The report tool is simple and loop based. We have a template which can be any text file and this is parsed and any data found substituted in the output where there are variables. The language is rich and has hundreds of variables and is loop based. So you can loop over databases that have been scanned and policies and checks. The language is easy to use

So, I used repo.exe in the GUI to go off and create a project when a user chose to start a new project. Re-use!

Around the same time I came up with the idea of adding plugins to PFCLScan. A plugin is simply a project in PFCLScan. So it can be created in the GUI and policies and checks added and then converted into a plugin - it can be run as a project in the GUI. Plugins can be run ad-hoc from the plugins screen or can be run from hook points throughout the application. A hook point can have any plugin associated with it. This means an end user could if they wished write a new plugin and replace ours. Powerful.

The first two plugins created were to test the connection to a database and test the connection to a server with ssh.

So around the 2009 / 2011 timeframe we added the ability to run shell commands on a server. To allow a plugin to connect to the database through OCI in oscan.exe we also at this time added Lua to oscan.exe. This was a neat extendable way to do this and also meant we can now write checks in Lua!!

Most of the core features were added around this 2009 / 2011 timeframe. I showed a picture of PFCLScan on September 1st 2009 presumably using the old mechanism for testing a connection as this was before plugins. Search for blog post (A book, a database scanner and a magazine column and a few bugs) to see this picture.

We started to add applications with PFCLObfuscate as the first one in 2019 / 2020 by building apps on the top of PFCLScan so that they could easily use the core features of the main product and remove the need to rewrite things again in each app. This also was to make build and deployment easier as we now had one software build rather than separate ones for each product. The natural next step was also to use PFCLScan plugins to build and activate PFCLScan itself. Full circle!

I added the product website around March 2017. Version 1.3 was released on the 18th October 2013 and version 3 on 11 July 2019 and version 4 before xmas 2024. Version 5 (2025) is being worked on now.

The first public demo was on May 13th 2013 in Edinburgh and the first sale was on the 10th February 2013

We are enow working on version 2025 and also adding a new product as an App, PFCLUserRights that will do a detailed analysis of users and their privileges in the database plus updates to all the apps and the core scanner.

So, that is a brief history of PFCLScan and apps and as I said I might come back and extend this in the future with more details

#oracle #security #pfclscan #vulnerability #scanner #breach #databreach #datasecurity

It has been a long time coming but I have finally got this website running on HTTPS / SSL. Google have been pushing webmasters and site owners to move to HTTPS for more than 10 years. Google in fact stated in 2014 that HTTPS should be used and stated that it was a ranking signal. In 2017 Google started marking sites that collected credit card and/or password information as insecure. I didn't move the site to https as it does not collect and data and is static. But almost every site now is https so it was time to change. We are a security company!

We have made thousands of changes to the site to ready it for the switch over and tested HTTPS first. It's quite a big website and we needed to make sure nothing would break. The site moved to HTTPS last night just before midnight and we still have had to fix things most of today to get it working properly. The main parts of the site were stable last night but lots of peripheral parts had to be tweaked and changed including this blog editor. The blog pages are actually static and no database behind it.

So it's live on https and we have set up redirects from all old URLs so you can still find us on old links and get redirected to the new ones

I had a website before PeteFinnigan.com but registered and started to put content on this site on the 3rd may 2001, 24 years ago!!

#oracle #security #website #https

One of the goals of creating an interpreter written in PL/SQL to execute a custom language was for our use in our tools. We wanted to be able to ship PL/SQL and customise it after its deployed without re-compiling the PL/SQL but also to add a secondary level of security.

Each piece of PL/SQL that is part of an application is visible to anyone who has access to the database in USER_SOURCE, ALL_SOURCE and DBA_SOURCE depending on privileges. Even if the code is wrapped then it can be unwrapped although even if 10g and above is easy to unwrap and no unwrappers are available for 9ir2 and below it is still possible; I can do it!
What I wanted was a combination of two things:

• Use code in our PL/SQL that is not visible in the SOURCE$ tables or views so that someone with direct database access cannot as easily work out how our code works


• Extend an application at run time without the need to re-compile the PL/SQL or allow the user of the application to do so. If we let end users change the PL/SQL the there is a
  danger of security issues and the need to allow elevated access to the PL/SQL interface

If we allow end users or more likely power users to extend a PL/SQL application at run time then we must control the level of extension and how its extended and what features or aspects of the original application are exposed to the scripting interface.

In other words the end user/customiser can write as much or as little scripting language as they want BUT we as designers control how the script language is exposed to the PL/SQL application.

A good example/model is the embedding of Lua into C programs. We do this by embedding Lua into all of our products so that rules in PFCLScan can be written in Lua or generic obfuscation can be done in PFCLObfuscate using Lua and rules in our PL/SQL code analyser PFCLCode can be written in Lua; in fact all of our products because they are built on PFCLScan can use Lua as part of then checks and functionality.

Lua is a good example of extending a product. In the case of PFCLScan where the core engine is written in C we have embedded Lua.

The extension to add Lua is done at compile time of our C based applications; in the case of PFCLScan we compile in the Lua engine and we provide an interface to the Lua engine so that scripts can be loaded and then executed. To be able to do something useful we needed to extend Lua to give it access to our C based engine. We added C functions written using the Lua API to extend Lua from C so that when they are also compiled in with the Lua engine scripts can be written in Lua that also execute code against our engine BUT from Lua.
Some examples of the extension functions we added to Lua for PFCLScan are first that we add functions to Lua written in C:

• PFCLOpen() �" this is used to open previous checks so that results can be parsed and worked on in Lua


• PFCLLine() �" we can read one line of results to split into columns of data


• PFCLSplit() �" can be used to split a line of data based on a passed in separator


• PFCLPrint() �" print a line of data to our scanner output

Second we also pass in tables of data from the C engine as Lua is executed so that data items processed in C can be accessed via the Lua scripts. In our code analyser in PFCLCode for instance we can access the lexer and parser output as a table of data and in all of our tools we pass flags to Lua that can then be used in Lua. One example is a debug flag so that if “trace” is enabled in our engine then a debug flag is passed to Lua so that a “trace” flag can also be enabled or not in Lua and then debug statements will write to the output to aid debugging of Lua scripts.

The third thing that we do is provide some Lua libraries written in Lua that can be included in scripts run by the tool (in user space �" i.e. we do not need to recompile the C code) to help with common tasks such as checking if a file exists or reading and writing files.

The goal with PL/SQL applications including APEX is to do the same thing as Lua with C code applications. We have created a simple language interpreter written in PL/SQL. A few articles on writing a simple language interpreter in PL/SQL are on my website

A simple script showing a nested loop is here:

declare
    lv_prog varchar2(32767):=q'[ 
                                LET m=2
                                LET x=1
                                PRINT "Start of tests"
                                PRINT "=================="
                                LOOP
                                    LET y=1
                                    LOOP
                                        PRINT "x, y is [";x;",";y;"]"  
                                        IF y>m THEN
                                            EXIT
                                        FI
                                        LET y=y+1
                                    POOL
                                    IF x>m THEN
                                        EXIT
                                    FI
                                    LET x=x+1
                                POOL
                                PRINT "=================="
                                PRINT "End of Tests"
                                END
                            ]'; 
begin
    --
    pfclscript.init(true,1);
    pfclscript.run(lv_prog);
    --
end;
/

And the output is here:

SQL> @interp
Start of tests
==================
x, y is [1,1]
x, y is [1,2]
x, y is [1,3]
x, y is [2,1]
x, y is [2,2]
x, y is [2,3]
x, y is [3,1]
x, y is [3,2]
x, y is [3,3]
==================
End of Tests

PFCLScript Execution Time (Seconds) : +000000 00:00:11.041386000
SQL>

To add this as a language to any PL/SQL application including Apex we need to do:

• ship the interpreter package header and body with the PL/SQL application in a separate schema


• Lock the interpreter schema, ensure only permission to execute is given to the PL/SQL application. Use other features such as “accessible by” clause to ensure that the interpreter cannot be started outside of its designed use


• Add the init() and run() calls to the interpreter in your PL/SQL application where you choose to use them


• Store and allow scripts to be loaded as data in your PL/SQL application and arrange that they can be chosen and executed as needed


• Create a simple API into your application that can be used from the interpreter as package calls to expose only the features/functionality as needed. These package procedure calls can access data/functions in the PL/SQL application and this API should be in a separate schema. Grant access on the PL/SQL application to this schema


• Create the “register calls” in your PL/SQL application after init() and before run() to register the interpreter function names that map to the API you created above

A simple call sequence inside your application would be:

…
begin
    --
    pfclscript.init(true,1);
    pfclscript.register(‘PFCLFUNCA’,’API.FUNCA’);
    pfclscript.register(‘PFCLFUNCB’,’API.FUNCB’);
    pfclscript.run(lv_prog);
    --
end;
…

The register() function in effect creates a new ID internally in the interpreter for the “PFCLFUNA” and “PFCLFUNCB” and these are mapped to actual PL/SQL functions in the interface PL/SQL API created. This now means that a script can be created that executes in our script engine that uses these functions to access features or data in the actual PL/SQL or Apex application.

To the script, these just look like function calls. The designer of the PL/SQL or Apex application decides where to run the script and what features to expose.
Data can be passed in by the script writer in the form or parameters to the script function and these parameters are then passed to the registered PL/SQL API functions.
Libraries of script code can be added easily, simply by prepending them to the script to run. We do not currently have “include”, “require” type keywords but they can be simulated by pre-pending the library code.

The engine can also pass data to the script engine in a similar way to Lua but in essence in a simpler day. We can do this in two ways. The first by setting up internal data in the API functions or the second by passing data as script variables again pre-pended to the script to run

#oracleace #sym_42 #plsql #apex #scripting #compiler #interpreter #oracle #security