Column Level Audit in Oracle 23ai

Oracle Database support auditing at column level and Audit actions at column level introduced in Oracle 23ai Database. Using this feature, you can Audit column level for tables and views.

Before Oracle 21c and before you can audit at table level. Oracle deprecated traditional auditing in Oracle 21c, and it is desupported from Oracle 23ai.

In Oracle 23ai user can perform audit actions at individual column level by creating audit policies. You can audit action on a Tableau or view column using “CREATE AUDIT POLICY” Statement

For example,

• Audit all actions on a table

    SQL> CREATE AUDIT POLICY aud_all_act_emp ACTIONS ALL ON SCOTT.EMP;

When using ACTIONS ALL user should be cautious. Because enabling on an online transaction processing (OLTP) workload. Will lead large number of audit records.

• Audit action on a column

    SQL> CREATE AUDIT POLICY aud_col_bon_act_emp ACTIONS SELECT (BONOUS) ON                SCOTT.EMP;

Users can query UNIFIED_AUDIT_TRAIL dictionary view to view audit events information

SQL> SELECT OBJECT_NAME,SQL_TEXT FROM UNIFIED_AUDIT_TRAIL WHERE OBJECT_NAME = 'EMP';

OBJECT_NAME     SQL_TEXT
------------------------ ----------------------------------------------------------------
EMP                         SELECT BONUS FROM EMP WHERE EMPNO=123

You can also audit actions on SYS objects using CREATED AUDIT POLICTY statement.

SQL> CREATE AUDIT POLICY aud_col_sys_obje ACTIONS SELECT ON SYS.TABLES;

To audit recursive actions, use ONLY TOPLEVEL clause in the CREATE AUDIT POLICY statement.

User can audit PL/SQL packages, functions, procedures and triggers, pls consider below points

• You can audit standalone PL/SQL packages, functions and procedures 
• Auditing a PL/SQL package will audit all functions and procedures within the package. You cannot audit individual producers and functions within the PL/SQL package. 
• Auditing all executions will enable auditing all triggers, functions and procedures within PL/SQL packages.
• Auditing EXECUTE operation on a PL/SQL stored procedure or function, will determine the success or failure of the operation auditing purpose.

For a list of Object-level Database action audit options, you can refer Oracle documentation here

Thanks & Regards,

https://oracleracexpert.com

Webinar: Oracle Database 23c New Security features

This Webinar helps you to understand Oracle Database 23c new Security features and make use of these cutting edge functionalities.

Date and time: June 14th 2024, 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• SQL Firewall
• Audit
• Authentication
• Authorization
• Encryption
• Autonomous Database
• Other

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com
You will receive an email confirmation with meeting link or Webinar link will be posted here.

Note that registrations are limited and first come and first serve basis.

For Zoom Link Click here
For presentation link Click here

Thanks & Regards,
http://www.oracleracexpert.com

The listen port 8080 is already in use by another process using ORDS

I have come across below error when installing ORDS for APEX using 23.x+ version. When you try to bring up the ORDS you may receive the same warning message

/oracle:DBA>ords --config /oracle/ords_conf install

ORDS: Release 23.4 Production on Tue Feb 27 05:42:02 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/oracle/ords_conf/

The configuration folder /oracle/ords_conf does not contain any configuration files.

Oracle REST Data Services - Interactive Install

Enter a number to select the type of installation
[1] Install or upgrade ORDS in the database only
[2] Create or update a database pool and install/upgrade ORDS in the database
[3] Create or update a database pool only
Choose [2]:
Enter a number to select the database connection type to use
[1] Basic (host name, port, service name)
[2] TNS (TNS alias, TNS directory)
[3] Custom database URL
Choose [1]:
Enter the database host name [localhost]: orasrv1
Enter the database listen port [1521]:
Enter the database service name [DBA]: ADB
Provide database user name with administrator privileges.
Enter the administrator username: SYS
Enter the database password for SYS AS SYSDBA:
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//orasrv1:1521/ADB

Retrieving information.
Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]: ORDS_DATA

Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]: TEMP

Enter a number to select additional feature(s) to enable:

[1] Database Actions (Enables all features)
[2] REST Enabled SQL and Database API
[3] REST Enabled SQL
[4] Database API
[5] None
Choose [1]:

Enter a number to configure and start ORDS in standalone mode
[1] Configure and start ORDS in standalone mode
[2] Skip
Choose [1]:

