Pete Finnigan's Oracle Security Weblog

I had a conversation a few weeks ago with someone who asked me how to find a missing table when you have a wrapped PL/SQL file and cannot see the source code and you install it and it gives an ORA-0942 error - Table or view does not exist. He asked me for advice on how you might find out what table or view cannot be accessed by the wrapped PL/SQL.

This was an interesting question.

Firstly why not just look at the clear text PL/SQL? - well in this example case it was a commercial application he was asked to install into a database and the PL/SQL was wrapped and he could not see the code. He did of course ask the vendor and in the end got an answer that some other scripts that created the tables needed to be run first and problem solved.

So assuming that you do not have access to the clear text PL/SQL what can you do?

I want to create a simple example. First connect to my 23c database as SYS and create a sample user PETE1:

[oracle@localhost ~]$ sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 09:58:52 2024
Version 23.2.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> create user pete1 identified by pete1;

User created.

SQL> grant create session to pete1;

Grant succeeded.

SQL> grant unlimited tablespace to pete1;

Grant succeeded.

SQL> grant create procedure to pete1;

Grant succeeded.

SQL>

Connect to my sample user PETE1 and test whether I can access SYS.USER$. Obviously I know the answer in advance that the error is my PL/SQL cannot access SYS.USER$ but I need to be sure there is an error first simply in SQL*Plus:

SQL> connect pete1/pete1@//192.168.56.18:1521/freepdb1
Connected.
SQL> select name from sys.user$;
select name from sys.user$
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Now create the PL/SQL and in it access SYS.USER$ - the table that is missing, inaccessible.

SQL> get test.sql
  1  -- test PL/SQL for wrapped create
  2  create or replace procedure test as
  3     lv_password varchar2(4000);
  4  begin
  5     select password
  6     into lv_password
  7     from sys.user$
  8     where name='SYS';
  9* end;
 10  .

Now wrap the PL/SQL:

[oracle@localhost ~]$ wrap iname=test.sql oname=test.plb

PL/SQL Wrapper: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 10:07:41 2024
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

Processing test.sql to test.plb
[oracle@localhost ~]$

Show the code to prove it is wrapped:

[oracle@localhost ~]$ cat test.plb
create or replace procedure test wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
81 be
LlzHv6ZvxN4mhihv9rosYR6UAmEwg5nnm7+fMr2ywFwWoWLRzLh0iwbSvW0ouHTLy/4owMzn
x3TAM7h0ZSV8f3x8UKCLwMAy/tKGBnSfgVIyy8yp1YYG0tKZCOpnJfqVKW9duwovdojYH6uK
qxoVlYi57p6glXKzsT0rpBbYSNdD/MHg16ama3fKHg==

/
[oracle@localhost ~]$

Now let us try and install this code into my database:

SQL> @test.plb

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2      PL/SQL: SQL Statement ignored
6/11     PL/SQL: ORA-00942: table or view does not exist
SQL>

OK, so we know there is a ORA-00942 error but we do not know what table causes it so that we can fix it as the code is wrapped and looking at lines 4 and 6 is meaningless in this context

How to know what table or view does not exist?

Try the ALL_ERRORS view

