Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Implement AI-Powered User Behavior Analytics with Oracle Database 23ai, Oracle Data Safe and Oracle APEX

Introduction

As database environments grow more complex, securing sensitive data and detecting anomalous user behavior is crucial. This project leverages Oracle Data Safe logs, Oracle Machine Learning for SQL (OML4SQL), and Oracle Database 23ai to enhance User Behavior Analytics (UBA), identifying unusual access patterns and potential security threats.

This step-by-step tutorial demonstrates how to implement AI-powered anomaly detection using Oracle Machine Learning (OML) and Oracle APEX, enabling real-time security monitoring. Designed for Database Security Admins, AutoML Admins, AI/ML Admins, and APEX Developers, this solution strengthens database security by integrating Oracle’s AI/ML capabilities for proactive threat detection.

At its core, the project utilizes One Class Support Vector Machines (SVMs), an unsupervised machine learning algorithm that learns normal user behavior and flags deviations as anomalies. By analyzing features like total logins and average session duration, the model identifies irregular activity, helping to detect potential security threats and unauthorized access attempts.

Use Case: Database Users Anomaly Detection.

This use case focuses on detecting anomalous user login and logout activities in an Oracle database environment by analyzing logon and logoff events. The system identifies suspicious patterns such as unusual session durations or irregular login frequencies and visualizes insights through an Oracle APEX dashboard, enabling administrators to proactively secure the database.

By leveraging AI and security analytics, this approach flags abnormal logon and logoff patterns, helping mitigate potential security threats.

Key Highlights:

• AI-Driven Detection: Machine learning models identify deviations from normal user behaviour.
• Real-Time Monitoring: Oracle APEX dashboards provide continuous security insights.
• Proactive Risk Mitigation: Early anomaly detection strengthens database security and reduces potential risks.

Audience

• Database administrators (DBAs) managing Oracle databases.

• OCI security administrators ensuring database security compliance.

• Security analysts leveraging Oracle security tools for threat detection.

Objectives

Implement AI-powered UBA in an OCI environment by integrating:

• Oracle Database 23ai for data processing.

• Oracle Data Safe for security auditing and log analysis.

• OML4SQL for AI-driven anomaly detection.

• Oracle APEX for real-time visualization.

The primary objective is to identify anomalous user behavior by analyzing Oracle Data Safe logon event datasets, enabling proactive threat detection and enhanced database security.

Prerequisites

• Access to an Oracle Database 23ai with Data Warehouse instance as a Workload Type.

  

• Oracle Data Safe configured and registered with target databases.

  

• Basic knowledge of SQL, Machine Learning (ML), and Oracle APEX.

Task 1: Download Logon and Logoff Event Dataset from Oracle Data Safe

Generate and download a filtered report of Logon and Logoff events from Oracle Data Safe, then convert it to CSV for database import.

1. Log in to the OCI Console, navigate to Oracle Databases, Data Safe, Activity Auditing, Audit Reports and click All Activity.

2. Apply filters to capture Logon and Logoff events.

   

3. Generate the report and download it in .xls format. For example, Datasafe_LOGIN_DatasetJan2024-Feb2025_V2.xls.

4. Rename the file to .csv format for loading into the Oracle Autonomous Database. For example, Datasafe_LOGIN_DatasetJan2024-Feb2025_V2.csv.

Task 2: Create a User in Oracle Database 23ai Autonomous Database

Create a user named OML_USER with necessary OML and web access in Oracle Database 23ai.

1. Go to the OCI Console, navigate to Oracle Databases and click Autonomous Database.

2. Click Database Actions and Database Users.

   

3. Create a user named OML_USER with OML and Web Access.

   

   

Task 3: Load Dataset into Oracle Database 23ai Autonomous Database

Use Data Load to upload the CSV dataset, verify data integrity, and ensure proper loading.

1. Log in as OML_USER using the web access link.

   

2. Navigate to Data Studio and click Data Load.

   

3. Remove the comment section from the CSV file and ensure it starts with column headers.

   

