Pete Finnigan's Oracle Security Weblog

Our product PFCLObfuscate allows dynamic obfuscation of PL/SQL. The original use of this in the product was to add licensing automatically to PL/SQL. This is similar to products that protect binaries such as C programs or DLLs.

It is common to buy software and need a key to then activate that software. We added dynamic obfuscation in this product so that we could easily add licensing to any PL/SQL. BUT, the dynamic obfuscation can also be used for other things as well to help protect your PL/SQL. This is what we intended when we added this feature; hence we can

• obfuscate PL/SQL code that normally would not be possible to obfuscate; for example if your code calls DBMS_OUTPUT.PUT_LINE() then you cannot obfuscate it to GGFDAD.HASDFAD() for instance as the database doesn't know what that is and we cannot change the name of DBMS_OUTPUT.PUT_LINE TO this as no other code that uses this SYS package would work.


• Standard string obfuscation is simple in PFCLObfuscate but it is better to do much stronger string obfuscations and we can do this via the dynamic obfuscation


• Locking the PL/SQL to a database. The standard license protection can limit the use of PL/SQL to a date/time limit. This is the simplest protection method for licensing. We can also lock PL/SQL to a database so that you can deploy the PL/SQL code and it is initially restricted and your customer runs an activation piece of code that generates hex strings. You then use this to create a license package and deploy that to your customer. Then the PL/SQL will be unlocked and work at the customer site. If someone takes the PL/SQL code and even the license key as well the protected PL/SQL won't work in the wrong database


• Target the beginning and end of code blocks or target the start or end of a declare block. Any PL/SQL can then be injected into your PL/SQL code


• Targeted comments; you can add a special PL/SQL comment to your PL/SQL code and then use dynamic obfuscation to inject any PL/SQL at obfuscation time to the exact place it's needed. This is a great feature as the developers do not write this code; the security team or others can inject the code at deploy time

Our dynamic obfuscation is very powerful and at a high level works with hook points that control when Lua scripts are executed by PFCLObfuscate. You control these Lua scripts and you can write any Lua you need and generate any PL/SQL that you need. This is incredibly flexible

You can combine together any of these dynamic obfuscation features for instance you can inject the standard license using the special PL/SQL comment feature and this is what I want to demo here.

NOTE: For this example I assume that the reserved.txt has been populated already. I am also using the command line to keep the contents much smaller so I don't need to include a lot of screen shots here.

First here is the sample PL/SQL I want to license:

C:\_aa\PD>type sam2.sql
create or replace procedure TEST_PROC( PV_NUM in NUMBER,
 PV_VAR in VARCHAR2, PV_VAR3 in out INTEGER) IS
 L_NUM NUMBER:=3;
 L_VAR NUMBER;
 J NUMBER:=1;
procedure NESTED( PV_LEN in out NUMBER) is
 X NUMBER;
begin
 X:= PV_LEN * 5;
end;
begin
case L_NUM
 when 1 then
 L_VAR:=3;
 DBMS_OUTPUT.PUT_LINE('This is a header');
 DBMS_OUTPUT.PUT_LINE('The number is ' ||  L_VAR);
 DBMS_OUTPUT.PUT_LINE('The case var is ' ||  L_NUM);
 when 2 then
 L_VAR:=4;
 DBMS_OUTPUT.PUT_LINE('This is a header');
 DBMS_OUTPUT.PUT_LINE('The number is ' ||  L_VAR);
 DBMS_OUTPUT.PUT_LINE('The case var is ' ||  L_NUM);
 when 3 then
 L_VAR:=6;
 DBMS_OUTPUT.PUT_LINE('This is a header');
 DBMS_OUTPUT.PUT_LINE('The number is ' ||  L_VAR);
 DBMS_OUTPUT.PUT_LINE('The case var is ' ||  L_NUM);
else
 DBMS_OUTPUT.PUT_LINE('wrong choice');
end case;
if ( ( J = 1) and ( J = 3)) then
 DBMS_OUTPUT. PUT_LINE('here is IF');
