Pete Finnigan's Oracle Security Weblog

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

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

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


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

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

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

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

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

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

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


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


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


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

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

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

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

A simple script showing a nested loop is here:

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

And the output is here:

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

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

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

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


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


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


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


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


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

A simple call sequence inside your application would be:

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

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

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

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

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

In the first blog in this series we discussed the main issue with using DBMS_CRYPTO to encrypt data within the database. This is the lack of key management provided by Oracle natively for use with this package. I had intended to make the second part the design of a key vault for use with DBMS_CRYPTO or DBMS_OBFUSCATION_TOOLKIT but I decided to switch up part two as an investigation into whether its likely that there is any access to wallets or encryption that could be used where key management is already present. As we said in the last post if we could use a wallet then some level of key management is done, in that we have a wallet password we can cycle and if we can store a master key in a wallet then we could cycle the master key and we can then store column or other keys elsewhere as they are encrypted.

Here is the table of posts (now 6 parts):

1. Part 1: Encryption Key Management with DBMS_CRYPTO - Discuss the problem in general and the need to encrypt data in the database


2. Part 2: The search for wallets- The search for wallet interfaces in the database


3. Part 3: - We will present a simple solution design to store an encryption key for use with DBMS_CRYPTO and discuss some of its flaws


4. Part 4: - Lets build a key vault


5. Part 5: - Protecting the key vault


6. Part 6: - Using the key vault with DBMS_CRYPTO and SQL

If we look in the database for any objects that include the name WALLET we get:

SQL> col owner for a30
SQL> col object_name for a30
SQL> col object_type for a30
SQL> set lines 220
SQL> l
  1* select owner,object_name,object_type from dba_objects where object_name like '%WALLET%'
SQL> select owner,object_name,object_type from dba_objects where object_name like '%WALLET%' and object_type not in ('INDEX','SYNONYM');

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
SYS                            GV_$WALLET                     VIEW
SYS                            V_$WALLET                      VIEW
SYS                            V_$ENCRYPTION_WALLET           VIEW
SYS                            GV_$ENCRYPTION_WALLET          VIEW
SYS                            NACL$_WALLET                   TABLE
SYS                            DBA_WALLET_ACLS                VIEW
SYS                            CDB_WALLET_ACLS                VIEW
SYS                            DBA_WALLET_ACES                VIEW
SYS                            CDB_WALLET_ACES                VIEW
SYS                            USER_WALLET_ACES               VIEW
SYS                            NACL$_WALLET_EXP               VIEW

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
SYS                            NACL$_WALLET_EXP_TBL           TABLE

12 rows selected.

SQL>

Nothing looks useful here in terms of our goal. If we now search for PL/SQL:

SQL> select owner,object_name,procedure_name
  2  from dba_procedures where procedure_name like '%WALLET%';

OWNER                          OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS                            UTL_HTTP                       SET_AUTHENTICATION_FROM_WALLET
SYS                            UTL_HTTP                       SET_WALLET
SYS                            DBMS_ISCHED                    GET_AGENT_WALLET_LOCATION
SYS                            DBMS_GSM_FIX                   UPDATEWALLETFORBACKUP
SYS                            DBMS_NETWORK_ACL_ADMIN         GET_WALLET_ACLID
SYS                            DBMS_NETWORK_ACL_ADMIN         SET_WALLET_ACL
SYS                            DBMS_NETWORK_ACL_ADMIN         REMOVE_WALLET_ACE
SYS                            DBMS_NETWORK_ACL_ADMIN         APPEND_WALLET_ACL
SYS                            DBMS_NETWORK_ACL_ADMIN         APPEND_WALLET_ACE
SYS                            DBMS_NETWORK_ACL_ADMIN         UNASSIGN_WALLET_ACL
SYS                            DBMS_NETWORK_ACL_ADMIN         ASSIGN_WALLET_ACL