4. Click Load Data, select the CSV file, and configure the table name.

   

   

   

   

5. Click Start and Run to load the data.

   

6. Verify the data load status and fix any rejected rows.

   

Task 4: Validate and Clean the Data

Process the dataset using SQL procedures to remove inconsistencies and prepare data for analysis.

1. Navigate to Development and click SQL as OML_USER.

   

2. Create a staging table (datasafe_audit_logs) and process the data using a SQL procedure.

   • Insert records from DATASAFE_LOGIN_RAW_DATASET into datasafe_audit_logs.
   • Delete LOGOFF records that have no corresponding LOGON records.
   • Delete LOGON records if no LOGOFF event exists with a later timestamp.

   CREATE TABLE datasafe_audit_logs (
       target VARCHAR2(100),
       db_user VARCHAR2(100),
       client_host VARCHAR2(100),
       event VARCHAR2(50),
       object VARCHAR2(100),
       operation_status VARCHAR2(50),
       operation_time TIMESTAMP
   );
   

   Run the procedure to clean the dataset.

    CREATE OR REPLACE PROCEDURE process_datasafe_audit_logs AS
   BEGIN
       -- Step 1: Insert data from raw dataset to audit logs
       INSERT INTO datasafe_audit_logs (
           TARGET,
           DB_USER,
           CLIENT_HOST,
           EVENT,
           OBJECT,
           OPERATION_STATUS,
           OPERATION_TIME
       )
       SELECT
           TARGET,
           DB_USER,
           CLIENT_HOST,
           EVENT,
           OBJECT,
           OPERATION_STATUS,
           CAST(OPERATION_TIME AS TIMESTAMP(6)) -- Convert DATE to TIMESTAMP(6)
       FROM DATASAFE_LOGIN_RAW_DATASET
       WHERE OPERATION_STATUS = 'SUCCESS';
   
       -- Commit the insertion
       COMMIT;
   
       -- Step 2: Delete LOGOFF events that do not have a corresponding LOGON event
       DELETE FROM datasafe_audit_logs a
       WHERE a.EVENT = 'LOGOFF'
       AND NOT EXISTS (
           SELECT 1
           FROM datasafe_audit_logs b
           WHERE a.TARGET = b.TARGET
           AND a.DB_USER = b.DB_USER
           AND a.CLIENT_HOST = b.CLIENT_HOST
           AND b.EVENT = 'LOGON'
       );
   
       -- Step 3: Delete LOGON events that do not have a corresponding LOGOFF event
       -- with an OPERATION_TIME that is greater than or equal to the LOGON event.
       DELETE FROM datasafe_audit_logs a
       WHERE a.EVENT = 'LOGON'
       AND NOT EXISTS (
           SELECT 1
           FROM datasafe_audit_logs b
           WHERE a.TARGET = b.TARGET
           AND a.DB_USER = b.DB_USER
           AND a.CLIENT_HOST = b.CLIENT_HOST
           AND b.EVENT = 'LOGOFF'
           AND b.OPERATION_TIME >= a.OPERATION_TIME  -- Ensure LOGOFF happened after or at the same time
       );
   
       -- Commit the deletions
       COMMIT;
   END process_datasafe_audit_logs;
   /
   
   EXEC process_datasafe_audit_logs;
   

   

Task 5: Create an Oracle Machine Learning Notebook

Set up an OML notebook under OML_USER to perform anomaly detection and user behavior analysis.

1. Navigate to Development and click Oracle Machine Learning.

   

2. Log in as OML_USER and create a new notebook.

   

   

   

Task 6: Analyze Database User Anomalies with OML4SQL

Analyze database user anomalies by calculating session durations and detecting unusual patterns using SQL and OML4SQL.

1. Move the cursor to the bottom of the paragraph to select SQL for OML4SQL and other components like OML4Py, OML4R, and so on. In this tutorial, we will be using OML4SQL.

   

2. Add the title for the paragraph.

   