elsif ( ( J = 2) or ( J != 3)) then
 DBMS_OUTPUT.PUT_LINE('The elsif clause');
else
 DBMS_OUTPUT.PUT_LINE('else clause');
end if;
 J:=4;
 NESTED( J);
 DBMS_OUTPUT.PUT_LINE('nested=:' ||  J);
for J in reverse 1.. PV_NUM loop
if MOD( J,2) = 0 then
 DBMS_OUTPUT.PUT_LINE('for loop with reverse');
end if;
end loop;
end;
/
C:\_aa\PD>

Install this code to the database:

C:\_aa\PD>sqlplus orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 29 19:49:37 2022
Version 19.12.0.0.0

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

Last Successful login time: Mon Mar 21 2022 16:07:58 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

Test the code to show that it works:

SQL> @sam2.sql

Procedure created.

SQL>

SQL> set serveroutput on
SQL> declare
  2  lv_num number;
  3  begin
  4  test_proc(1,'hello',lv_num);
  5  end;
  6  /
This is a header
The number is 6
The case var is 3
The elsif clause
nested=:4

PL/SQL procedure successfully completed.

SQL>

Now add the special comment to the PL/SQL; I show only the code to where the special comment (--++TESTCOMMENT) is added:

C:\_aa\PD>type sam2.sql
create or replace procedure TEST_PROC( PV_NUM in NUMBER,
 PV_VAR in VARCHAR2, PV_VAR3 in out INTEGER) IS
 L_NUM NUMBER:=3;
 L_VAR NUMBER;
 J NUMBER:=1;
procedure NESTED( PV_LEN in out NUMBER) is
 X NUMBER;
begin
 X:= PV_LEN * 5;
end;
begin
--++TESTCOMMENT
case L_NUM
 when 1 then
 L_VAR:=3;
...

Now edit the functionfile.txt to add the comment processing rules:

C:\_aa\PD>type functionfile.txt
6|COMMENT|--++TESTCOMMENT|dbms_lic_begin_new.lua|dbms_lic_begin_fwd.lua|dbms_lic_begin_func.lua|1|1|0
C:\_aa\PD>

I only want the comment line in my dynamic obfuscation setup and also I use the existing Lua from the license code. I could write my own Lua if I wished or edit this Lua. Any valid Lua syntax can be used BUT the three Lua files do this:

1 - add the forward declaration of a function to do the work
2 - Add an actual function declaration to do the work
3 - add the "call". This calls the function that is declared above.

The PL/SQL added from the Lua must be valid PL/SQL; it will also be obfuscated as its injected into the parse stream

Edit the config file ob.conf to turn on dynamic obfuscation. I show just the dynamic settings here, nothing else changed

C:\_aa\PD>type ob.conf

...
# The function and string hide file. This file specifies a package to "hide" or
# strings to hide and then specifies the code to add new PL/SQL processing to
# deal with this and also PL/SQL substitute for the actual located string. This
# is detailed in the help
#
functionfile=functionfile.txt
#
# This parameter controls whether the intermediate files generated via any lua
# scripts are saved or not. This is useful for debugging.
#
#keepintermediate=yes
#
# This parameter controls whether the source code file containing the injected
# PL/SQL code is kept for debugging. The Injected code file contains all inserted
# headers, functions and the calls where the replace packages or strings. These
# are encased with {{n:n:n}} variables for removal.
#
#keepinsert=yes
#
# This parameter is  the file extension used for the intermediate files. This
# can be changed.
#
intermediatefile=.imm
#
...

Add the procedure name TEST_PROC to the omit.txt so that the public name remains the same:

C:\_aa\PD>type omit.txt
TEST_PROC
C:\_aa\PD>

Update the license.txt to add the license rules:

C:\_aa\PD>type license.txt
expire_days=30
expire_date=27-MAR-2022
start_date=01-MAR-2022
license_type=trial
C:\_aa\PD>

Now obfuscate the code:

C:\_aa\PD>obs -v -c ob.conf -i sam2.sql -o sam2.opf