Enter a number to select the protocol
[1] HTTP
[2] HTTPS
Choose [1]: 2
Enter the HTTPS port [8443]:
Enter a number to select the certificate type
[1] Use self-signed certificate (generates automatically)
[2] Use my SSL certificate (requires SSL certificate and SSL certificate private key)
Choose [1]:
Enter the SSL hostname: orasrv1
Enter the APEX static resources location: /oracle/apex/images
The setting named: db.connectionType was set to: basic in configuration: default
The setting named: db.hostname was set to: orasrv1 in configuration: default
The setting named: db.port was set to: 1521 in configuration: default
The setting named: db.servicename was set to: ADB in configuration: default
The setting named: plsql.gateway.mode was set to: proxied in configuration: default
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: default
The setting named: db.password was set to: ****** in configuration: default
The setting named: feature.sdw was set to: true in configuration: default
The global setting named: database.api.enabled was set to: true
The setting named: restEnabledSql.active was set to: true in configuration: default
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in onfiguration: default
The global setting named: standalone.https.port was set to: 8443
The global setting named: standalone.https.host was set to: orasrv1
The global setting named: standalone.static.path was set to: /oracle/apex/images/
The global setting named: standalone.static.context.path was set to: /i
The global setting named: standalone.context.path was set to: /ords
The global setting named: standalone.doc.root was set to: /oracle/ords_conf/global/doc_root
2024-02-22T03:00:32.213Z INFO Created folder /oracle/ords/logs
2024-02-22T03:00:32.214Z INFO The log file is defaulted to the current working directory located at /oracle/ords/logs
2024-02-22T03:00:32.432Z INFO Installing Oracle REST Data Services version 23.4.0.r3461619 in NON_CDB
2024-02-22T03:00:33.913Z INFO ... Verified database prerequisites
2024-02-22T03:00:34.311Z INFO ... Created Oracle REST Data Services proxy user
2024-02-22T03:00:34.912Z INFO ... Created Oracle REST Data Services schema
2024-02-22T03:00:35.474Z INFO ... Granted privileges to Oracle REST Data Services
2024-02-22T03:00:39.099Z INFO ... Created Oracle REST Data Services database objects
2024-02-22T03:00:50.174Z INFO Completed installation for Oracle REST Data Services version 23.4.0.r3461619. Elapsed time: 00:00:17.662
2024-02-22T03:00:50.244Z INFO Completed configuring PL/SQL gateway user for Oracle REST Data Services version 23.4.0.r3461619. Elapsed time: 00:00:00.66
2024-02-22T03:00:50.245Z INFO Log file written to /oracle/ords/logs/ords_install_2024-02-22_060032_21483.log
2024-02-22T03:00:50.725Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2024-02-22T03:00:50.726Z INFO HTTPS and HTTPS/2 listening on host: 0.0.0.0 port: 8443
2024-02-22T03:00:50.746Z INFO Disabling document root because the specified folder does not exist: /oracle/ords_conf/global/doc_root
2024-02-22T03:00:50.746Z INFO Default forwarding from / to contextRoot configured.
2024-02-22T03:00:50.803Z SEVERE Could not start Standalone Mode because the listen port: 8080 is already in use by another process. Check if another instance of ords is already running/oracle:DBA>
When
2024-02-22T03:00:50.245Z INFO Log file written to /oracle/ords/logs/ords_install_2024-02-22_060032_21483.log
2024-02-22T03:00:50.725Z INFO HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2024-02-22T03:00:50.726Z INFO HTTPS and HTTPS/2 listening on host: 0.0.0.0 port: 8443
2024-02-22T03:00:50.746Z INFO Disabling document root because the specified folder does not exist: /oracle/ords_conf/global/doc_root
2024-02-22T03:00:50.746Z INFO Default forwarding from / to contextRoot configured.
2024-02-22T03:00:50.803Z SEVERE Could not start Standalone Mode because the listen port: 8080 is already in use by another process. Check if another instance of ords is already running

/oracle:DBA>ords --config /oracle/ords_conf serve

The main reason for the error is the port 8080 already in use by another processes.

The ports 80 and 8080 are common ports for Web servers (HTTP) to use. First check is ORDS is already running by checking the processes.

$ps -ef |grep ords

If you don’t see any processes, then there might be another processes using the 8080 port and run below command to check the process

$ netstat -lnptu |grep 8080
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN

If you see any processes, you have few options,

1. Check is it safe to kill the application that is using port 8080, unless you know that is this application or processes I don’t recommend this
2. Change the ORDS port to any another port such as 8088

First run the below command to check any value set for

$ords config get standalone.http.port

Cannot get setting standalone.http.port because the setting is not found in database pool default located at /oracle/ords_conf/databases/default