OWNER                          OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS                            KUPU$UTILITIES                 CHECK_ENCRYPTION_WALLET
SYS                            KUPU$UTILITIES_INT             CHECK_ENCRYPTION_WALLET
XDB                            DBMS_XDB_ADMIN                 INSTALLDEFAULTWALLET

14 rows selected.

SQL>

At first glance UTL_HTTP and KUPU$UTILITIES may be useful but UTL_HTTP may not be useful as its a web based use and not encryption. you need to create a wallet with orapki or openssl and whislt UTL_HTTP can be used to open the wallet and set the wallet only it does not seem to have a way to add an encryption key or retrieve that key - its for SSL certificates.

What about certificate based objects:

SQL> select owner,object_name,procedure_name
2 from dba_procedures where procedure_name like '%CERT%';

OWNER OBJECT_NAME PROCEDURE_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS DBMS_ISCHED ADD_AGENT_CERT
SYS DBMS_USER_CERTS DROP_CERTIFICATE
SYS DBMS_USER_CERTS ADD_CERTIFICATE
SYS DBMS_ISCHED_REMOTE_ACCESS ADD_AGENT_CERTIFICATE

SQL>

and PL/SQL:

SQL> select owner,object_name,procedure_name
  2  from dba_procedures where object_name like '%CERT%';

OWNER                          OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS                            DBMS_USER_CERTS                ADD_CERTIFICATE
SYS                            DBMS_USER_CERTS                DROP_CERTIFICATE
REMOTE_SCHEDULER_AGENT         ADD_AGENT_CERTIFICATE
SYS                            DBMS_USER_CERTS

SQL>

We could use utl_http to open a wallet; first create the wallet with orapki and then use ACLs to assign the wallet to the encryption and use a certificate as a key - but certificate cannot be accessed via PL/SQL, only used in a HTTPS web transaction from UTL_HTTP

If we go back to KUPU$UTILITIES and KUPU$UTILITIES_INT and have a more detailed look at the first package:

SQL> desc sys.kupu$utilities
PROCEDURE BITCLR
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MASK1                          NUMBER                  IN/OUT
 MASK2                          NUMBER                  IN
PROCEDURE BITSET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MASK1                          NUMBER                  IN/OUT
 MASK2                          NUMBER                  IN
FUNCTION BITSETRET RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MASK1                          NUMBER                  IN
 MASK2                          NUMBER                  IN
PROCEDURE BITSOC
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MASK1                          NUMBER                  IN/OUT
 MASK2                          NUMBER                  IN
 EXPR                           BOOLEAN                 IN
FUNCTION BITTST RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MASK1                          NUMBER                  IN
 MASK2                          NUMBER                  IN
FUNCTION BITTSTANY RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MASK1                          NUMBER                  IN
 MASK2                          NUMBER                  IN
FUNCTION BOOLTOINT RETURNS BINARY_INTEGER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BOOLVAL                        BOOLEAN                 IN
 TRUEVAL                        NUMBER(38)              IN     DEFAULT
FUNCTION BOOLTOSTR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BOOLVAL                        BOOLEAN                 IN
PROCEDURE CHECK_ENCRYPTION_WALLET
FUNCTION CHECK_IF_SHARDED_DB RETURNS NUMBER
FUNCTION CHECK_TBS_FOR_TDECOL_TABS RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TBS                            VARCHAR2                IN
PROCEDURE DEBUG
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            VARCHAR2                IN
FUNCTION FORMAT_ERROR_STACK RETURNS VARCHAR2
PROCEDURE GET_DB_VERSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DBLINK                         VARCHAR2                IN
 VERSION                        VARCHAR2                OUT
 COMPAT                         VARCHAR2                OUT
