Replacing SQL*Plus Wildcard Restrictions in Oracle 26ai

Wildcards provide a concise way to find values that follow a pattern. Earlier versions of this lesson extended that idea to the SQL*Plus Product User Profile, where a username pattern such as B% supposedly disabled a command for every matching account. That approach is no longer suitable for Oracle AI Database 26ai. Oracle desupported the PRODUCT_USER_PROFILE table beginning with Oracle Database 19c, and SQL*Plus product-level security is unavailable.

The old examples also combined two different security requirements. Preventing database users from inserting rows is an authorization requirement that Oracle Database must enforce across every client. Preventing a SQL*Plus process from launching an operating system command is a client-hardening requirement. Oracle 26ai addresses the first requirement with privileges and roles. The SQL*Plus -RESTRICT startup option can address the second requirement for a controlled SQL*Plus process.

This distinction is essential. A client setting must not be treated as a substitute for database authorization, and a database role does not control the local editing and operating system commands provided by SQL*Plus. The appropriate control depends on the operation and the boundary that must enforce it.

Use Wildcards for Pattern Matching

Oracle SQL uses two familiar wildcard characters with the LIKE condition. A percent sign (%) matches zero or more characters, while an underscore (_) matches exactly one character. These characters are part of a search pattern; they are not users, roles, or privileges.

The following query returns employee last names that begin with Smi. The trailing percent sign permits any number of characters after that prefix:

SELECT employee_id, last_name
FROM   employees
WHERE  last_name LIKE 'Smi%'
ORDER  BY last_name;

An underscore has a narrower meaning. The pattern 'Smi_h' contains exactly five character positions: the three literal characters Smi, one arbitrary character, and the final h. It can match Smith, but it does not match names of arbitrary length merely because they begin with Smi and end with h.

Sometimes a percent sign or underscore is data rather than a wildcard. An ESCAPE clause can designate a character that makes the following wildcard literal. For example, the next predicate finds codes that begin with the four literal characters QA_1:

SELECT test_code
FROM   quality_tests
WHERE  test_code LIKE 'QA\_1%' ESCAPE '\';

The backslash escapes the underscore, while the final percent sign remains a wildcard. Pattern results can also depend on the data type, database character set, and collation rules used for the comparison. Test a predicate with representative data rather than assuming that letter case and linguistic equivalence behave identically in every database configuration.

Wildcard placement can affect performance. A prefix such as 'Smi%' may allow useful index access because the beginning of the value is known. A pattern beginning with % often prevents a conventional B-tree index range scan because the starting value is unknown. That is a planning consideration rather than an absolute rule: expression design, indexes, collation, statistics, and optimizer choices all influence the resulting execution plan.

Do Not Use Username Patterns as Authorization

A naming convention can help an administrator locate accounts, but it is not a durable statement of responsibility. Bob, Brian, and Bruce happen to have usernames beginning with B; that shared character does not prove that they perform the same job. A later account beginning with B might belong to an administrator, a service, or an unrelated application.

An authorized administrator can use a pattern as a discovery aid. For example, this query reports database usernames beginning with B:

SELECT username
FROM   dba_users
WHERE  username LIKE 'B%'
ORDER  BY username;

Access to DBA_USERS requires appropriate dictionary privileges. More importantly, the result is a list for review, not an authorization decision. Do not turn an unreviewed result into dynamic GRANT or REVOKE statements. Account ownership, employment status, application purpose, approval records, and current responsibilities should determine access.

A governed provisioning process can still use patterns as input to a report or review queue. The process should resolve each account to an owner, compare the requested role with approved responsibilities, record who authorized the change, and reject ambiguous matches. This preserves the administrative convenience of finding similar names without allowing a naming accident to modify production access.

Oracle does not provide a wildcard form of GRANT or REVOKE, nor does it provide a general DENY privilege that overrides other grants. Authorization is additive: a user can receive a privilege directly, through an enabled role, through a role hierarchy, or through another applicable grant. A secure design therefore records explicit membership and examines every path that can supply the privilege.

Assign Database Privileges Through Roles

A role is a named collection of privileges. Roles let an organization model job responsibilities explicitly and grant the same reviewed privilege set to multiple users. The consolidated lesson image divides the sample users into two groups: order writers can read and modify orders, while order readers can only read them.

The following example creates two roles in the current pluggable database and grants each role only the object privileges required for its purpose:

CREATE ROLE order_writer;
CREATE ROLE order_reader;

GRANT SELECT, INSERT, UPDATE ON app_owner.orders
  TO order_writer;

GRANT SELECT ON app_owner.orders
  TO order_reader;

GRANT order_writer TO alice, chris, debby, elvis;
GRANT order_reader TO bob, brian, bruce;

The administrator issuing these statements must have authority to create the roles and grant privileges on app_owner.orders. In a multitenant environment, the administrator must also work in the correct container. These examples assume local roles and users in the applicable PDB; they do not create common roles across the CDB.

Bob, Brian, and Bruce cannot obtain INSERT from ORDER_READER because that privilege was not granted to the role. This is an application of least privilege, not a deny rule. If Bob has a direct INSERT grant or receives it through another enabled role, he can still insert rows. Removing a privilege from one role does not override a separate authorization path.

