Wednesday, February 7, 2024

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


Friday, January 5, 2024

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