Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Granting ALL on Database Objects

I was asked by a friend a few days ago a few questions related to the granting of ALL on a database object such as a table or a PL/SQL package. For example - GRANT ALL ON OWNER.TABLE TO DAVE. Let me list the questions and then we will explore the answers:

  1. Does Oracle document the exact individual grants made to an object (i.e. a TABLE / PROCEDURE/... when GRANT ALL.... is executed

  2. Can we tell if a grant has been made via a GRANT ALL.... or an individual grant such as GRANT SELECT ON OWNER.TABLE TO DAVE;

  3. How can we find all objects in the database that have been granted ALL


As far as I can tell to answer question (1) - please correct me if I am wrong - Oracle does not document the exact privileges granted when you GRANT ALL on an object such as a table. We can resolve this for all object types by creating one of each type and issuing a GRANT ALL... and then using this information to test each object to see what was granted and whether we can tell if GRANT ALL... was granted.

For example lets connect to my 19c database and create a table and then issue GRANT ALL on it:

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> create table test (test01 number);

Table created.

SQL> grant all on test to emil;

Grant succeeded.

SQL>

Now we can check the actual grants that have been made:

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL>
SQL> set lines 220
SQL> col grantee for a30
SQL> col grantor for a30
SQL> col privilege for a40
SQL> l
1* select grantee, grantor, privilege from dba_tab_privs where owner='ORABLOG' and table_name='TEST'
SQL> /

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG ALTER
EMIL ORABLOG DELETE
EMIL ORABLOG INDEX
EMIL ORABLOG INSERT
EMIL ORABLOG SELECT
EMIL ORABLOG UPDATE
EMIL ORABLOG REFERENCES
EMIL ORABLOG READ
EMIL ORABLOG ON COMMIT REFRESH
EMIL ORABLOG QUERY REWRITE
EMIL ORABLOG DEBUG

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG FLASHBACK

12 rows selected.

SQL>

So, in 19c 12 privileges are granted to a user when GRANT ALL is issued. This will vary per object and also version of Oracle. What if we revoke all and then check whats granted:

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> revoke all on test from emil;

Revoke succeeded.

SQL>

Lets check the grants:

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select grantee, grantor, privilege from dba_tab_privs where owner='ORABLOG' and table_name='TEST';

no rows selected

SQL>

This is simple for one object type (table) and for grant and revoke. What if we make it more complex. Lets first grant ALTER on the table to EMIL and check permissions and then GRANT ALL and then REVOKE ALL. What happens:

Make the the single grant

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> sho user
USER is "ORABLOG"
SQL> grant alter on test to emil;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select grantee, grantor, privilege from dba_tab_privs where owner='ORABLOG' and table_name='TEST';

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG ALTER

SQL>

Now GRANT ALL on the same table and check the permissions:

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> sho user
USER is "ORABLOG"
SQL> grant all on test to emil;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select grantee, grantor, privilege from dba_tab_privs where owner='ORABLOG' and table_name='TEST';

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG ALTER
EMIL ORABLOG DELETE
EMIL ORABLOG INDEX
EMIL ORABLOG INSERT
EMIL ORABLOG SELECT
EMIL ORABLOG UPDATE
EMIL ORABLOG REFERENCES
EMIL ORABLOG READ
EMIL ORABLOG ON COMMIT REFRESH
EMIL ORABLOG QUERY REWRITE
EMIL ORABLOG DEBUG

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG FLASHBACK

12 rows selected.

SQL>

The same 12 grants now exist to EMIL on the table ORABLOG.TEST. This GRANT ALL overwrites the single grant of ALTER on the same table - It is lost and this is a problem for your management of grants on objects and indeed the same applies for system grants.

This is a classic problem I have seen many times that occurs in a number of places in the Oracle database. A slightly different example on the same area would be that an application or DBA grants one right on an object and then another application or DBA grants two more rights where one of the two new grants is the same as the first. At this point the permissions work BUT if the second DBA then revokes the two grants he/her made then the first grant is also removed and again the security model is broken.

Another example where the same issue occurs is audit trail. If one application or DBA turns on two audit settings and then another application or DBA turns on two more and one is the same as the first. If one of the applications is removed or audit disabled the first is affected.

How can we fix this grants problem?

One idea; obviously is to add the grants for DBA 1 to a role - say DBA_ROLE_1 and add the grants for DBA2 to another role, say DBA_ROLE_2. In this way we revoke the role we need to revoke and remove the grants BUT we also need to then grant back the other role where there is a privilege cross over to put the security model back correctly

Each object type has a different grants possible so there is no consistent way to test all objects on the database to see if ALL has been granted other than creating one object and then listing the grants and then checking all objects of the same type to see if they match.

If we want to try and find all objects where GRANT ALL has been made then we also have the issue that we can GRANT ALL ON ... TO ... but we can also make 12 individual grants on a table individually and we would not be able to tell the difference between these individual grants and the GRANT ALL.

I have written a script to look for potential GRANT ALL (bear in mind someone can grant each right individually BUT the result is the same as GRANT ALL) and to keep this post shorter I will present that script in part 2 of this blog post and demo looking for users / schemas that have been granted ALL.

Do You Worry Your Companies data is Being Stolen?

The number of data breaches is seemingly growing daily and a lot of companies worry that they could be the next statistic of misery and embarrassment.

Do you lose sleep worrying that your company could be breached and its data stolen or leaked?

Do you worry that you don't even know where to start to secure the data held in your Oracle database?

I get asked a question quite often; is it easier for a security person to learn Oracle or easier for an Oracle person to learn security. Oracle is big and complex and the applications designed and deployed into an Oracle database make it more complex. There no easy simple switches to turn on and secure an Oracle database such as

SQL> alter system set make_oracle_secure=true scope=both;

It would be nice if there was but its not going to happen. Oracle the database is a generic engine that provides you the tools to create and manage your data your way. This means, not surprisingly that the design of your application (tables, code etc) also MUST include data security design; it is obvious really.

So is it easier to teach a security person Oracle or an Oracle person security? probably it is easier to teach an Oracle person security because they know already the Oracle database BUT security people can also learn Oracle.

The DBA teams manage the data stores often in Oracle databases and they and the security teams and IT managers and even developers are responsible for making sure that the business data stays safe. These IT staff must know what to do and to choose the logical, simplest and most cost effective ways to secure data in an Oracle database.

Do you want to add value to your organisation? protect its data? then the best option is to attend our Oracle security training classes. These are all taught live on-line over webex by Pete Finnigan who has more than 20 years experience helping people secure data in Oracle. We provide the tuitions, lots of examples and demos and experience from real life. Also we include hundreds of free tools that can be used in real Oracle security work on your sites and databases. These are not toys and are useful tools that we use in our work. These free tools and scripts are worth joining the classes for. You also get plenty of chances to ask questions and discuss the content and your own worries.

We have many training courses - Our Oracle Security Classes - ranging from how to conduct a security audit of an Oracle database, secure coding in PL/SQL, designing audit trails, GDPR for Oracle, Oracle forensics and hardening Oracle.

We have just posted on line live class dates from now until March 2023 - Our Live Oracle Security Training Schedule - more dates will be added.

Solve your worries and get the knowledge to be confident to secure your own data in an Oracle database. Contact me on social media or via email - Contact Pete Finnigan - to book your place now

Searchlight a Product to Make Finding Data Easy

Do you need to comply with GDPR and protect personal data but have no idea where to start to locate that data? Searchlight; is a tool to find your data.

PeteFinnigan.com Limited have become the reseller for a great product called Searchlight. This product complements our software products and also our services. We focus on the security of data held in the database and provide consulting, products and services to help customers in all aspects of this. We decided to partner with Custom Computer Programs Limited to allow us to widen the scope of what we do. Often companies extract data from an Oracle database or indeed any other database and this ends up sitting on PCs and servers and more.

Searchlight OOTB (Out Of The Box) has some extremely simple (to use) but powerful searches so that companies can truly know where personal data or card data or health data or... is stored whether in the database or anywhere else.

We felt that Searchlight is a great addition to our offerings

Searchlight is simple to use BUT also very powerful and fully customisable. It can search a single PC hard drive or network shares or co-ordinate agents installed on each PC to be searched. You can use the simple easy built in searches or define your own.

To learn more or see a demo then please contact us and we will be happy to set up a personal demo for you.

Oracle Security - Hidden Grant When Create a Role and Revoke in a CDB

I am keen to reduce grants made in any customers database. One area we can focus on is this curios state of affairs that the creator of a role in the Oracle database is also granted that role as part of the create. We can see this in my 19c database in a PDB. First lets create a role:

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 6 09:46:13 2022

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

SQL> @cs
Connected.
USER is "SYS"

'CONNECTEDAS'||USER
--------------------------------------------------------------------------------
CONNECTED AS SYS

SQL> create role fred;

Role created.


This is a CREATE privilege and use BUT Oracle also makes a silent GRANT. We can see this by checking who has been granted the ROLE FRED in this case:

SQL> @print 'select * from dba_role_privs where granted_role=''''FRED'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_role_privs where granted_role=''FRED''','''','''''');
Executing Query [select * from dba_role_privs where granted_role='FRED']
GRANTEE : SYS
GRANTED_ROLE : FRED
ADMIN_OPTION : YES
DELEGATE_OPTION : NO
DEFAULT_ROLE : YES
COMMON : NO
INHERITED : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Because I created the role as SYS, then SYS has also been granted the role FRED BUT not explicit GRANT was made. This needs to be managed in each database and unless the creator of the role actually needs to have the role then these grants should be revoked as they are unnecessary rights.

We should also think carefully about who grants roles. If we decide that the application schema needs to grant roles then the schema needs the ability to create roles. This means that after we create all the needed application roles we should remove this grant also from the schema. It maybe makes more sense to have the application create its roles as then all of the application including roles is contained within the application.

As you can see a database often can end up with privileges and security objects such as roles that are not needed at run time. We must always strive for least rights in all parts of the database.

There is a second slight glitch in the creation of roles if we do this in a CDB. Lets create a role in the CDB:

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 6 09:54:37 2022

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

SQL> @cs
Connected.
USER is "SYS"

'CONNECTEDAS'||USER
--------------------------------------------------------------------------------
CONNECTED AS SYS

SQL>

This is still a 19c database. Lets create the role:

SQL> create role c##fred;

Role created.

SQL>

Now we can check to see if SYS has also been granted C##FRED in the CDB:

SQL> @print 'select * from dba_role_privs where granted_role=''''C##FRED'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_role_privs where granted_role=''C##FRED''','''','''''');
Executing Query [select * from dba_role_privs where granted_role='C##FRED']
GRANTEE : SYS
GRANTED_ROLE : C##FRED
ADMIN_OPTION : YES
DELEGATE_OPTION : NO
DEFAULT_ROLE : YES
COMMON : YES
INHERITED : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, as with the PDB and for the same reasons I would like to revoke C##FRED from SYS as it is not a needed grant:

SQL> revoke c##fred from sys;
revoke c##fred from sys
*
ERROR at line 1:
ORA-01951: ROLE 'C##FRED' not granted to 'SYS'


SQL>

When we created the role we didn't specify which pluggable databases we would like the role created in so Oracle uses a default of "container=all" behind the scenes this is why the revoke needs "container=all":

SQL> revoke c##fred from sys container=all;

Revoke succeeded.

SQL>

The role still exists in the PDB of course as well as the CDB:

SQL> alter session set container=orclpdb;

Session altered.

SQL> select role from dba_roles where role like '%FRED%';

ROLE
--------------------------------------------------------------------------------
FRED
C##FRED

SQL>
Don't create roles in the CDB as by default they are created in each PDB and also granted to the creator in each PDB.:

SQL> @cs
Connected.
USER is "SYS"

'CONNECTEDAS'||USER
--------------------------------------------------------------------------------
CONNECTED AS SYS

SQL> create role c##test;

Role created.

SQL> alter session set container=orclpdb;

Session altered.

SQL> @print 'select * from dba_role_privs where granted_role=''''C##FRED'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_role_privs where granted_role=''C##FRED''','''','''''');

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> @print 'select * from dba_role_privs where granted_role=''''C##TEST'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_role_privs where granted_role=''C##TEST''','''','''''');
Executing Query [select * from dba_role_privs where granted_role='C##TEST']
GRANTEE : SYS
GRANTED_ROLE : C##TEST
ADMIN_OPTION : YES
DELEGATE_OPTION : NO
DEFAULT_ROLE : YES
COMMON : YES
INHERITED : YES
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Do not create roles in the CDB for applications use, create then in each PDB and also ensure that you "manage" who creates them and revoke the hidden grants and also revoke the CREATE ROLE if granted to a schema after the creation.

Adaptive Database Auditing and Security

We are working with customers to design security for their Oracle databases and also to help and design audit trails.

An audit trail is the easiest countermeasure or control that can be added to a database because if you do not have any audit trails then you don't know what is happening in the database. We have a audit toolkit called PFCLATK that can be used to design and implement audit trails really easily and quickly.

This toolkit and service is a set of PL/SQL and meta data that allows policy based audit (not the same as Unified audit) to be added. We can also use it with Unified audit or syslog as well. We combine different sources with audit and different policies and events. The toolkit is policy based and each policy is split in two. The policy includes what raw audit to collect and then how to test if an audit event has come true. So, an example could be:

@@check.sql "CORE: Create EVE.1.10 Policy"
begin
-- ---------------------------------------------------------------------------
-- create the policy
-- ---------------------------------------------------------------------------
pfclatk.createpolicy('EVE.1.10');
-- ---------------------------------------------------------------------------
-- create the rules to collect data
-- ---------------------------------------------------------------------------
pfclatk.createandaddrule('EVE.1.10','EVE.1.10:All Statements by special user','CORE-P','all statements',pfclatk.getfactor('SPECIAL-USER'));
pfclatk.createandaddrule('EVE.1.10','EVE.1.10:LOGON','TRIG-S','LOGON');
-- ---------------------------------------------------------------------------
-- create the filter
-- ---------------------------------------------------------------------------
pfclatk.addfilter('EVE.1.10:SPECIAL-USER','EVE.1.10','HALFDAY',
'[Alert] A Special user has performed actions',
'select ''{''||a.action_name||''} used by {''||a.username||''} using IP {''||p.ip_address||''} with error code {''||a.returncode||''}'' from dba_audit_trail a, pfclatk_audit p where a.username in ('||pfclatk.getfactorc('SPECIAL-USER')||') and a.sessionid=p.sessionid and p.audit_type=''LOGON''');
-- ---------------------------------------------------------------------------
-- enable the policy
-- ---------------------------------------------------------------------------
--pfclatk.enablepolicy('EVE.1.10');
end;
/

In this example the policy is created and is aimed at detecting if a defined special user has connected and then all statements that they execute. You can specify what a special user is. It could be a performance monitoring user or a third party connection user or what ever you choose. The policy is split into two sections; the first creates the collection rules in this case a logon trigger and also all statements. You specify the special users with factors that are used here. An example factor is:

...

pfclatk.addfactor('SPECIAL-USER','SCANNER');
pfclatk.addfactor('SPECIAL-USER','MONITOR');
...

The second half of the policy specifies the event filter. This checks if the event has occurred and this can be run on specified intervals such as ever few minutes, half a day or once a day or...

The PFCLATK toolkit takes care of running the filters. Enabling or disabling a policy turns on the lower level audit or trigger or....

So, this toolkit and service is great as we can help customers get a useful audit trail up and running very fast so if you have not got an audit trail and do not know who and what is going on in your database talk to us so that we can help you reduce your risk and worry.

So, back to the main part of the story. If we have a simple to use and comprehensive audit trail where we can easily specify what we want to audit and we can easily enable and disable individual events then this fact alone allows us to design and implement adaptive audit trails. Everyone (even us non Americans) have heard of the DEFCON levels, where DEFCON 5 is relatively safe and DEFCON 1 could mean the outbreak of nuclear war. The USA has never been at DEFCON 1 as far as I know but was at DEFCON 2 during the Cuban missile crisis. We can imagine similar levels of audit trail in a database.

Audit level 5 would mean its safe, no sign of attacks and we should capture lighter audit. The heavier the audit the more audit data is created and then its more storage and more processing and more costs. So imagine that we are at AUDIT LEVEL 5 and we detect a lower level of attack signs. We can then increase the AUDIT LEVEL to 4 and turn on or enable more policies that capture more audit data. We can go right up to AUDIT LEVEL 1. Imagine that we know there is an attack we can then audit everything at AUDIT LEVEL 1. This would be onerous normally but necessary if we know there is a major issue.

The key idea here is we create some AUDIT LEVEL policies that use the same type of audit rules and events/filters/factors as above and we use these to detect potential changes in AUDIT LEVELS. The toolkit can handle an alert being detected automatically and the payload can be to enable or disable audit. So we have an adaptive audit system that could be used if needed. I would recommend that you set up a comprehensive audit first and enable it and use it before thinking about adaptive audit of course.

Because we can create a policy based audit where we can use events to take actions this means that we could/can also implement adaptive security using the same techniques and triggers (not database triggers). We can have different levels of database security and use audit levels to trigger changes in the actual Oracle database security. Of course you should not even consider this until you have a good level of Oracle security implemented already. We could have a situation where the database runs with a lower level of security (I don't mean no security) and if there is an attack detected we can raise the security level. For instance under normal security we allow connections from most IP addresses and most applications but if we decided to raise the security level we may only allow access to the database from critical applications and block others.

We can also link these ideas of adaptive audit and adaptive security across the whole estate of databases. If one database detects a possible AUDIT LEVEL that could mean an attack then it can signal all or groups of databases and they could increase their audit level or security level.

This is a really interesting area and can be powerful to implement.

The challenges of securing data in an Oracle database

I will be doing a talk at an even in Eight Members Club Bank, 1 Change Alley, London,EC3V 3ND on the 14th June 2022. The event runs from 8am to 10am. The event is free to attend and to register go to Northdoor, the organisers website. I hope to see you there!!

In this talk I will use the Oracle database as the example as that is my area of focus and expertise but the talk is general to all databases so even if you use SQL Server or DB2 or Postgres or DB2 or... the ideas and discussions are just as valid for you to get something useful from it.

This talk is based on many years of working with customers to assess the security of the data held in their databases. Remember, our task is to secure the data we hold and process and to not just simply try and secure the database software itself; the problem space is more complex than that.

I will discuss the main threats to data held in an database from the complexity of the database stack; the lack of a strategy in most companies to secure data in a database and a lack of evidence of actual security of data in a database when I come and review it.

Legacy, default installations and the move to put databases in the cloud do not improve the picture of the core database security and data security itself. I will discuss why this picture persists across the industry in my experience and then the best part of the talk; what can companies do to make data more secure in an Oracle database and how to do it cost effectively in terms of time and money and use of or lack of skilled people.

I will expand on all of this at the talk in London on the 14th June. Please come along and list to myself and also Noam Markfeld who will speak about securing and masking sensitive data and masking.

To register please visit this link.

Add License Checks Anywhere in your PL/SQL

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 obfuscated PL/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