PFCLObfuscate: Release 2.1.46.1031 - Production on Tue Mar 29 21:38:33 2022

Copyright (c) 2017 PeteFinnigan.com Limited. All rights reserved.

[2022 Mar 29 20:38:33] obs: Starting PFCLObfuscate...
[2022 Mar 29 20:38:33] obs: Pre-Load Keywords from [key.txt]
[2022 Mar 29 20:38:33] obs: Pre-Load Omit words from [omit.txt]
[2022 Mar 29 20:38:33] obs: Pre-Load StringOmit words from [string.txt]
[2022 Mar 29 20:38:33] obs: Pre-Load Reserved words from [reserved.txt]
[2022 Mar 29 20:38:33] obs: Pre-Load force words from [force.txt]
[2022 Mar 29 20:38:33] obs: Pre-Load function file list from [functionfile.txt]
[2022 Mar 29 20:38:33] obs: Pre-Load map file list from [map.txt]
[2022 Mar 29 20:38:33] obs: Initialise the string file list...
[2022 Mar 29 20:38:33] obs: Version 2.0 Initialisation...
[2022 Mar 29 20:38:33] obs: Initialise the file list...
[2022 Mar 29 20:38:33] obs: Initialise the Depth Stack...
[2022 Mar 29 20:38:33] obs: Initialise the FWD Function list...
[2022 Mar 29 20:38:33] obs: Initialise the FUNC function list...
[2022 Mar 29 20:38:33] obs: Initialise the NEW function list...
[2022 Mar 29 20:38:33] obs: Running PFCLObfuscate PL/SQL Obfuscator
[2022 Mar 29 20:38:33] obs: Obfuscating PL/SQL Input File [ sam2.sql ]
[2022 Mar 29 20:38:33] obs: Save the transposed variables
[2022 Mar 29 20:38:33] obs: Process intermediate file...
[2022 Mar 29 20:38:33] obs: Closing Down PFCLObfuscate

C:\_aa\PD>

Put the PL/SQL obfuscated code in the database:

C:\_aa\PD>sqlplus orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 29 21:48:41 2022

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> @sam2.opf

Procedure created.

SQL>

Test the code; it should be blocked by the license as its expired; the license is set from 01-03-2022 to 27-03-2022 and its the 29th today

SQL> set serveroutput on
SQL> declare
  2  lv_num number;
  3  begin
  4  test_proc(1,'hello',lv_num);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-20095: invalid license(3)
ORA-06512: at line 1
ORA-06512: at "ORABLOG.TEST_PROC", line 1636713768
ORA-06512: at "ORABLOG.TEST_PROC", line 44
ORA-06512: at line 4


SQL>

change the license to end on the 30th March:

C:\_aa\PD>type license.txt
expire_days=30
expire_date=30-MAR-2022
start_date=01-MAR-2022
license_type=trial
C:\_aa\PD>

obfuscate again

C:\_aa\PD>obs -v -c ob.conf -i sam2.sql -o sam2.opf

PFCLObfuscate: Release 2.1.46.1031 - Production on Tue Mar 29 21:53:45 2022

Copyright (c) 2017 PeteFinnigan.com Limited. All rights reserved.

