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.

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

Software from Building Blocks - Fast Development - One Month Projects

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

Make Pete Finnigan a remote expert part of your team

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.

Do we Need to Revoke PUBLIC from a User?

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