3. Create a view (user_session_data) to capture session data.

   CREATE OR REPLACE VIEW user_session_data AS
   WITH session_data AS (
       SELECT
           target,
           db_user,
           client_host,
           operation_time AS logon_time,
           LEAD(operation_time) OVER (
               PARTITION BY target, db_user, client_host
               ORDER BY operation_time
           ) AS logoff_time
       FROM datasafe_audit_logs
       WHERE event = 'LOGON'
   )
   SELECT * FROM session_data WHERE logoff_time IS NOT NULL;
   

   

4. Calculate session durations and create a summary view (user_activity_summary). Add one more SQL paragraph and place the query and click the run icon.

   CREATE OR REPLACE VIEW user_activity_summary AS
   SELECT
       target,
       db_user,
       client_host,
       COUNT(*) AS total_logins,
       ROUND(
           AVG(
               EXTRACT(DAY FROM (logoff_time - logon_time)) * 24 * 60 +
               EXTRACT(HOUR FROM (logoff_time - logon_time)) * 60 +
               EXTRACT(MINUTE FROM (logoff_time - logon_time)) +
               EXTRACT(SECOND FROM (logoff_time - logon_time)) / 60
           ), 2
       ) AS avg_session_duration_minutes
   FROM user_session_data
   GROUP BY target, db_user, client_host;
   

   

Task 7: Build an Anomaly Detection Model

Use One-Class SVM in OML4SQL to create an AI-based anomaly detection model per target database.

1. Move the cursor to the bottom of the paragraph to select script for OML4SQL.

   

2. Create a settings table (anomaly_model_settings) for One-Class SVM.

   %script
   CREATE TABLE anomaly_model_settings (
   setting_name  VARCHAR2(30),
   setting_value VARCHAR2(4000)
   );
   
   -- Insert settings for One-Class SVM
   BEGIN
      INSERT INTO anomaly_model_settings (setting_name, setting_value) VALUES ('ALGO_NAME', 'ALGO_SUPPORT_VECTOR_MACHINES');
      INSERT INTO anomaly_model_settings (setting_name, setting_value) VALUES ('SVMS_OUTLIER_RATE', '0.05');  -- 5% of data as anomalies
      COMMIT;
   END;
   

   

3. Build an anomaly detection model for each target database using DBMS_DATA_MINING.CREATE_MODEL.

   %script
   DECLARE
       CURSOR target_cursor IS
           SELECT DISTINCT target
           FROM user_activity_summary;
   BEGIN
       FOR target_rec IN target_cursor LOOP
           DBMS_DATA_MINING.CREATE_MODEL(
               model_name          => 'ANOMALY_DETECTION_MODEL_' || REPLACE(target_rec.target, ' ', '_'),
               mining_function     => dbms_data_mining.classification,
               data_table_name     => 'USER_ACTIVITY_SUMMARY',
               case_id_column_name => 'DB_USER',
               target_column_name  => NULL,  -- No target column for anomaly detection
               settings_table_name => 'ANOMALY_MODEL_SETTINGS'
           );
       END LOOP;
   END;
   

   

4. Verify the model creation.

   %sql
   SELECT model_name, mining_function, algorithm, creation_date FROM user_mining_models where model_name like 'ANOMALY_DETECTION_MODEL_%';
   

   

Task 8: Apply the Model to Detect Anomalies

Run anomaly predictions to classify suspicious user activity and store results in a structured table.

1. Create tables to store anomalous users for each target database. Move the cursor to the bottom of the paragraph to select script for OML4SQL.

   .%script
   DECLARE
      CURSOR target_cursor IS
          SELECT DISTINCT target
          FROM user_activity_summary;
   BEGIN
      FOR target_rec IN target_cursor LOOP
          EXECUTE IMMEDIATE '
              CREATE TABLE anomalous_users_' || REPLACE(target_rec.target, ' ', '_') || ' AS
              SELECT
                  target,
                  db_user,
                  client_host,
                  total_logins,
                  avg_session_duration_minutes
              FROM (
                  SELECT
                      target,
                      db_user,
                      client_host,
                      total_logins,
                      avg_session_duration_minutes,
                      PREDICTION(ANOMALY_DETECTION_MODEL_' || REPLACE(target_rec.target, ' ', '_') || ' USING *) AS prediction
                  FROM user_activity_summary
                  WHERE target = ''' || target_rec.target || '''
              )
              WHERE prediction = 0';  -- Filter for anomalous users
      END LOOP;
   END;
   

   

