Oracle 26ai Session and SQL Command Security

Oracle AI Database 26ai does not use the old SQL*Plus Product User Profile as a supported command-control system. The PRODUCT_USER_PROFILE table, commonly called the PUP table, was deprecated in Oracle Database 18c and desupported starting with Oracle Database 19c. Its tables and administrative views are therefore no longer suitable learning objectives for a current database architecture course.

Product User Profile was a client-oriented control. A cooperating Oracle client could consult the profile and disable selected SQL or SQL*Plus commands for a user. That approach did not provide a universal database policy: another client could take a different path. Oracle recommends protecting data with database settings so that enforcement remains consistent when a user connects through an application, SQL*Plus, SQL Developer, or another database client.

There is no single one-for-one replacement. A current design begins with least-privilege grants and roles, then adds the control that matches the specific risk. Oracle SQL Firewall is the closest 26ai mechanism for learning and enforcing the expected SQL and connection contexts of a database account. Database Vault, secure application roles, Virtual Private Database, Real Application Security, Unified Auditing, profiles, and Resource Manager solve different problems. Treating them as interchangeable creates gaps.

A security design must first identify where a command is processed. Statements such as SELECT, INSERT, UPDATE, DELETE, DDL, and calls to stored program units reach the database. Oracle authorization can reject them, SQL Firewall can compare supported SQL with an allow-list, Database Vault can apply conditional rules, and auditing can record the result.

Commands such as SQL*Plus HOST, SPOOL, SAVE, GET, and START are implemented by the client. They are not ordinary SQL statements executed by the database. A server policy cannot universally disable local capabilities in every client program. SQL*Plus provides its own -RESTRICT option for a hardened invocation:

sqlplus -RESTRICT 3 app_user@pdb1

Level 1 disables EDIT and HOST. Level 2 also disables SAVE, SPOOL, and STORE. Level 3 also disables GET and START, including the associated @ and @@ script commands. Each higher level includes the earlier restrictions. These controls remain active only for that SQL*Plus process; they are not a per-user database policy.

Requirement	Primary Oracle control	Boundary
Authorize use of objects and system capabilities	Object, schema, and system privileges assigned directly or through roles	The foundation for all later controls
Permit expected SQL and approved connection contexts for an account	Oracle SQL Firewall	A maintained per-account allow-list, not a source of privileges
Apply conditions to sensitive commands or protected data	Database Vault command rules, factors, and realms	Specialized security configuration
Enable a role only after application checks	Secure application role	An authorized invoker-rights PL/SQL unit enables the role
Filter data according to session context	Virtual Private Database and fine-grained access control	Row-level data policy, not a general command blocker
Represent application identities and authorization	Real Application Security	An application policy framework, not a PUP replacement
Record and investigate security activity	Unified Auditing and fine-grained auditing	Detective and evidentiary controls
Limit session resources and idle behavior	Profiles and Database Resource Manager	Resource governance, not SQL authorization

These layers reinforce one another without replacing one another. An allow-list cannot grant a missing object privilege. An audit record does not make a permitted statement fail. VPD does not decide who may issue ALTER SYSTEM. A profile that limits idle time does not determine who may select a table. Define the requirement before choosing the feature.

Before learning an account's SQL, inventory what the account should be able to do. Separate interactive users, application service accounts, batch jobs, deployment accounts, and administrators when their responsibilities differ. Grant only the object, schema, and system privileges required by each responsibility. Application-specific roles make those privileges easier to review and revoke.

The following simple example creates a reporting role. It does not grant broad administration authority merely to make the lesson work:

CREATE ROLE hr_reporting_role;

GRANT SELECT ON hr.employees TO hr_reporting_role;
GRANT hr_reporting_role TO report_user;

Oracle authorization evaluates whether REPORT_USER may query HR.EMPLOYEES. SQL Firewall can then restrict the account to the expected statement patterns and connection contexts within that authorized boundary. If the role or object privilege is missing, an allow-list entry does not override the authorization failure.

Consider a local application account named APP_API in PDB1. The account runs a stable API workload. The goal is to learn its legitimate SQL in a controlled environment, inspect that evidence, observe deviations without interrupting the application, and enable blocking only after the policy has been validated.

A user granted the SQL_FIREWALL_ADMIN role administers the feature through DBMS_SQL_FIREWALL. The protected application account should not administer its own allow-list. Confirm the container and current state before changing anything:

SHOW CON_NAME

SELECT status,
       status_updated_on,
       exclude_jobs
FROM   dba_sql_firewall_status;

SQL Firewall can be configured in the CDB root and in individual PDBs. Connect to the container that owns the target account and confirm the intended scope. The EXCLUDE_JOBS value also matters. Oracle Scheduler job sessions are excluded from SQL Firewall capture and enforcement by default, preventing an incomplete policy from disrupting critical jobs. Document whether scheduled application work belongs inside or outside the chosen policy.

Enable capture for the application account

