Replacing SQL*Plus Product Profiles in Oracle AI Database 26ai

Lesson 1 distinguished several Oracle features that use the word profile. This lesson concentrates on the security problem that the historical SQL*Plus product user profile attempted to solve. The old mechanism could tell SQL*Plus to reject selected commands or disable roles for named users, but it did not change the authorization enforced by Oracle Database. A current design must express its security policy in the database so that it applies whether a request comes from SQL*Plus, SQLcl, SQL Developer, JDBC, an application server, or another client.

This distinction is especially important when an application allows an operation only after checking business conditions. If its database account has an unrestricted privilege on the underlying table, a user who can assume that account may submit the same operation outside the application interface. The durable solution is not to hide a command in one tool. It is to grant only the required database privileges and expose conditional operations through a controlled database interface.

How the Historical Product User Profile Worked

The historical product user profile, commonly called the PUP table, contained restriction information that SQL*Plus could consult after a user connected. SQL*Plus interpreted the matching entries and suppressed designated client commands or role use. This was product-level behavior: the SQL*Plus executable performed the check.

That architecture had a fundamental boundary. A PUP restriction did not revoke an object privilege or system privilege from the connected database user. If another client did not implement the same check, Oracle Database could still authorize a statement according to the user's grants and enabled roles. Even within SQL*Plus, command suppression was not equivalent to a database policy on the protected object.

Oracle deprecated PRODUCT_USER_PROFILE in Oracle Database 18c and desupported it beginning with Oracle Database 19c. Oracle's upgrade guidance states that the SQL*Plus product-level security feature is unavailable and recommends Oracle Database settings so security remains consistent across client applications. Therefore, an Oracle AI Database 26ai deployment must not recreate the old table, run PUPBLD.SQL, or maintain PUP rows. A user-created table with a familiar name has no security effect unless supported enforcement code reads and applies it.

Migration begins by identifying what each old restriction was intended to accomplish. A rule meant to protect a table, constrain application data changes, prevent routine DDL, reduce operating-system access from SQL*Plus, or control password policy represents a different requirement. No single feature replaces every PUP entry, and a row-for-row conversion would preserve the old design error.

Why Application-Only Restrictions Can Be Bypassed

Suppose an order-management application permits cancellation only while an order is pending, before shipment, and by an authorized employee. The user interface checks those conditions before submitting a request. If the application's database identity also has direct DELETE access to the base table, however, the interface is not the only path to that privilege. Anyone who can use the same identity may attempt an authorized DELETE through another client without running the interface checks.

The security problem is the scope of the database privilege, not the presence of SQL*Plus. Blocking the word DELETE in SQL*Plus would leave the underlying privilege intact. Blocking SQL*Plus entirely would still leave every other connection path to consider. Likewise, trusting a client name or a value supplied by the client does not prove that the request passed through approved application logic.

The database authorization model should reflect the business rule. If an account must never delete arbitrary rows, do not grant it direct DELETE on the base table. Place protected tables in an application-owner schema, keep end users and runtime identities separate from that owner, and expose only the operations those identities need. This makes the policy independent of the tool used to connect.

Enforce the Policy with Privileges and Roles

Oracle object privileges authorize actions on specific objects. Examples include SELECT, INSERT, UPDATE, DELETE, and EXECUTE. System privileges authorize broader database operations. Roles group privileges so that administrators can manage a job function as a unit. Oracle Database evaluates these privileges and roles when it processes a statement, regardless of the submitting client.

Least privilege means granting a human or application identity only the capabilities required for its purpose. It also means separating identities whose responsibilities differ. An application owner, deployment account, runtime service, reporting account, and human administrator should not automatically share one credential or one broad role. Object-specific grants are generally safer than convenient ANY privileges when the required object set is known.

The old DROP TABLE example requires particular care. A schema owner has inherent authority over objects in its schema, so hiding DROP in SQL*Plus does not protect that owner from its own authority. Protected application objects should not be owned by an ordinary end-user account. Routine users also should not receive broad privileges such as DROP ANY TABLE. DDL should be performed through a controlled owner or deployment identity, with security-relevant activity audited when organizational policy requires evidence.

A supported restriction is removed through the same security mechanism that created it. An object privilege is changed with an appropriate GRANT or REVOKE; a role assignment or default-role setting is changed through role and user administration; a package policy, Virtual Private Database policy, read-only user setting, or lockdown rule is altered through its documented interface. Deleting a row from an obsolete PUP table is not part of this model.

Expose Approved Changes Through a Stored API

