Running revised NetWitness oracle auditing xml files fails with error ORA-00904: : invalid identifier

[#1]

Article Number

000039926

Applies To

RSA Product Set: RSA NetWitness Platform
RSA Product/Service Type: Log Collector
RSA Version/Condition: 11.x
Platform: CentOS
O/S Version: 7
Product Name: Oracle 10g, 11g

Issue

Followed RSA Knowledgebase article, Error message "ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion" is reported when attempting to configure Oracle ODBC collection in RSA Security Analytics.

Running with the revised NetWitness oracle_10g_auditing.xml and oracle_11g_auditing.xml files fails with error ORA-00904: : invalid identifier
 

Oct 18 21:47:43 NWDEC01 NwLogCollector[148857]: [OdbcCollection] [failure] [oracle_11g_auditing.ORA11G] [processing] [ORA11G] [processing]
 An error occurred collecting ODBC events using query tag ORACLE.  Error: Unable to execute statement: Statement:
"SELECT "SYS"."V_$INSTANCE"."VERSION", '-', "SYS"."V_$INSTANCE"."HOST_NAME", '-', "SYS"."V_$INSTANCE"."INSTANCE_NAME", '-', '-', "SYS"."AUD$"."SESSIONID", "SYS"."AUD$"."ENTRYID", "SYS"."AUD$"."STATEMENT", CASE "SYS"."AUD$"."ACTION#" WHEN 101 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') WHEN 102 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') ELSE TO_CHAR(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date),'yyyy-mm-dd hh24:mi:ss') END "timestamp", "SYS"."AUD$"."USERID", "SYS"."AUD$"."USERHOST", "SYS"."AUD$"."TERMINAL", "SYS"."AUD$"."ACTION#", "SYS"."AUD$"."RETURNCODE", "SYS"."AUD$"."OBJ$CREATOR", "SYS"."AUD$"."OBJ$NAME", "SYS"."AUD$"."AUTH$PRIVILEGES", "SYS"."AUD$"."AUTH$GRANTEE", "SYS"."AUD$"."NEW$OWNER", "SYS"."AUD$"."NEW$NAME", "SYS"."AUD$"."SES$ACTIONS", "SYS"."AUD$"."SES$TID", "SYS"."AUD$"."LOGOFF$LREAD", "SYS"."AUD$"."LOGOFF$PREAD", "SYS"."AUD$"."LOGOFF$LWRITE", "SYS"."AUD$"."LOGOFF$DEAD", COALESCE(TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss'),' ') as LOGOFF$TIME, "SYS"."AUD$"."COMMENT$TEXT", "SYS"."AUD$"."CLIENTID", "SYS"."AUD$"."SPARE1", "SYS"."AUD$"."SPARE2", "SYS"."AUD$"."OBJ$LABEL", "SYS"."AUD$"."SES$LABEL", "SYS"."AUD$"."PRIV$USED", "SYS"."AUD$"."SESSIONCPU",
 dbms_lob.substr("SYS"."AUD$"."SQLBIND", 4000, 1) as SQL_BIND, dbms_lob.substr("SYS"."AUD$"."SQLTEXT", 4000, 1) as SQL_TEXT
 FROM "SYS"."AUD$", "SYS"."V_$INSTANCE" WHERE (cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) > to_date('2021-11-07 06:39:08','yyyy-mm-dd hh24:mi:ss') AND SYSDATE-(2/(3600*24)) > cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) AND "SYS"."AUD$"."ACTION#" NOT IN (101,102)) OR ("SYS"."AUD$"."LOGOFF$TIME" > to_date('2021-11-07 06:39:08','yyyy-mm-dd hh24:mi:ss') AND "SYS"."AUD$"."ACTION#" IN (101,102) AND SYSDATE-(2/(3600*24)) > "SYS"."AUD$"."LOGOFF$TIME" )
 ORDER BY 11 ASC";
 Reason: state: 57; error-code: 140428250710920; description: [RSA][ODBC 20101 driver][Oracle]ORA-00904: : invalid identifier

The oracle_10g_auditing.xml and oracle_11g_auditing.xml files replaced the default files in the NetWitness Log Collector /etc/netwitness/ng/logcollection/content/collection/odbc/ directory.

 

Cause

As the only change in the revised oracle_10g_auditing.xml and oracle_11g_auditing.xml files is to use the dbms_lob.substr() function, it is likely the Oracle login that NetWitness uses does not have execute permission on the sys.dbms_lob package (although the privilege is granted to PUBLIC by default).

NetWitness uses the dbms_lob.substr() function to retrieve long (>4,000 characters) CLOB fields.

The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulation-specific parts of a LOB or complete LOBs. The bulk of the LOB operations are provided by this package. This package must be created under SYS. Operations provided by this package are performed under the current calling user, not under the package owner SYS.

Reference: Oracle DBMS_LOB

Resolution

Ask the Oracle DBA to run the following SQL with sys to check whether the login has the privilege to execute the DBMS_LOB package.

select * from dba_tab_privs where table_name='DBMS_LOB';

By default, you should see PUBLIC in the grantees.

If not, you can run the following SQL with sys to grant execute permission.

 grant execute on sys.DBMS_LOB to public;

This returns the permission back to the default setting.  Also confirm the Oracle login that NetWitness is using is included in the public group.

Or if access to sys.DBMS_LOB needs to be more restricted, then grant execute permission to the Oracle login that NetWitness is using.

 grant execute on sys.DBMS_LOB to [user];

Where [user] is the Oracle login that NetWitness uses.

If this doesn't resolve the issue have the Oracle DBA investigate if any of the following could be the cause.

1. There is no synonym dbms_lob for sys.dbms_lob in the database (although this public synonym should exist).
2. The sys.dbms_lob package is not installed.
3. The schema contains some other package with the same name DBMS_LOB.