FUNCTION GET_NLS_ALTER_SESSION RETURNS VARCHAR2
FUNCTION GET_PLATFORM_NAME RETURNS VARCHAR2
FUNCTION GET_REMOTE_DBLINK_USER RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINK_NAME                      VARCHAR2                IN
FUNCTION GET_TSTZ_VERSION RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 REMOTE_LINK                    VARCHAR2(128)           IN     DEFAULT
FUNCTION REPLACE_XML_VALUES RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
ERROR:
ORA-24328: illegal attribute value


 XML_REPL_TAB                   TABLE OF                IN
 XML_CLOB                       CLOB                    IN
PROCEDURE UPDATEFEATURETABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UTILITY_NAME                   VARCHAR2                IN
PROCEDURE UPDATEFEATURETABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UTILITY_NAME                   VARCHAR2                IN
ERROR:
ORA-24328: illegal attribute value


 FEATURE_INFO                   TABLE OF                IN

SQL>

This package and its internal version are wrapped so we cannot see the source code and they are internal and as far as I can find not documented in any way. There are no references to their use on the internet or in the rdbms/admin folder of the database server. They are most likely used within other SYS packages internally and also in wrapped code. A simple grep of the folder does not locate any publicly visible code that we can see that uses them. The only thing of interest is check_encryption_wallet in the non INT package.

We can check dependencies for kupu$utilities as follows:

SQL> @dep
Enter value for object_to_test: KUPU$UTILITIES
old   3: where referenced_name='&&object_to_test'
new   3: where referenced_name='KUPU$UTILITIES'
Enter value for owner_to_test: SYS
old   4: and referenced_owner='&&owner_to_test'
new   4: and referenced_owner='SYS'

NAME                           OWNER                TYPE
------------------------------ -------------------- ------------------------------
KUPU$UTILITIES                 SYS                  PACKAGE BODY
DBMS_METADATA_INT              SYS                  PACKAGE BODY
DBMS_PLUGTS                    SYS                  PACKAGE BODY
KUPM$MCP                       SYS                  PACKAGE BODY
KUPUTIL                        PUBLIC               SYNONYM

SQL>

Lets describe the internal package:

SQL> desc kupu$utilities_int
PROCEDURE CHECK_ENCRYPTION_WALLET
FUNCTION CHECK_IF_SHARDED_DB RETURNS NUMBER
FUNCTION CHECK_IGNORE_DESC_IN_INDEX RETURNS BOOLEAN
FUNCTION CHECK_TBS_FOR_TDECOL_TABS RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TBS                            VARCHAR2                IN
PROCEDURE CREATE_DIRECTORY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DIR_PATH                       VARCHAR2                IN
PROCEDURE DEBUG
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            VARCHAR2                IN
FUNCTION DECODE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SRC                            VARCHAR2                IN
PROCEDURE DH_CHECK_DIGESTS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
 SRCDIGEST                      VARCHAR2(512)           IN
 TARDIGEST                      VARCHAR2(512)           IN
FUNCTION DH_DECRYPT RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
 SRCBUF                         VARCHAR2                IN
 OUTFMT                         BINARY_INTEGER          IN     DEFAULT
FUNCTION DH_ENCRYPT RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
 SRCBUF                         VARCHAR2                IN
 INPFMT                         BINARY_INTEGER          IN     DEFAULT
PROCEDURE DH_FREE_HANDLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
FUNCTION DH_GEN_SHARED_KEY RETURNS VARCHAR2(512)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
 PUBKEY                         VARCHAR2(1024)          IN
FUNCTION DH_GET_PUBLIC_KEY RETURNS VARCHAR2(1024)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
FUNCTION DH_NEW_HANDLE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 KEY_SIZE                       NUMBER                  IN
FUNCTION DIRECTORY_SCAN RETURNS BINARY_INTEGER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 INFILESPEC                     VARCHAR2                IN
 INDEFDIRSPEC                   VARCHAR2                IN
 INSKIPFILEVERIFY               BOOLEAN                 IN     DEFAULT
 INSUBDIRSEARCH                 BOOLEAN                 IN     DEFAULT
 OUTHASWILDCARD                 BOOLEAN                 OUT
 INPLSQLARRAY                   T_DATAFILE_NAMES        IN/OUT
 INARRAYINDEX                   BINARY_INTEGER          IN
