Restricting SQL Operations and SQL*Plus Commands in Oracle 26ai

Lessons 1 and 2 replaced the historical SQL*Plus product user profile with security controls supported by Oracle AI Database 26ai. The next step is to apply that model to a specific operation. The phrase “disable a command” is too imprecise for a security policy because a SQL statement and a SQL*Plus client command are not the same thing. They are interpreted by different components and require different controls.

Oracle Database decides whether an identity may query an object, change data, execute stored code, create an object, or perform an administrative operation. Privileges, roles, ownership, stored APIs, and applicable database policies participate in that decision. SQL*Plus separately interprets client commands for editing files, starting scripts, spooling output, or invoking an operating-system command. The SQL*Plus -RESTRICT startup option can reduce those local capabilities, but it does not revoke authority from the connected database user.

Define the Operation Before Choosing a Control

A useful restriction begins with the protected action, not the spelling of a command. Before making a change, identify the user, service, role, or administrative identity; the exact operation and target object; the current container; and whether the operation must be always allowed, always denied, or allowed only after a business condition is satisfied. Then identify every path that can authorize it.

For example, “prevent deletion” might mean preventing direct DELETE on one application table, making an entire reporting identity read-only, or permitting deletion only through a cancellation procedure. “Prevent dropping tables” might mean withholding a broad system privilege from a deployment account, but a table owner already has authority over objects in its own schema. “Prevent host access” refers to a SQL*Plus client command, not an Oracle object privilege.

Oracle normally grants authority rather than attaching an arbitrary deny entry to each user. Removing one grant does not override another grant that still supplies the same authority. An administrator must inspect direct grants, grants through enabled roles, common and local grants in a multitenant database, ownership, administrative privileges, stored-program behavior, and any applicable policy feature. Only then can a test demonstrate that the intended restriction is effective.

Restrict SQL Through Privileges and Roles

An object privilege authorizes an operation on a particular object. Examples include SELECT, INSERT, UPDATE, DELETE, and EXECUTE. A system privilege authorizes a broader database operation, such as creating a session or dropping a table in another schema. These privilege categories use different SQL syntax and should not be combined into one invalid statement.

The following examples deliberately keep object privileges and system privileges separate:

REVOKE DELETE ON app_owner.orders FROM order_clerk;
REVOKE DROP ANY TABLE FROM order_clerk;

GRANT CREATE SESSION TO reporting_user;
GRANT SELECT ON app_owner.order_summary TO reporting_user;

The first statement removes a direct object privilege on one table. The second removes a broad system privilege. The third permits a database session, and the fourth permits access to one reporting object. These examples illustrate syntax and scope; they are not a complete production change plan. Revocation can affect applications and dependent program units, so representative testing and dependency analysis are required.

A direct revocation is not a deny rule. If order_clerk still receives DELETE through an enabled role, another grant, or an applicable administrative path, the operation may remain authorized. Likewise, revoking DROP ANY TABLE does not prevent a schema owner from dropping a table it owns. Protected application tables should reside in a controlled owner schema rather than an ordinary end-user schema, and routine accounts should not receive broad ANY privileges merely for convenience.

Roles group privileges for a job function. A default role is enabled when the user creates a session. SET ROLE can change which already available roles are enabled under their authentication rules, but it does not grant a new role. Lesson 4 examines role assignment and activation in more detail. The important point here is that disabling the text SET ROLE in one client would not correct an excessive role grant.

Conditional Operations Belong Behind a Database API

Some operations cannot be modeled as an unconditional grant. An order application might allow cancellation only while an order is pending, before it ships, and when the caller has an approved business function. Granting unrestricted DELETE on the base table would let that identity bypass those conditions through any database client.

The design introduced in Lesson 2 places the table in a protected application-owner schema, withholds direct DML from the runtime identity, and exposes a reviewed PL/SQL package that implements the permitted operation. A narrowly scoped role receives EXECUTE on the package. The package checks inputs, current state, caller context, concurrency conditions, and transaction rules before changing data.

This design does not depend on recognizing SQL*Plus or trusting a client-supplied application name. Oracle Database enforces the privilege boundary. The package itself still requires security review: its owner and grants must be appropriate, dynamic SQL must not permit injection, exceptions must not disclose sensitive information, and connection pools must initialize and clear trusted session context correctly.

PL/SQL Does Not Bypass Missing Privileges

The legacy lesson warned that a user could place a prohibited SQL statement inside BEGIN and END to bypass a restriction. That explanation confuses client command filtering with database authorization. An anonymous PL/SQL block executes in the caller's privilege domain. Wrapping a statement in a block does not create an object privilege or system privilege that the caller lacks.

Stored PL/SQL introduces deliberate privilege models rather than an automatic bypass. A caller generally needs EXECUTE on a stored package, procedure, or function. A definer's-rights program unit runs using the owner's security domain for referenced objects; required privileges must be granted directly to the owner where Oracle requires direct grants. This pattern can expose a narrow operation while keeping its base tables private.