2. Review the anomalous user data.

   %sql
   SELECT * FROM ANOMALOUS_USERS_ADBIDCS3
   UNION ALL
   SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB
   UNION ALL
   SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB2;
   

   

Task 9: Visualize Anomalies in Oracle APEX

Develop an interactive dashboard with charts to monitor and analyze anomalous database activity in real time.

1. Launch Oracle APEX from the OCI Console.

   

   

2. Log in as OML_USER and create a new application.

   

   

3. Build a dashboard with four charts.

   

   

   • Chart 1: Suspicious User Table (Classic Report).

     SQL : %sql
     SELECT * FROM (
         SELECT * FROM ANOMALOUS_USERS_ADBIDCS3
         UNION ALL
         SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB
         UNION ALL
         SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB2
     )
     ORDER BY avg_session_duration_minutes DESC;
     

     

   • Chart 2: Anomalous Users per Database (Pie Chart).

     SQL : %sql
     SELECT TARGET, COUNT(DB_USER) AS anomaly_count
     FROM (
         SELECT * FROM ANOMALOUS_USERS_ADBIDCS3
         UNION ALL
         SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB
         UNION ALL
         SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB2
     )
     GROUP BY TARGET;
     

     

     

   • Chart 3: Session Duration Trends (Line Chart).

     SELECT
     DB_USER,
     (AVG(AVG_SESSION_DURATION_MINUTES) / 60) AS session_duration_hours,
     TARGET
     FROM (
     SELECT * FROM ANOMALOUS_USERS_ADBIDCS3
     UNION ALL
     SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB
     UNION ALL
     SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB2
     )
     GROUP BY DB_USER, TARGET;
     

     

     

   • Chart 4: Top Users with the Most Anomalies (Bar Chart).

     SQL : %sql
     SELECT
         DB_USER AS x_value,  -- Ensure DB_USER is used as the category (X-Axis)
         COUNT(*) AS y_value,  -- Anomaly count or other metric
         TARGET
     FROM (
         SELECT * FROM ANOMALOUS_USERS_ADBIDCS3
         UNION ALL
         SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB
         UNION ALL
         SELECT * FROM ANOMALOUS_USERS_DBAAS23PDB2
     )
     GROUP BY DB_USER, TARGET
     ORDER BY y_value DESC;
     

     

     

4. Run the page and share the dashboard URL with your team.

   

   

Next Steps

This tutorial showed an AI-driven approach to database security using Oracle Database 23ai, Oracle Data Safe, and Oracle APEX. Here we,

1. Processed login and logoff data to analyze session durations.
2. Built an anomaly detection model using OML4SQL.
3. Visualized results in an Oracle APEX dashboard for real-time monitoring.

By integrating machine learning and interactive dashboards, security teams can proactively detect and mitigate suspicious activities. This scalable solution extends to fraud detection, network security, and more, leveraging Oracle’s built-in AI/ML capabilities for seamless implementation.

Related Links

• Oracle Machine Learning for SQL Release 23

• Oracle Machine Learning for SQL (OML4SQL)

• Get Started with Notebooks Classic for Data Analysis and Data Visualization

• Announcing New Oracle Machine Learning Features in Oracle Database 23ai

Acknowledgments

• Author - Alex Kovuru (Principal Cloud Architect)

• Contributor - Indiradarshni Balasundaram (Senior Cloud Engineer)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

<

Implement AI-Powered User Behavior Analytics with Oracle Database 23ai, Oracle Data Safe and Oracle APEX

G28758-01

Copyright ©2025, Oracle and/or its affiliates.