[2022 Mar 29 20:53:45] obs: Starting PFCLObfuscate...
[2022 Mar 29 20:53:45] obs: Pre-Load Keywords from [key.txt]
[2022 Mar 29 20:53:45] obs: Pre-Load Omit words from [omit.txt]
[2022 Mar 29 20:53:45] obs: Pre-Load StringOmit words from [string.txt]
[2022 Mar 29 20:53:45] obs: Pre-Load Reserved words from [reserved.txt]
[2022 Mar 29 20:53:45] obs: Pre-Load force words from [force.txt]
[2022 Mar 29 20:53:45] obs: Pre-Load function file list from [functionfile.txt]
[2022 Mar 29 20:53:45] obs: Pre-Load map file list from [map.txt]
[2022 Mar 29 20:53:45] obs: Initialise the string file list...
[2022 Mar 29 20:53:45] obs: Version 2.0 Initialisation...
[2022 Mar 29 20:53:45] obs: Initialise the file list...
[2022 Mar 29 20:53:45] obs: Initialise the Depth Stack...
[2022 Mar 29 20:53:45] obs: Initialise the FWD Function list...
[2022 Mar 29 20:53:45] obs: Initialise the FUNC function list...
[2022 Mar 29 20:53:45] obs: Initialise the NEW function list...
[2022 Mar 29 20:53:45] obs: Running PFCLObfuscate PL/SQL Obfuscator
[2022 Mar 29 20:53:45] obs: Obfuscating PL/SQL Input File [ sam2.sql ]
[2022 Mar 29 20:53:45] obs: Save the transposed variables
[2022 Mar 29 20:53:45] obs: Process intermediate file...
[2022 Mar 29 20:53:45] obs: Closing Down PFCLObfuscate

C:\_aa\PD>

Put the obfuscatedPL/SQL back in the database and test:

SQL> @sam2.opf

Procedure created.

SQL>
SQL> declare
2 lv_num number;
3 begin
4 test_proc(1,'hello',lv_num);
5 end;
6 /
This is a header
The number is 6
The case var is 3
The elsif clause
nested=:4

PL/SQL procedure successfully completed.

SQL>

Now it works of course as the license is valid.

If anyone would like more details or a demo please email me on pete at petefinnigan dot com

More than 20 years ago I was working away from home and was in a loud restaurant / bar in London and chatting to colleagues there and we were all talking about ways to make money and ideas. I proposed an idea where a person can create code blocks that implement core functionality where you would spend a limited amount of time then building the actual application.

Yes, I am aware that is the idea with tools like VB.net and Apex and Delphi and others but these development environments still require you to put together all of the core elements for the application and glue it all together and then write the actual functionality that you need.

That evening more than 20 years ago I suggested One Month Projects where such a toolkit of core features would allow new applications and programs that would be saleable could be made and marketed within one month each; that would include writing the application, installer, documentation, marketing, etc so it could be sold. This meant in terms of that discussion that you could create a new application / program to sell every month and keep adding to the portfolio of software to sell and one (or ideally more than one) of them would for sure be successful and the person doing it would make money. The discussion that evening in London was ways to make money

I never forgot that discussion and when I created PFCLScan which was our first product I wanted to use these same code/functionality block ideas to make the development and construction of other applications faster and easier. So PFCLScan was more complex and comprehensive that it needed to be to simply run security checks against an Oracle database.

First I created separation from the GUI and the scanning engine itself. I have mentioned some of these ideas here before. The scanning engine runs projects; these projects are XML files that link to further XML files that are policies that in turn contain checks; the actual things that do the work. These checks can be SQL, PL/SQL, Shell, DOS, Lua, ftp, sftp, ssh and many more. In fact a check can run anything because it can run a local DOS command. We also created many tools that do things singly and these can be run from a check. The checks themselves then are dynamic; a check can read data from a previous check in another policy that has already run and even from a previously executed project. These can be static data or loop based data; i.e. run a check in one language based on the list of results from a previous check or project.

The scanning engine was designed to literally do anything that can run as a command.

The reporting tool uses its own report language and any input text file can be a report template. This means that we can generate any text file based on static data in the scanner, the rules themselves or from results of a scan.

When we chain these two things together it means we can use projects and reports as tools within PFCLScan itself. We do this for plugins and also to implement features in the product. It means that PFCLScan is infinitely extendable.

Next came the One Month Projects that took more than one moth to create but built on the core PFCLScan features and blocks in a similar way to what I envisaged more than 20 years ago. The scanner also includes a core framework, logging and trace, reporting screens, connections, and more. When we created applications I wanted them to use PFCLScan and not re-implement the same features over and over. So a new application uses our application frame that already brings a lot of features we need. We then implement all of the functionality as plugins (projects and policies) and do minimal GUI work simply to call plugins and then manage the data and display it. Not One Month projects but very fast development. We have 5 software applications now and two more in development and many more in the pipeline. Each will be added into the framework.