An invoker's-rights program unit runs with the invoker's privileges and is subject to additional inheritance and object-resolution rules. Code-based access control can grant roles to program units for carefully designed cases. Dynamic SQL is also authorized when it executes and does not manufacture missing privileges. Its construction requires particular care because unsafe concatenation can introduce SQL injection.

Consequently, blocking the SQL*Plus words BEGIN, DECLARE, or EXECUTE would not be a durable database policy. The supported design controls who may invoke stored code, which security domain the code uses, which objects it can reach, and which privileges or roles are effective during execution.

Use SQL*Plus -RESTRICT for Client Commands

Oracle AI Database 26ai SQL*Plus supports -RESTRICT {1|2|3} as a startup option. It disables a documented set of SQL*Plus commands that interact with files or the operating system. The restriction applies even before a server connection is established and remains active until that SQL*Plus process terminates.

Level 3 also prevents SQL*Plus from reading login.sql. SQL*Plus reads glogin.sql, but restricted commands in the site profile fail. A managed reporting launcher could start a restricted process without exposing the database password in shell history or process arguments:

sqlplus -RESTRICT 3 reporting_user@service_name

SQL*Plus can prompt for the password or use another approved authentication method. The option does not disable SELECT, DML, DDL, anonymous PL/SQL, arbitrary stored packages, CONNECT, EXIT, RUN, SET, or PASSWORD. Oracle Database still authorizes SQL and PL/SQL submitted from the restricted process.

The switch is useful only within its operating boundary. A user who can launch an unrestricted copy of SQL*Plus or a different client is not constrained by the restricted process. Operating-system permissions, access to executables and script files, credential management, and database grants remain separate controls.

ORA_PLUS_AUTOEXEC=RESTRICT is another distinct safeguard introduced in Lesson 1. It blocks selected commands and statements while glogin.sql, login.sql, and nested implicitly started scripts are being processed. It does not restrict explicitly invoked scripts. Its behavior must not be confused with the three -RESTRICT startup levels.

Choose Stronger Policies When Grants Are Not Enough

Some requirements need a broader barrier or a conditional policy in addition to ordinary grants. Oracle AI Database 26ai provides read-only user and session capabilities that operate independently of granted privileges. The read-only user capability applies to local users, while a read-only session can apply to any user type in any container. These controls can support testing, administration, or read-only application paths.

Read-only users and sessions are broad write barriers. They do not implement a business rule that permits some updates or deletions while rejecting others. They are also different from SET TRANSACTION READ ONLY, which establishes a read-only transaction and a transaction-level consistent view. The scope and lifecycle of the required restriction determine which feature is appropriate.

A secure application role is useful when privileges should become active only after a trusted database-side procedure evaluates session conditions. The role is enabled only through its authorized PL/SQL policy path. This requires a trustworthy context-establishment design; a client-supplied program name alone is not proof that the request originated from an approved application.

In a configured Oracle Database Vault deployment, command rules can apply rule sets to otherwise authorized SQL statements, including selected DML, DDL, and administrative statements. This is a database policy control, not a SQL*Plus switch. Database Vault should be considered only after checking its availability, licensing, multitenant scope, realm interactions, administrative ownership, and testing requirements. Simulation and representative workload testing are important before a command rule is allowed to block production activity.

Verify the Effective Privilege Domain

A restriction is not complete until it is verified. Supported dictionary views can show direct object grants, system privileges, role grants, currently enabled roles, and current session privileges. Depending on the administrator's authority and container, relevant views include DBA_TAB_PRIVS, DBA_SYS_PRIVS, DBA_ROLE_PRIVS, SESSION_ROLES, and SESSION_PRIVS. A successful test also confirms that legitimate application work continues, because an unusable security control can create significant operational risk.

Privilege analysis can capture which privilege paths representative users and applications actually exercise. That evidence can identify unused grants, but the capture window must include normal, exceptional, batch, deployment, and recovery paths before production privileges are removed. Tests should cover every supported client and application route, including proxy identities and pooled sessions where they are used.

Unified auditing can record configured SQL actions, object access, privilege use, and role activity. Auditing provides evidence; it does not prevent a statement. Authorization must enforce the restriction, while auditing supplies the records needed for monitoring, investigation, and compliance. The audit design also needs appropriate retention, protection, storage, and review procedures.

The modern lesson is therefore not about hiding command words. It is about placing each operation behind the correct enforcement boundary. Oracle Database privileges and policies govern SQL across clients. Stored APIs govern conditional business operations. SQL*Plus -RESTRICT reduces selected local client capabilities. In the next lesson, this same model will be applied to supported role assignment and activation rather than to role rows in a desupported product-profile table.