The role names describe responsibilities rather than arbitrary username patterns, which makes the design easier to approve, audit, and maintain. When a person changes duties, an administrator revises that person's explicit role membership. The administrator does not have to rename the account or hope that its first character continues to describe the correct access.

Direct table DML is not always the best application design. If an order may be changed only after validating inventory, status, approval limits, and other business rules, place that transaction in a reviewed stored API and grant the runtime role EXECUTE on the API rather than broad direct DML on the underlying tables. A role then determines who may invoke the operation, while the stored code defines how the operation must be performed. This prevents an ad hoc client from bypassing application-only checks with a syntactically valid statement.

Role membership also requires a lifecycle. Record the business owner and purpose of each role, review its object and system privileges, remove departed or transferred users promptly, and periodically recertify membership. Privilege analysis can provide evidence about which privileges a representative workload actually uses. That evidence can help narrow a role, but it does not replace approval or automatically determine who should receive the role.

Use SQL*Plus -RESTRICT for Client Commands

The earlier lesson also tried to disable the SQL*Plus HOST command for all users through a % row in the obsolete product profile. In Oracle 26ai, SQL*Plus offers the -RESTRICT startup option for selected commands that interact with the operating system. A controlled launcher can start a session at restriction level 1 without exposing a password on the command line:

sqlplus -RESTRICT 1 reporting_user@service_name

At level 1, SQL*Plus disables EDIT and HOST. Those commands remain disabled until that SQL*Plus process terminates. The option is effective even before a server connection exists, which confirms that it operates in the client process rather than changing privileges in Oracle Database.

The boundary is intentionally narrow. -RESTRICT 1 does not disable SQL INSERT, UPDATE, or DELETE; it does not revoke a role; and it does not change the database account. Another SQL*Plus process started without the option is not restricted by this switch. SQLcl, SQL Developer, JDBC programs, application servers, and other clients also remain outside this particular process boundary.

Restriction levels 2 and 3 disable additional SQL*Plus file and script commands, but a higher level should be selected only when those additional limits match the operational requirement. A managed shortcut, wrapper, remote application environment, or other controlled launch path is needed when an organization expects users to start SQL*Plus with a required option. Database privileges must still enforce access to database data and operations.

Disabling HOST is not an operating system sandbox. If a person already has access to a command prompt, terminal, scheduler, or another program on the workstation, that person may execute operating system commands outside SQL*Plus. The option reduces the capabilities exposed through the controlled client process; workstation permissions, application isolation, and operating system policy remain separate responsibilities.

Verify the Scope of Each Control

A security change is complete only after its effective scope has been verified. A user can inspect roles that have been granted and roles currently enabled in the session with separate views:

SELECT granted_role, default_role
FROM   user_role_privs
ORDER  BY granted_role;

SELECT role
FROM   session_roles
ORDER  BY role;

USER_ROLE_PRIVS describes role grants visible to the current user, while SESSION_ROLES describes roles enabled in the current session. They answer different questions. Administrators may also need DBA_ROLE_PRIVS, ROLE_TAB_PRIVS, direct object-grant views, and role-hierarchy information to determine every path that supplies access.

Test authorization with a newly authenticated session after changing role grants. Existing pooled or long-running sessions may retain an enabled role set until roles are refreshed or the session is replaced. Test through each relevant client as well: a successful SQL*Plus test does not prove that an application connection uses the same identity, roles, container, or proxy configuration.

A useful test plan contains both positive and negative cases. Confirm that an order writer can perform the approved transaction and that an order reader can query the intended object. Then confirm that the reader cannot insert or update through direct SQL, an application connection, or another relevant path. When a negative test unexpectedly succeeds, inspect direct grants, nested roles, grants to PUBLIC, and the identity actually used by the connection before changing the intended role.

Unified auditing can record selected privilege and role activity, while application logs can supply the business context surrounding a request. These records help demonstrate that the implemented controls behave as designed. Auditing does not prevent an operation by itself, so it must accompany—not replace—the privilege model, protected APIs, and controlled client configuration.

Client hardening needs a different test. Confirm that HOST and EDIT fail inside the process started with -RESTRICT 1, then confirm that required database work still succeeds according to the user's privileges. Do not interpret that result as evidence that every client on the workstation or every session for the database user is restricted.

Summary

1. % and _ remain useful SQL pattern-matching characters.
2. Username patterns can identify accounts for review, but they are not authorization principals.
3. Database roles assign reviewed privileges by responsibility and apply across database clients.
4. A role that omits INSERT does not override a direct or inherited INSERT privilege from another path.
5. SQL*Plus -RESTRICT 1 disables HOST and EDIT only in the controlled SQL*Plus process.

The next lesson examines how to revise or remove a supported restriction, such as changing role membership or updating a controlled SQL*Plus launch policy, without returning to the desupported Product User Profile table.

Using Wildcards - Exercise

The exercise preserves the original wildcard-restriction scenario. Use it to identify which requirement belongs to database authorization and which belongs to SQL*Plus client hardening; do not execute legacy Product User Profile statements against Oracle 26ai.

Using Wildcards - Exercise