As you see ORDS setting “standalone.http.port” doesn’t have any value set in the config. So we are safe to assign another port

$ords config set standalone.http.port 8081

Now you can try to bring up the ORDS using below command

ords --config /oracle/ords_conf serve

When ORDS processes comes online you should see below message

apped local pools from /oracle/ords_conf/databases:
/ords/ => default => VALID

2024-02-22T07:04:22.648Z INFO Oracle REST Data Services initialized

Warning When installing ORDS 23.x

When trying to install ORDS I got below message. You will see this message as Oracle recommends using a different configuration folder outside of ORDS.

2024-02-27T01:14:10.925Z INFO        Your configuration folder /oracle/ords is located in ORDS product folder.  Oracle recommends to use a different configuration folder

I have created a folder and resuming the ORDS install and received below warning.

ADB>java -jar ords.war install advanced
Warning: Support for executing: java -jar ords.war has been deprecated.
Please add ords to your PATH and use the ords command instead.
Run the following command to add ords to your PATH:
echo -e 'export PATH="$PATH:/oracle/ords/bin"' >> ~/.bash_profile

Start a new shell to pick up this change.

2024-02-27T01:14:10.925Z INFO Your configuration folder /oracle/ords is located in ORDS product folder. Oracle recommends to use a different configuration folder.


ORDS: Release 23.4 Production on Mon Feb 26 17:14:10 2024
Copyright (c) 2010, 2024, Oracle.

Configuration:
/oracle/ords/

Unknown command: ords install [advanced]

I realized that from ORDS 23.x+ the commands are changed using “java -jar ords.war” has been deprecated.

I would already suggest following Oracle documentation so that you have latest commands and updates.

https://docs.oracle.com/en/database/oracle/apex/index.html

I have come across an issue when using Oracle Java version lower than 11 and the installation fails with an error message.

Error: ORDS requires Java 11 and above to run.
Found Java version 1.

Please set JAVA_HOME to appropriate version and update PATH if necessary.

Make sure you are using Oracle Java version 11, or 17

Thanks

https://oracleracexpert.com

Bugs in Oracle Apex 23.2 and ORDS 23.4

I have recently installed Apex 23.2, ORDS 23.4 for POC purpose and come across below issues when working with APEX and ORDS.

Bug: Oracle Apex 23.2 with Interactive Grid

When working with Interactive Grid, the filters saved in the Interactive Grid not displaying correct values. The issue happens intermittently, and filter values change from date to string or filter operators may drop out of the list.

When users working with operators they see inconsistency in the values, which is a big concern.

After research I found that its known issue ang filed bug #36200437

For more detailed info please check below Oracle forum link

BUG in APEX 23.2 Interactive Grid: Multiple Criteria Filter Issue - Oracle Forums

Bug: ORDS 23.4 with PL/SQL

Please see the cause and action for error PLS-00306: wrong number or types of arguments in call to 'string”

Cause: 
This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.

Action: 
Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

But in my case when working with PL/SQL code users receiving “PLS-00306: wrong number or types of arguments in call”. The same code works after some time, after research found that it’s a known bug.

For more details, please see the below Oracle forums link.

ORDS 23.4 with PL/SQL. PLS-00306: wrong number or types of arguments in call to - Oracle Forums

Hope this helps.

Thanks & Regards.

Satishbabu Gunukula, Oracle ACE Pro

https://oracleracexpert.com

SQL Firewall in Oracle 23c

SQL Firewall inspects all incoming statements and ensures only authorized SQL is run and it is embedded in the Oracle Database and unauthorized SQL Statements will be logged and blocked.

SQL firewall provides real-time protection from attacks and mitigate risks from SQL injection attacks, anomalous access, credential abuse or theft. SQL Firewall supports all commands except transaction control commands such as SAVEPOINT, COMMIT, ROLLBACK.

To administer SQL Firewall user must have SQL_FIREWALL_ADMIN role. To query DBA_SQL_FIREWALL* data dictionary the user must have SQL_FIREWALL_VIEWER role

You can Configure SQL Firewall using DBMS_SQL_FIREWALL package or Oracle Data Safe. SQL Firewall can be used in both root and Pluggable Database (PDB)


You can enable SQL Firewall using below command.
SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;

Create and enable SQL Firewall capture for a user using below command

SQL> BEGIN
DBMS_SQL_FIREWALL.CAPTURE_CAPTURE (
Username => ‘SCOTT’
top_level_only => TRUE,
Start_capture => TRUE
);
END;

Enable SQL Firewall Allow List