Each application can be licensed standalone or with others or with PFCLScan itself

We also manage the build of the applications and management of licenses and customers also using PFCLScan. We created a custom plugin that can build and email and ftp the software for each customer and even build and send updates to each live customer when one is ready.

The scope to extend and build more applications can be done on a number of levels:

• Create more core applications into the application framework


• Create project based applications where we create a new project based application that runs from PFCLScan; we can do this or customer/partners could do this also


• Create non database type applications for instance PFCLScan is a database security scanner BUT it could just as easily be something else completely such as website cookie scanner


• Integrate our software into other products as a commercial venture; all our products have the functionality built as plugins and these can be run from the command line and therefore easily integrated into other companies products


• Integrate our software into other tools personally as an aid to do something; all our products have the functionality built as plugins and these can be run from the command line and therefore easily run from other tools that you have for instance the editor TextPad can run commands so our scanner can be run as a command and provided it produces a text file (it can produce text, HTML, JSON and many more) then it will load into TextPad; that's just one example. Our tools can also run from the command line so could be integrated into other custom internal software such as running PFCLCode as part of a PL/SQL software build process


• Many More....

Whilst we didn't create exactly One Month Projects you can hopefully see how I designed PFCLScan to be a toolkit that can easily be extended, integrated and become other products or be the basis for other products. I always remembered this discussion and wanted to be able to do this and make creation of new products fast and easy and to make products extendable and powerful.

If you would like to see a demo of our software then please send me a DM on any of our social channels or send me an email to pete at petefinnigan dot com

Over the last few years I have personally been asked many times to come and work full time in large companies to head up or direct their Oracle security efforts or more general database security efforts. Others ask us to come and work on a contract basis on specific work packages. I have always accepted work packages where we have the capacity and where it is Oracle security specific but I have always turned down job offers in large companies. I run PeteFinnigan.com Limited and we have many customers and we sell Oracle Security training and 5 software products that we have developed as well as providing consultancy and services to customers.

So, when someone/company really wanted me to be part of their team to consult or guide or design at a high level specifically in the area of Oracle security I needed to say yes in some way to some of them. So over the last few years I have done that. The way this works is that a customer can have me as part of their team on a call off basis. I charge a fixed rate per hour and the customer can call on me to provide consultancy, represent them at meetings, be in internal meetings and more. Some of the areas we have covered over the last few years are SSL for the database, audit trail designs, Database Vault design and implementation, kerberos authentication to the database, encryption, TDE, Oracle key vault and more. I am able to provide expert consultancy on a short term call off basis

This works by agreeing in advance I am not a full time 8 hours a day employee but I am part of the customers team on a full time basis but not working full time. We charge a fixed rate per hour and the customer agree small term pieces of work, or calls to be part of or... I charge down to 5 minutes so if you need me for 15 minutes that is fine, I charge 1/4 of the hourly rate for that 15 minutes.

This works when the amount of time for me is not excessive but the customer has access to me all of the time to ask questions or be involved. This has worked as a sort of secret service in that I don't advertise it up to now. I wanted to be sure this works well for more than one customer at a time and it does and I can still work and run the business at the same time.

The customer gets me in his team and is able to request time and advice at any time but I also can service multiple clients and other work.

If you would like to have me as part of your team as an expert in securing data in Oracle just ask. Send me an email at pete at petefinnigan dot com or send me a DM on social media. We have limited space but ask anyway and we can discuss the details.