FUNCTION ENCODE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SRC                            VARCHAR2                IN
FUNCTION GET_DP_UPDATE_LOCK RETURNS VARCHAR2
FUNCTION GET_PARAMETER_VALUE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
FUNCTION GET_REMOTE_DBLINK_USER RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 USERID                         NUMBER                  IN
 LINK_NAME                      VARCHAR2                IN
FUNCTION GET_SESSION_STAT RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STATISTIC                      VARCHAR2                IN
 SID                            NUMBER                  IN     DEFAULT
FUNCTION GET_SHARDED_TABLE_FAMILY_ID RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMANAME                     VARCHAR2                IN
 TABLENAME                      VARCHAR2                IN
PROCEDURE INTALGCONV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ALG                            BINARY_INTEGER          IN
 IN_VAL                         VARCHAR2                IN
 OUT_VAL                        VARCHAR2                OUT
FUNCTION INT_CHECK_IF_SHARDED_DB RETURNS NUMBER
FUNCTION IS_SUPERSET RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FRCSID                         BINARY_INTEGER          IN
 TOCSID                         BINARY_INTEGER          IN
FUNCTION MAX_DATAPUMP_JOBS_PER_PDB RETURNS BINARY_INTEGER
PROCEDURE RELEASE_DP_UPDATE_LOCK
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SWLOCK_HANDLE                  VARCHAR2                IN
PROCEDURE SET_DEBUG
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEBUG_FLAGS                    BINARY_INTEGER          IN
PROCEDURE SET_KGL_TIME_TO_WAIT_FOR_LOCKS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LWAIT_TIME                     NUMBER                  IN
FUNCTION TEN_G_LOGON_ALLOWED RETURNS NUMBER
PROCEDURE UPDATEFEATURETABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UTILITY_NAME                   VARCHAR2                IN
 FEATURE_INFO                   FEATURE_INFO_T          IN
PROCEDURE WRITEAUDITRECORD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ACTION                         BINARY_INTEGER          IN
 MASTER_ONLY                    BOOLEAN                 IN     DEFAULT
 DATA_ONLY                      BOOLEAN                 IN     DEFAULT
 METADATA_ONLY                  BOOLEAN                 IN     DEFAULT
 DUMPFILE_PRESENT               BOOLEAN                 IN     DEFAULT
 JOB_RESTARTED                  BOOLEAN                 IN     DEFAULT
 MASTER_TABNAME                 VARCHAR2                IN
 META_JOB_MODE                  VARCHAR2                IN
 JOB_VERSION                    VARCHAR2                IN
 ACCESS_METHOD                  VARCHAR2                IN
 DATA_OPTIONS                   VARCHAR2                IN
 DUMPER_DIRECTORY               VARCHAR2                IN
 REMOTE_LINK                    VARCHAR2                IN
 TABLE_EXISTS_ACTION            VARCHAR2                IN
 PARTITION_OPTIONS              VARCHAR2                IN
 WARNINGS1                      VARCHAR2                IN

SQL>

There are quite a few functions and procedures of interest listed here:

FUNCTION ENCODE RETURNS VARCHAR2
FUNCTION DH_GET_PUBLIC_KEY RETURNS VARCHAR2(1024)
FUNCTION DH_NEW_HANDLE RETURNS NUMBER
FUNCTION DH_GET_PUBLIC_KEY RETURNS VARCHAR2(1024)
FUNCTION DH_GEN_SHARED_KEY RETURNS VARCHAR2(512)
PROCEDURE DH_FREE_HANDLE
FUNCTION DH_ENCRYPT RETURNS VARCHAR2
FUNCTION DH_DECRYPT RETURNS VARCHAR2
PROCEDURE DH_CHECK_DIGESTS
PROCEDURE CHECK_ENCRYPTION_WALLET