SQL>BEGIN
DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
username => ‘SCOTT’,
enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL,
block => TRUE );
END;

You can use below commands to START and STOP capture.

SQL> EXEC DBMS_SQL_FIREWALL.START_CAPTURE (‘SCOTT’);
SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE (‘SCOTT’);

You can generate an allow list using below procedures.

DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT
DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT
DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL

The SQL Firewall may generate large volume of capture logs and to minimize performance impact the database memory needs to be sized to handle the load. It is advised to add additional 2GB to LARGE_POOL_SIZE parameter and also it advised to have SGA_TARGET to 8GB or more.

To purge logs you can use below procedure

BEGIN
DBMS_SQL_FIREWALL.PURGE_LOG (
username => ‘SCOTT’,
purge_time => '2024-01-10 12:00:00.00 -08:00',
log_type => 'DBMS_SQL_FIREWALL.ALL_LOGS'
);
END;
/

You can also enable and disable SQL Firewall Trace using below commands. The trace level value should be LOW, HIGH, HIGHEST based upon how much detail tracing you want to have.

-Session level Tracing Enable and Disable
ALTER SESSION SET EVENTS 'TRACE SQL_FIREWALL DISK=trace_level
ALTER SESSION SET EVENTS 'TRACE SQL_FIREWALL OFF

-System Level Tracing Enable and Disable
ALTER SYSTEM SET EVENTS 'TRACE SQL_FIREWALL DISK=trace_level
ALTER SYSTEM SET EVENTS 'TRACE SQL_FIREWALL OFF

You can query below Data Dictionary Views for SQL Firewall protections
DBA_SQL_FIREWALL_ALLOWED_SQL - View shows allowed SQL and Accessed objects
DBA_SQL_FIREWALL_ALLOWED_IP_ADDR – View shows the Users allowed IP address
DBA_SQL_FIREWALL_CAPTURE_LOGS – View shows the Capture log entries
DBA_SQL_FIREWALL_VIOLATIONS – View shows the SQL Firewall Violations

Thanks & Regards

https://oracleracexpert.com

Oracle ACE Pro

Oracle Agile Application or Quick Search performance issue and how to resolve?

When you come across any performance issue on agile first, you need to verify schema integrity by running agile9_check.sql.

The agile9_check.sql it verifies Database Schema version with Agile Schema version, and it MUST match. Also, it validates schema objects against Agile schema version and report any errors or warnings.

You may see ERRORS/WARNINGS
ERROR: Missing or INVALID index (columns) XXXXXXXXX on table XXXXX.
ERROR: Missing or INVALID FTS CTX indexes XXXXXXX.
WARNING: ACTIVITY_BASELINE_XXXXX does not belong to AGILE DB. Please Drop the Table if not required.

In case of any Errors/Warnings you need we need to fix the issue. If you have created any custom objects you will see WARNING that object doesn't  below to Agile DB and you can ignore. 

Once user fix the schema integrity download listFTSInfo.sql and run the script as Agile user and it will generate listFTSInfo_xxxxxxxxxx.log file. You need to search for "029", where it says "List Index Percent of Fragmentation" where you can see the indexes that has fragmentation.

If you see any fragmentation, it will effect Quick search and stuck threads will be generated.

<[STUCK] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "1,247" seconds working on the request "Http Request Information: weblogic.servlet.internal.ServletRequestImpl@2f2fb94[GET /Agile/PCMServlet]
", which is more than the configured time (StuckThreadMaxTime) of "1,200" seconds in "server-failure-trigger". Stack trace:
java.net.SocketInputStream.socketRead0(Native Method)

To resolve the issue, Log into sqlplus as agile database user, and run agile_ctx_recreate.sql found under %Oracle_base%\admin\{SID}\create\{agile_schema_username} to rebuild CTX indexes.

Oracle support recommends stopping Agile application, run agile_ctx_recreate.sql and Start the Application.

It is highly advisable to run complie_Invalid_objects.sql, agile9stats.sql to recompile invalid objects and collect stats to improve the performance.

Refer Oracle Agile support doc for Full Text search (FTS) Enablement, synchronization, Indexing and optimization.

Frequently Asked Questions on Agile Product Lifecycle Management (PLM) Full Text Search (FTS) Enablement, Synchronization, Indexing, and Optimization (Doc ID 1503311.1)

For Agile Stuck thread issue, refer below Oracle support Doc ID

Agile Managed Servers Goes to Warning State, Having Stuck Thread at com.agile.cs.query.QuerySessionBean (Doc ID 2919154.1)

Thanks & Regards

https://oracleracexpert.com, Oracle ACE