When a data change is valid only after business conditions are satisfied, a stored PL/SQL API can provide a narrow database entry point. The base table remains in a protected schema. Runtime users receive no direct DML privilege that would bypass the API. A package validates the caller's request, checks the current data and required conditions, performs the permitted transaction, and reports a controlled result.

A definer's-rights program unit can perform work using the security domain of its owner while callers receive only EXECUTE on the program unit. The owner must have required object privileges directly where Oracle requires direct grants; roles are not a substitute inside definer's-rights code. Definer's rights alone do not make a package safe. Its ownership, grants, input validation, transaction behavior, exception handling, and use of dynamic SQL all require review.

The following sketch shows the privilege boundary, not a complete production implementation:

CREATE ROLE order_app_user;

GRANT EXECUTE ON app_owner.order_api TO order_app_user;
GRANT order_app_user TO app_runtime;

In this design, app_runtime is not granted direct DELETE on the protected order table. Its role can invoke the approved package, and the package contains the cancellation policy. The exact package code depends on real business requirements, concurrency rules, audit requirements, and the application's transaction design.

A secure application role can add conditional role activation when privileges should become available only after trusted database-side checks. CREATE ROLE ... IDENTIFIED USING associates such a role with an authorized PL/SQL procedure or package. The application invokes the policy code, and Oracle Database enables the role only through that approved path after the checks succeed.

This design must account for connection pools, proxy authentication, session reuse, and trusted application-context initialization. A program name or other client-supplied label is not reliable proof by itself. Secure application roles solve conditional activation; they do not eliminate the need for least-privilege grants, secure credentials, protected context-setting code, and correct pool cleanup.

Choose the Control That Matches the Requirement

The intended policy determines the appropriate Oracle 26ai control. Several supported mechanisms can participate in one design, but their boundaries should remain explicit.

The Oracle 26ai read-only user setting illustrates why scope matters. It can prevent a local PDB user from performing writes even when that user has write privileges. That is useful when the requirement is broadly read-only access. It does not implement a rule that allows some conditional updates or deletions while denying others, so it does not replace a stored API or row-sensitive policy.

Views can expose selected rows or columns and can be combined with carefully scoped privileges. Oracle Virtual Private Database can apply dynamic predicates for row- or column-sensitive access where the feature is appropriate. PDB lockdown profiles address containment within pluggable databases. These mechanisms do not replace ordinary grants and roles, and administrators must check edition, service, deployment, and licensing requirements before choosing optional policy technologies.

Database User Profiles Solve a Different Problem

An Oracle Database user profile is a named object created with CREATE PROFILE. It groups password-management parameters and resource limits that can be assigned to users. Password settings can cover subjects such as failed login attempts, verification, reuse, and inactivity. Resource settings can cover subjects such as concurrent sessions, connection time, idle time, CPU, and logical reads.

A database user profile does not authorize SQL commands or object access. It cannot replace GRANT, REVOKE, a stored API, or a row-level policy. Password parameters are enforced independently; resource limits that depend on RESOURCE_LIMIT require the documented database configuration. Administrators should inspect the actual DEFAULT profile and organizational policy rather than assume every default resource value is unlimited.

Oracle AI Database 26ai also supports mandatory profiles for centrally enforcing permitted password-complexity settings across a CDB or selected PDBs. This is account-policy functionality, not a revival of SQL*Plus product-level security. SQL*Plus profile scripts, PDB lockdown profiles, optimizer SQL profiles, and SQL plan baselines likewise retain the separate meanings introduced in Lesson 1.

Verify and Monitor the Modern Policy

Privilege analysis can capture privileges used by users and applications during a defined period and report used and unused privilege paths. This can help an administrator reduce excessive grants, but the capture must represent the application's real workloads before privileges are removed. Supported dictionary views should be used to report object grants, system privileges, role assignments, enabled roles, and profile settings.

Unified auditing provides evidence about configured activities, including user, role, privilege, object, and application-context events. Auditing does not prevent an operation; authorization must already enforce the policy. Audit design should select relevant events, protect the records, manage retention, and account for the performance and storage requirements of the organization's evidence policy.

The lesson's modernization can therefore be summarized as a change in enforcement boundary. The historical product user profile asked one client to hide selected capabilities. Oracle AI Database 26ai asks the database to grant the minimum authority, place conditional operations behind controlled interfaces, and record relevant activity. Client hardening may still reduce local exposure, but it remains an additional safeguard rather than the authorization system.

In the next lesson, this principle will be applied to restricting an operation with supported database controls. The task will begin with the object, user, and business rule that must be protected, not with an entry in a desupported product-profile table.