Of particular interest is the wallet procedure CHECK_ENCRYPTION_WALLET and the encrypt/decrypt functions. We can also check the dependencies of this package:

SQL> @dep
Enter value for object_to_test: KUPU$UTILITIES_INT
old   3: where referenced_name='&&object_to_test'
new   3: where referenced_name='KUPU$UTILITIES_INT'
Enter value for owner_to_test: SYS
old   4: and referenced_owner='&&owner_to_test'
new   4: and referenced_owner='SYS'

NAME                           OWNER                TYPE
------------------------------ -------------------- ------------------------------
DBMS_DATAPUMP                  SYS                  PACKAGE BODY
KUPV$FT_INT                    SYS                  PACKAGE BODY
KUPP$PROC                      SYS                  PACKAGE BODY
DBMS_DATAPUMP_UTL              SYS                  PACKAGE BODY
KUPV$FT                        SYS                  PACKAGE BODY
KUPU$UTILITIES                 SYS                  PACKAGE BODY
KUPU$UTILITIES_INT             SYS                  PACKAGE BODY
DBMS_METADATA_UTIL             SYS                  PACKAGE BODY
KUPW$WORKER                    SYS                  PACKAGE BODY
KUPM$MCP                       SYS                  PACKAGE BODY
KUPF$FILE_INT                  SYS                  PACKAGE BODY

NAME                           OWNER                TYPE
------------------------------ -------------------- ------------------------------
DBMS_PLUGTS                    SYS                  PACKAGE BODY
DBMS_TTS                       SYS                  PACKAGE BODY
DBMS_METADATA                  SYS                  PACKAGE BODY
DBMS_METADATA_DIFF             SYS                  PACKAGE BODY

15 rows selected.

SQL>

And also look to see what packages use this internal package:

SQL> col referenced_owner for a30
SQL> col referenced_name for a30
SQL> col referenced_type for a30
SQL> select referenced_owner,referenced_name,referenced_type
  2  from dba_dependencies
  3  where name='KUPU$UTILITIES_INT' and owner='SYS';

REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE
------------------------------ ------------------------------ ------------------------------
SYS                            STANDARD                       PACKAGE
SYS                            STANDARD                       PACKAGE
SYS                            PLITBLM                        PACKAGE
SYS                            DBMS_LOCK                      PACKAGE
SYS                            DBMS_UTILITY                   PACKAGE
SYS                            DBMS_ASSERT                    PACKAGE
SYS                            DBMS_METADATA_UTIL             PACKAGE
SYS                            KUPP$PROC                      PACKAGE
SYS                            KUPCC                          PACKAGE
SYS                            KUPF$FILE                      PACKAGE
PUBLIC                         KUPUTIL                        SYNONYM

REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE
------------------------------ ------------------------------ ------------------------------
PUBLIC                         KUPUTIL                        SYNONYM
SYS                            KUPU$UTILITIES_INT             PACKAGE
SYS                            KUPULIB                        LIBRARY
SYS                            X$KSPPI                        TABLE
SYS                            X$KSPPCV                       TABLE

16 rows selected.

SQL>

So, this package KUPU$UTILITIES_INT does not use DBMS_CRYPTO or DBMS_OBFUSCATION_TOOLKIT so its unlikely that there is any package shipped by Oracle that provides key management using wallets in the database. Because we do not see evidence of DBMS_CRYPTO used with wallets to manage symetric keys then the assumtion is that the undocumented package KUPU$UTILITIES_INT is aimed at the TDE wallet and encryption using a TDE key. But it is not documented so we dont know for sure.

Lets try some of the kupu$utility_int functions, the wallet function and the encrypt functions for fun!

In particular these are the interesting ones from earlier:

FUNCTION ENCODE RETURNS VARCHAR2
FUNCTION DH_GET_PUBLIC_KEY RETURNS VARCHAR2(1024)
FUNCTION DH_NEW_HANDLE RETURNS NUMBER
FUNCTION DH_GET_PUBLIC_KEY RETURNS VARCHAR2(1024)
FUNCTION DH_GEN_SHARED_KEY RETURNS VARCHAR2(512)
PROCEDURE DH_FREE_HANDLE
FUNCTION DH_ENCRYPT RETURNS VARCHAR2
FUNCTION DH_DECRYPT RETURNS VARCHAR2
PROCEDURE DH_CHECK_DIGESTS
PROCEDURE CHECK_ENCRYPTION_WALLET

We can try the wallet procedure:

SQL> exec sys.kupu$utilities_int.check_encryption_wallet;
BEGIN sys.kupu$utilities_int.check_encryption_wallet; END;

*
ERROR at line 1:
ORA-28365: wallet is not open
ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 96
ORA-06512: at line 1


SQL>

Which wallet is not open?

SQL> set serveroutput on
SQL> @sc_print 'select * from v$encryption_wallet'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from v$encryption_wallet','''','''''');
Executing Query [select * from v$encryption_wallet]
WRL_TYPE                      : FILE
WRL_PARAMETER                 :
STATUS                        : NOT_AVAILABLE
WALLET_TYPE                   : UNKNOWN
WALLET_ORDER                  : SINGLE
KEYSTORE_MODE                 : UNITED
FULLY_BACKED_UP               : UNDEFINED
CON_ID                        : 3
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

This seems to align

SQL> @sc_print 'select * from v$wallet'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from v$wallet','''','''''');
Executing Query [select * from v$wallet]

PL/SQL procedure successfully completed.

SQL>

SQL> @sc_print 'select * from v$encryption_keys'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from v$encryption_keys','''','''''');
Executing Query [select * from v$encryption_keys]

PL/SQL procedure successfully completed.

SQL>

If this function tries to use a TDE wallet then we likely would need a TDE license to create a wallet and open it. This would make use of these functions not possible even if we could find how to use them properly. Lets try the encryption:

FUNCTION DH_DECRYPT RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
 SRCBUF                         VARCHAR2                IN
 OUTFMT                         BINARY_INTEGER          IN     DEFAULT
FUNCTION DH_ENCRYPT RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
 SRCBUF                         VARCHAR2                IN
 INPFMT                         BINARY_INTEGER          IN     DEFAULT

My guess is these functions could encrypt and decrypt TDE data?

SQL> set serveroutput on
SQL> declare
  2  lv_in varchar2(32767):='this is text';
  3  lv_out varchar2(32767);
  4  begin
  5  lv_out:=kupu$utilities_int.dh_encrypt(1,lv_in,1);
  6  dbms_output.put_line(lv_out);
  7  end;
  8  /

PL/SQL procedure successfully completed.


SQL> 

The call did not fail but didnt return anything; maybe it needs the wallet?

There is one way to use a wallet to manage keys for DBMS_CRYPTO and this is to use Oracle Key Vault introduced in 21c and use the integration accelerator for DBMS_CRYPTO. There is a PL/SQL API that allows simple features such as FetchKey() and CreateKey() from a wallet held in the Oracle Key Vault. There is a sample script dbms_crypto_using_okv_keymgmt.sql available with the product demonstrating use of a wallet and DBMS_CRYPTO. A Java SDK Program KeyManager.java is supplied called okv_key_mgtm that can be modified.

Oracle Key Vault (OKV) is out of the scope for most people wanting to use a wallet as OKV is an additonally licensed appliance and is overkill if you wanted to use DBMS_CRYPTO to encrypt small amounts of data columns / rows

So in conclusion there is no simple free way to use a wallet to manage keys for use with DBMS_CRYPTO

#oracleace #sym_42 #oracle #key #vault #wallet #encryption #tde #key

I often get asked how to use DBMS_CRYPTO to encrypt data in the Oracle database. Or I used to be asked how to use DBMS_OBFUSCATION_TOOLKIT when it was the go-to encryption in an Oracle database.

Before we go far; this is the first part of a 5 part blog around the subject of managing keys for use with DBMS_CRYPTO. Of course as I write this blog the links to the next 4 parts do not exist; I hope to come back and add these very soon. The five proposed blog parts are:

1. Part 1: (This blog) - Discuss the problem in general and the need to encrypt data in the database


2. Part 2 - We will present a simple solution design to store an encryption key for use with DBMS_CRYPTO and discuss some of its flaws


3. Part 3 - Lets build a key vault


4. Part 4 - Protecting the key vault


5. Part 5 - Using the key vault with DBMS_CRYPTO and SQL

This method of using Oracle built in packages to encrypt data differs from TDE (Transparent Database Encryption) in that TDE is automatic as it either decrypts blocks of data returned to the SGA from data files on disk for tablespace encryption or it returns encrypted blocks and decrypts columns of data from rows of data as it needs to on demand for column based encryption.

Both TDE mechanisms are automatic and from the users perspective (The SQL executor) the data is still manipulated in clear text as normal but is in fact stored on disk encrypted. With TDE the keys are managed automatically and are generally in a wallet. There are also three layers of keys 1: Passphrase/password to open the wallet or autologin, 2: Master key stored in the wallet, 3: column keys or tablespace keys stored locally encrypted. These final keys are used to encrypt and decrypt the actual data and these keys are encrypted/decrypted by the master key. TDE uses synchronous two way encryption and whilst its ease of use is fantastic the problem - if you can call it a problem - is that the data is still available transparently via a SQL interface where the user of that SQL has rights to access encrypted data. So, if a user can select from a table in an encrypted tablespace or from a column of data that is encrypted he/she still can and can see clear text data.

The purpose of TDE is to protect the data in the datafiles at rest.

The one open gateway if you will is that the data files are encrypted - good - but the software owner (can be "oracle") is usually the only user able to access these files at the file system level - BUT that user can also simply connect to the database "/ as sysdba" and access any protected data. To prevent this in a database using TDE you must also limit the access "as sysdba" to the encrypted data. You must also use other mechanisms from normal object rights to tools such as Database Vault or VPD to limit access to the tables and therefore the encrypted data.

Of course we mentioned a wallet above used as an encryption key store but Oracle also supports the use of a Hardware Security Module (HSM) to store and protect the master key. In this case the customer can use a HSM from a third party where the master key never leaves the HSM and a C library is provided that allows TDE to talk to the HSM using standard wallets and wallet access software silently via the database. In fact in TDE the commands to open the wallet/HSM and pass the keys to be used is done silently in the background.

So, TDE is a great product and makes encrypting data easy without much effort and it also provides a way to protect data files at rest BUT what if you want to make sure that the data held in the database is normally presented encrypted to any non-authorised random access?

This is also a solution to the problem presented earlier and it means that even if someone has SQL access like with TDE they do not see clear text data unless also authorised to decrypt the data

The solutions provided by Oracle are the DBMS_CRYPTO package and in older databases the DBMS_OBFUSCATION_TOOLKIT package. The thing that stands out immediately with DBMS_CRYPTO and earlier with DBMS_OBFUSCATION_TOOLKIT is there there is no built in key management and no simple way to use or access a standard wallet. There are no mechanisms to store the encryption keys securely or provide management of keys so that a password / passphrase that unlocks the master key could be changed / cycled when needed but leaving the master key and the data encrypted with the original keys; or there is no way to cycle the master key so that again the column or row keys are left encrypted but the master key can be changed and the column keys decrypted/encrypted to allow the change but no change to the data. Finally there is no easy built in way to cycle the column keys.

Further there is no thoughts to backup of data and keys or data and old data that is encrypted say on tape with old keys.

We will not get into semantics of the use of DBMS_CRYPTO; the algorithms and other factors available as well as all of the concerns around the problems of actually encrypting data such as a join from a column that is encrypted to a clear text column or indexes on encrypted columns or the performance of dealing with now encrypted data or any changes to storage requirements such as column data types or lengths of data.

All of these are valid issues but as part of this blog and following blogs we are not even in a position to discuss these items until we can securely use DBMS_CRYPTO and keys. Performance, storage and joins are moot if we simply have to hard code a single key or pass it in. I am going to focus on the problem of keys and managing them for DBMS_CRYPTO.

Oracle does not appear to have any interfaces that would allow us to use a wallet to store and retrieve a master key for use in PL/SQL and with DBMS_CRYPTO. A quick search of the database shows:

SQL> col object_name for a30
SQL> col owner for a30
SQL> col procedure_name for a30
SQL> set lines 220
SQL> select owner,object_name,procedure_name
  2  from dba_procedures
  3  where procedure_name like '%WALLET%'
  4  or object_name like '%WALLET%';

OWNER                          OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            UTL_HTTP                       SET_AUTHENTICATION_FROM_WALLET
SYS                            UTL_HTTP                       SET_WALLET
SYS                            DBMS_ISCHED                    GET_AGENT_WALLET_LOCATION
SYS                            DBMS_GSM_FIX                   UPDATEWALLETFORBACKUP
SYS                            DBMS_NETWORK_ACL_ADMIN         GET_WALLET_ACLID
SYS                            DBMS_NETWORK_ACL_ADMIN         SET_WALLET_ACL
SYS                            DBMS_NETWORK_ACL_ADMIN         REMOVE_WALLET_ACE
SYS                            DBMS_NETWORK_ACL_ADMIN         APPEND_WALLET_ACL
SYS                            DBMS_NETWORK_ACL_ADMIN         APPEND_WALLET_ACE
SYS                            DBMS_NETWORK_ACL_ADMIN         UNASSIGN_WALLET_ACL
SYS                            DBMS_NETWORK_ACL_ADMIN         ASSIGN_WALLET_ACL

OWNER                          OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            KUPU$UTILITIES                 CHECK_ENCRYPTION_WALLET
SYS                            KUPU$UTILITIES_INT             CHECK_ENCRYPTION_WALLET
XDB                            DBMS_XDB_ADMIN                 INSTALLDEFAULTWALLET

14 rows selected.

SQL>

Nothing stands out as a simple interface to create a wallet, store a key and retrieve a key as needed. We could get around this by orapki/openssl and creating a wallet and adding a key to a bucket. We could then use Java and the bouncy castle API to access the wallet and expose this to PL/SQL.

This is not ideal;

We really would like at least 3 layers of keys, one to open the wallet / vault, a master key we never see and then actual data/column keys encrypted/decrypted by the master key. Oracle with TDE does similar so we should aim for a similar goal. We also should have commands (APIs) to allow the password, master key or column keys to be changed.

We also must consider stealing of the keys or data at all stages of storage or in-flight

In other wards we want a vault that manages keys and the use of DBMS_CRYPTO.

I have done this and designed a key/crypto vault; we will go into the design in more details in the next post. It allows a password to be provided to open the vault; this is the first key and is not stored in the database or in the vault. This is similar to a wallet in that a password is used to open the wallet and this password is not stored in the wallet. We then generate and store master keys and column keys and can manage old keys through use of a manifest of sorts

Oracle provides a simple interface to encryption via DBMS_CRYPTO but leaves the hardest part to you.

Similar to audit trails in the Oracle database; all tools are provided but you need to decide what to audit and how to review that audit trail for attack

similar to securing the data; all tools are provided but you need to actually design and implement the data security.

I mention these two similar problems in other areas because we need to solve them also as part of the key vault. We need to protect and secure this critical data (keys) and also audit access.

#oracleace #sym_42 #oracle #data #encryption #security #datasecurity #keys #vault #audit #audittrails