SQL> set serveroutput on
SQL> @sc_print 'select * from all_errors'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from all_errors','''','''''');
Executing Query [select * from all_errors]
OWNER                         : PETE1
NAME                          : TEST
TYPE                          : PROCEDURE
SEQUENCE                      : 1
LINE                          : 6
POSITION                      : 11
TEXT                          : PL/SQL: ORA-00942: table or view does not exist
ATTRIBUTE                     : ERROR
MESSAGE_NUMBER                : 0
-------------------------------------------
OWNER                         : PETE1
NAME                          : TEST
TYPE                          : PROCEDURE
SEQUENCE                      : 2
LINE                          : 4
POSITION                      : 2
TEXT                          : PL/SQL: SQL Statement ignored
ATTRIBUTE                     : ERROR
MESSAGE_NUMBER                : 0
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

This is not useful as it doesn't tell me what table does not exist and is a repeat of the error we saw trying to compile the wrapped code in SQL*Pus.

We can try dependencies instead:

SQL> @sc_print 'select * from all_dependencies where name=''''TEST'''' and referenced_type=''''TABLE'''''
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from all_dependencies where name=''TEST'' and referenced_type=''TABLE''','''','''''');
Executing Query [select * from all_dependencies where name='TEST' and
referenced_type='TABLE']
OWNER                         : PETE1
NAME                          : TEST
TYPE                          : PROCEDURE
REFERENCED_OWNER              : SYS
REFERENCED_NAME               : USER$
REFERENCED_TYPE               : TABLE
REFERENCED_LINK_NAME          :
DEPENDENCY_TYPE               : HARD
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Yes, the issue is there in the dependencies view and shows that it is indeed SYS.USER$ but if there were hundreds of tables then its harder to locate the actual one. Test if we can access it as PETE1 in SQL*Plus:

SQL> desc sys.user$
ERROR:
ORA-04043: Object sys.user$ does not exist.


SQL>

Reconnect as SYS and grant ALTER SESSION to PETE1 to allow the use of trace:

C:\_audit_scripts\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 18 10:22:48 2024

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> grant alter session to pete1;

Grant succeeded.

SQL>

Set trace and install the PLB again:

[oracle@localhost ~]$ sqlplus pete1/pete1@//192.168.56.18:1521/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 10:23:02 2024
Version 23.2.0.0.0

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

Last Successful login time: Mon Mar 18 2024 10:22:32 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> @test.plb

Warning: Procedure created with compilation errors.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL>

locate the trace file:

SQL> sho parameter diag

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /opt/oracle
diagnostics_control                  string      IGNORE
SQL>

Look for the potential trace file:

[oracle@localhost oracle]$ find . -name "*.trc" -print 2>/dev/null | xargs ls -al 2>/dev/null | grep "Mar 18"
-rw-r-----. 1 oracle oinstall 2243134 Mar 18 10:30 ./diag/rdbms/free/FREE/trace/FREE_dbrm_3163.trc
-rw-r-----. 1 oracle oinstall  101670 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr0_184977.trc
-rw-r-----. 1 oracle oinstall  905271 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr1_179252.trc
-rw-r-----. 1 oracle oinstall   42302 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr2_185301.trc
-rw-r-----. 1 oracle oinstall   71822 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr3_185112.trc
-rw-r-----. 1 oracle oinstall   41974 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr4_185336.trc
-rw-r-----. 1 oracle oinstall    1151 Mar 18 09:57 ./diag/rdbms/free/FREE/trace/FREE_j001_183579.trc
-rw-r-----. 1 oracle oinstall    1150 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_j003_184635.trc
-rw-r-----. 1 oracle oinstall    1453 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_j005_184641.trc
-rw-r-----. 1 oracle oinstall    1449 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_j006_184644.trc
-rw-r-----. 1 oracle oinstall  943553 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_lmhb_3199_data.trc
-rw-r--r--. 1 oracle oinstall   31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m000_183247.trc
-rw-r-----. 1 oracle oinstall   72908 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m000_184934.trc
-rw-r-----. 1 oracle oinstall   35341 Mar 18 10:20 ./diag/rdbms/free/FREE/trace/FREE_m001_184937.trc
-rw-r--r--. 1 oracle oinstall   31938 Mar 18 10:09 ./diag/rdbms/free/FREE/trace/FREE_m002_184940.trc
-rw-r--r--. 1 oracle oinstall   31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m004_184027.trc
-rw-r--r--. 1 oracle oinstall   31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m005_180811.trc
-rw-r-----. 1 oracle oinstall    1744 Mar 18 10:22 ./diag/rdbms/free/FREE/trace/FREE_ora_185518.trc
-rw-r-----. 1 oracle oinstall  537629 Mar 18 10:24 ./diag/rdbms/free/FREE/trace/FREE_ora_185538.trc
[oracle@localhost oracle]$

Looks like the last ones are a potential match so grep now for the PL/SQL procedure:

[oracle@localhost trace]$ grep -i test *.trc
FREE_m000_178093.trc:      SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1608) into  (partition WRH$_AWR_TEST_1_1405253007_1608,   partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m000_178093.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_178093.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_184934.trc:      SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1656) into  (partition WRH$_AWR_TEST_1_1405253007_1656,   partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m000_184934.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_184934.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m001_179164.trc:      SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1632) into  (partition WRH$_AWR_TEST_1_1405253007_1632,   partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m001_179164.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m001_179164.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_ora_185538.trc:create or replace procedure test wrapped
FREE_ora_185538.trc:  value="procedure test wrapped
FREE_ora_185538.trc:  value="TEST"
[oracle@localhost trace]$

Nothing useful is found in the trace. We can see the ORA-00942 and the insert into error$ but not the SQL that causes the error in the trace file.

There are other things we can do; the first is talk to the vendor and ask why their wrapped code fails to compile or ask the vendor about the line number in the code and what table is missing. We can use simple ideas such as dependencies to try and locate the missing table or permissions when we try and compile wrapped code where we don't have access to the clear text code.

Also remember the 9i wrap.exe and lower was the front end to a PL/SQL compiler, the 10g and higher is a simple obfuscation of the clear text code.

#oracleace #sym_42 #oracle #database #security #plsql #compile #permissions #error #942 #23c

Do you develop software in PL/SQL?

I will show you in the next few minutes how you can learn to find security vulnerabilities in your PL/SQL code

Even if the database that your PL/SQL is deployed to is secure then if you do not program your PL/SQL defensively and securely then it is a matter of when and not if that a breach of your customer or employers data could occur through non-secure PL/SQL applications.

If you are not aware of what security issues in PL/SQL look like and how they could be exploited then it is not easy for you to code your PL/SQL securely. How would you feel if it was your lack of knowledge that caused a data breach to occur?

We have an easy solution for you. We are running two live training events online on the 27th March 2024 on UK hours and the same class is re-run on the 28th March 2024 but this is on USA EST time zones. Some highlights of the class next:

• The classes are taught by Pete Finnigan live but on-line via webex so you do not need to leave your office or home to attend.


• You can ask questions at any time during the class day and get access to Pete and learn from him and his over 20 years of experience in this field of secure coding in PL/SQL.


• The class is one day and is called Secure Coding in PL/SQL and is taught live from 9am to 5pm. The class includes taught lessons and many demonstrations throughout the day.


• Each student will receive pdfs of full course notes and lessons and also over 100 free SQL and PL/SQL scripts and tools covering the demonstrations and also the many free tools used in the class.


• You do not need to be a PL/SQL developer to attend although most attendees are usually PL/SQL developers. We have also taught DBA staff and even managers who would like to understand the security risks likely to be found in PL/SQL their developers create.


• The high level agenda is as follows:
  
  
  
  • Data Theft: This lesson covers why data can be stolen or privilege escalated in a
    database focusing on issues related to privileges assigned to PL/SQL, bad
    programming practices and leakage of data. This section is an overview to allow the student to see how PL/SQL fits into
    the security model intended to protect Data
  
  
  • Permissions: We cover permissions of packages and procedures and design decisions that affect security
  
  
  • Coding Errors: This section introduces common PL/SQL Security programming issues and
    for each shows the issue in code form and exploitation and then also in terms
    of secure coding and solution. These include: Input validation, Object validation, Open interfaces, SQL and PL/SQL and Other Injection issues, File and external access, Operating system commands, Vulnerable and dangerous package use and more
  
  
  • Secure Coding Best Practice: We look at fixing the issues and secure coding best practice
  
  
  • Encryption: We use encryption as an example to demonstrate everything we have covered so far
  
  
  • Protecting PL/SQL: This section discusses techniques to lock down PL/SQL in terms of Preventing IPR loss, Prevent unauthorised execution both in the host database or if the code is removed, License type features and wrapping and unwrapping
  
  
  • Finishing Up: We cover processes to secure code and review coding and also automated checking of your code and finally creating secure coding policies
  
  
  
  

The course material and demonstrations have recently been extensively updated and cover up to Oracle version 23c. Of course all earlier versions are also covered.

The course fee is just £440 GBP (plus VAT if applicable).

To register your place on our class then simply send an email to securecode@petefinnigan.com and we will help you secure your place

As a bonus if you book more than one place we will include a 10% discount for all places booked by you.

Your PL/SQL code will be more secure after this class so please register your place now.

To register your place on our class then simply send an email to securecode@petefinnigan.com and we will help you secure your place

#oracleace #sym_42 #oracle #plsql #secure #coding #training