Enable SQL Firewall if it is disabled, then create and start one capture for APP_API:

EXEC DBMS_SQL_FIREWALL.ENABLE;

BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE(
    username       => 'APP_API',
    top_level_only => TRUE,
    start_capture  => TRUE
  );
END;
/

With TOP_LEVEL_ONLY set to TRUE, the capture focuses on SQL issued directly by the user rather than recursive SQL executed inside trusted PL/SQL units. The appropriate value depends on the application and policy objective. Do not copy the setting without deciding whether the behavior of stored program units must also be represented.

Run a representative authorized workload in a nonproduction or otherwise controlled environment. Exercise normal reads and writes, stored procedure calls, reports, batch paths, maintenance performed through the account, and the connection programs, operating-system users, and IP addresses that may be enforced. A brief happy-path test is not enough. Do not train an allow-list while the account is exposed to untrusted traffic, because successfully executed malicious or accidental SQL could enter the capture evidence.

Stop and review the capture

After the representative workload has completed, stop capture and inspect the collected statements before generating an allow-list:

EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('APP_API');

SELECT command_type,
       sql_text,
       accessed_objects,
       current_user,
       top_level,
       client_program,
       ip_address
FROM   dba_sql_firewall_capture_logs
WHERE  username = 'APP_API'
ORDER  BY session_id,
          sql_signature;

Review command types, normalized SQL, referenced objects, the effective current user, top-level status, and observed connection information. The view shows abbreviated SQL and object lists; the full values are available through DBA_SQL_FIREWALL_SQL_LOGS when deeper analysis is needed. Remove questionable activity from the training process rather than accepting every captured statement as legitimate.

SQL Firewall derives signatures from normalized statement patterns. Literal values in otherwise equivalent statements do not require a separate allow-list entry for every customer number, date, or search value. This normalization is an enforcement aid, not permission to concatenate untrusted input into SQL. Applications should continue using bind variables, validated identifiers, and narrowly scoped stored interfaces. Review the normalized text and accessed objects together so that a broad or unexpected pattern is not approved merely because one harmless execution appeared in training.

Generate and inspect the allow-list

A capture must be stopped before its first allow-list is generated:

EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('APP_API');

SELECT username,
       status,
       top_level_only,
       enforce,
       block
FROM   dba_sql_firewall_allow_lists
WHERE  username = 'APP_API';

SELECT sql_text,
       accessed_objects,
       current_user,
       top_level,
       version
FROM   dba_sql_firewall_allowed_sql
WHERE  username = 'APP_API'
ORDER  BY allowed_sql_id;

Generation converts reviewed capture evidence into permitted SQL and learned contexts. It does not automatically prove that training was complete, nor does it grant object access. Retain the capture inventory, test cases, reviewer decision, and application release associated with the policy.

Connection context requires the same care. A program name, operating-system username, or IP address can narrow an approved path, but no single value is a complete identity proof. Combine context enforcement with database authentication, network controls, credential protection, and application authorization. When infrastructure changes, update the approved context through change control rather than weakening enforcement with an unrestricted wildcard.

Observe violations before blocking

Enable SQL enforcement with blocking disabled. Unexpected SQL continues to run if ordinary authorization permits it, but SQL Firewall records the violation:

BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
    username => 'APP_API',
    enforce  => DBMS_SQL_FIREWALL.ENFORCE_SQL,
    block    => FALSE
  );
END;
/

SELECT sql_text,
       firewall_action,
       ip_address,
       cause,
       occurred_at
FROM   dba_sql_firewall_violations
WHERE  username = 'APP_API'
ORDER  BY occurred_at;

Classify each event. It may reveal unauthorized activity, an omitted test, an ORM-generated variant, a new report, or a task that should use a separate account. SQL Firewall writes a violation record whether blocking is on or off. Observe-only enforcement therefore provides evidence without making the first incomplete policy an application outage.

Enable blocking after validation

After representative testing, violation review, change approval, and a rollback plan, switch the allow-list to blocking mode:

BEGIN
  DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT(
    username => 'APP_API',
    enforce  => DBMS_SQL_FIREWALL.ENFORCE_SQL,
    block    => TRUE
  );
END;
/

Blocked unexpected SQL can return ORA-47605. When connection contexts are enforced, a disallowed context can prevent the connection. The example enforces SQL only; an organization may deliberately select context or combined enforcement after reviewing approved programs, operating- system users, and IP addresses.

An allow-list is a maintained security policy. Application releases, ORM changes, new reports, batch jobs, maintenance operations, and connection- path changes may require controlled updates. Oracle 26ai can append reviewed capture or violation records to an existing allow-list, including a single selected SQL record, but administrators must not approve a violation simply because it came from a familiar account.

SQL Firewall evaluates supported SQL that reaches the database and can enforce approved connection contexts such as IP address, operating-system user, and operating-system program. It is designed for stable application workloads and can mitigate SQL injection, anomalous access, and misuse of application credentials. It does not replace bind variables, input validation, patching, network controls, or ordinary authorization.