I was having a discussion a couple of weeks ago with a friend and he said that in the company he is working at the Oracle database security standard / guide that they are working to told them that they needed to revoke PUBLIC from users in the database. The guide they are using (I didn't get to read it so I only have what was said to me) does cover the normal revokes on some packages from PUBLIC as well.

I have talked about PUBLIC here in some details in the past but not this angle. The first post is "Can I connect to the database as the user PUBLIC?" and the second post was about XS$NULL but also involved PUBLIC a bit. That post is "XS$NULL - Can we login to it and does it really have no privileges?"

So, the first question; can we prove that PUBLIC is not directly granted and is in fact available to all users of the database - i.e. the privileges granted to PUBLIC are available without a grant specifically of PUBLIC to a user.

Lets connect to a 21c database:

C:\_aa\PD>sqlplus system/oracle1@//192.168.56.33:1539/xepdb1

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 2 10:55:03 2022

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


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

SQL>

Create a sample user that has a sample PL/SQL procedure so that we can grant EXECUTE on it to public and then prove we can use it from another user - which we will also create. Fisrt create the schema:

SQL> create user sch identified by sch;

User created.

SQL> grant create session, create procedure, unlimited tablespace to sch;

Grant succeeded.

SQL>

Connect to this user and create a dummy procedure, execute it as SCH and then grant execute to PUBLIC:

SQL> connect sch/sch@//192.168.56.33:1539/xepdb1
Connected.
SQL> create or replace procedure test as
  2  begin
  3  dbms_output.put_line('hello');
  4  end;
  5  /

Procedure created.

SQL> set serveroutput on
SQL> exec test
hello

PL/SQL procedure successfully completed.

SQL>

Now grant EXECUTE to PUBLIC:

SQL> grant execute on test to public;

Grant succeeded.

SQL>

Connect to SYSTEM again and create a user that has just CREATE SESSION to connect and then see if it can execute SCH.TEST

SQL> connect system/oracle1@//192.168.56.33:1539/xepdb1
Connected.
SQL> create user use identified by use;

User created.

SQL> grant create session to use;

Grant succeeded.

SQL>

Connect to this user and test the PL/SQL procedure:

SQL> connect use/use@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec sch.test;
hello

PL/SQL procedure successfully completed.

SQL>

It does work and silently we can execute the PL/SQL code because its granted to PUBLIC. Lets now do what my friends suggests and revoke PUBLIC from the user USE and see what happens:

SQL> connect system/oracle1@//192.168.56.33:1539/xepdb1
Connected.
SQL> revoke public from use;
revoke public from use
*
ERROR at line 1:
ORA-01951: ROLE 'PUBLIC' not granted to 'USE'


SQL>

OK, the role PUBLIC is not granted to the user USE. This is true as we didn't grant it. As we didnt actually revoke it then nothing has changed. Lets just check the PUBLIC execute still works for completeness:

SQL> connect use/use@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec sch.test;
hello

PL/SQL procedure successfully completed.

SQL>

Quite obviously it works because the revoke failed. What if we grant PUBLIC to the user USE:

SQL> connect system/oracle1@//192.168.56.33:1539/xepdb1
Connected.
SQL> grant public to use;

Grant succeeded.

SQL>

That does work; is it visible in the meta data:

SQL> select granted_role from dba_role_privs where grantee='USE';

GRANTED_ROLE
--------------------------------------------------------------------------------
PUBLIC

SQL>

Yes, it is granted and it is visible in DBA_ROLE_PRIVS. What if we now revoke it and then test the execute again:

SQL> sho user
USER is "SYSTEM"
SQL> revoke public from use;

Revoke succeeded.

SQL> select granted_role from dba_role_privs where grantee='USE';

no rows selected

SQL> connect use/use@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec sch.test;
hello

PL/SQL procedure successfully completed.

SQL>

So, obviously the test PL/SQL procedure still works. You can GRANT PUBLIC to a user and you can REVOKE PUBLIC from a user BUT PUBLIC is not removed from a user as its implicitly there as it is in fact not a role and is a USER GROUP; the only one in the Oracle database.

In one sense this is a pity as it would actually be great if we could actually revoke PUBLIC from a user and therefore remove all PUBLIC grants from that user. But we cannot do this. So the assumption of my friends Oracle security document is probably in part correct but not the meaning he thought to start with. Yes, we should revoke some rights from PUBLIC such as execute on SYS.UTL_FILE etc and yes we can revoke PUBLIC from users IF some has granted it to those users BUT it doesn't actually do anything as PUBLIC is still available to all users