Transaction-control operations such as SAVEPOINT, COMMIT, and ROLLBACK are outside its SQL allow-list control. SQL*Plus operations such as PASSWORD and DESCRIBE generate database-visible work, but purely client-side commands such as HOST and SPOOL still require client or operating-environment controls. Capture records successfully executed SQL; failed unauthorized attempts do not become learned allowed statements.

Secure application roles

A secure application role is useful when an application should receive a set of privileges only after validated checks. Create the role with IDENTIFIED USING and associate it with an authorized PL/SQL package or procedure:

CREATE ROLE hr_reporting_role
  IDENTIFIED USING security_admin.hr_role_guard;

GRANT SELECT ON hr.employees TO hr_reporting_role;
GRANT EXECUTE ON security_admin.hr_role_guard TO app_user;

Do not grant the secure role directly to APP_USER. The controlling unit must use invoker's rights with AUTHID CURRENT_USER, perform its security checks, and issue dynamic SET ROLE or call DBMS_SESSION.SET_ROLE only after those checks succeed. The application invokes the unit before it needs the protected privileges. A logon trigger cannot enable or disable this type of role.

Database Vault

Database Vault command rules can control sensitive SELECT, DDL, DML, and administrative statements according to rule sets and factors. Realms can protect application data even from accounts with broad system privileges. A suitable scenario is permitting production DDL only during an approved maintenance window and through an approved administrative path.

Database Vault is not an ordinary role grant or a short trigger. Its realms, command rules, rule sets, factors, responsible accounts, licensing, operational testing, and emergency procedures require deliberate design. Confirm the environment before selecting it.

VPD and Real Application Security

Virtual Private Database, also called fine-grained access control, can add policy-generated predicates to supported SQL. It is appropriate when two authorized sessions should see different rows, such as employees restricted to their assigned region. It is not a universal command-category blocker.

Real Application Security represents application users and authorization through principals, security classes, access control lists, and data- security policies. Current administration uses packages including XS_PRINCIPAL, XS_SECURITY_CLASS, XS_ACL, and XS_DATA_SECURITY. It is an application identity framework, not a feature enabled by one initialization parameter and not a Product User Profile replacement.

Unified Auditing and fine-grained auditing provide evidence and accountability. They can record security-relevant operations and SQL Firewall violations for investigation, but an audit record normally does not prevent the operation. Design audit policies around explicit questions: which account acted, which object or command was involved, whether it succeeded, from which container and client context, and what response followed.

Profiles govern password and session limits, including idle time and selected resource limits. Database Resource Manager allocates and constrains database resources. These controls protect availability and govern session behavior, but neither one defines an SQL allow-list or grants access to an object.

Logon triggers can initialize trusted session context or reject a narrowly defined session under carefully tested conditions. They should not become the primary authorization architecture. Blocking brand names such as TOAD or SQL Developer through USERENV.MODULE is brittle: the value can vary, may be unset, does not establish trust, and leaves unlisted clients untreated. Use database authorization, SQL Firewall context enforcement, or Database Vault factors when the policy must survive a change of client.

In a multitenant database, local users and their policies belong to the relevant PDB. The SQL Firewall example begins with SHOW CON_NAME so the administrator does not configure APP_API in the wrong container. SQL Firewall can operate in the CDB root and in individual PDBs, but the target account and intended scope determine where it should be administered.

Do not assume that every security object has identical common and local behavior. Common users, common roles, local users, application containers, Database Vault configuration, audit policies, and application-security objects have feature-specific rules. For a local application account, document the PDB service, configuration owner, allow-list owner, authorized connection paths, and recovery procedure as one policy record.

1. Inventory the account's legitimate SQL, stored programs, objects, batch work, and connection paths.
2. Revoke unnecessary privileges and place required privileges in narrowly scoped roles.
3. Separate application, batch, deployment, and administrative duties into appropriate accounts.
4. Capture SQL Firewall activity with representative workloads in a controlled environment.
5. Review the captured SQL and contexts before generating an allow-list.
6. Begin with nonblocking enforcement and investigate every violation.
7. Enable blocking only after application testing, approval, and rollback planning.
8. Establish release and emergency procedures for allow-list changes.
9. Use secure application roles, Database Vault, VPD, or RAS only when their specific control model matches the requirement.
10. Configure auditing for evidence, test the complete policy in the correct PDB, and document the result.
11. Use SQL*Plus or operating-environment hardening separately for client-only commands.

The central design principle is defense in depth with clear boundaries. Privileges decide what an account is authorized to do. SQL Firewall narrows a stable account to expected SQL and connection contexts. Conditional and data-level controls refine sensitive cases. Auditing supplies evidence, and client controls govern operations the database never receives. Together these mechanisms provide consistent Oracle 26ai protection without reviving the desupported Product User Profile.