Oracle Profiles and SQL*Plus Security in Oracle AI Database 26ai

The word profile has several meanings in the Oracle ecosystem. A profile can be a SQL*Plus startup script, a database object that applies password and resource limits, a multitenant lockdown control, or optimizer information associated with a SQL statement. These features have different purposes, operate at different layers, and must not be treated as interchangeable.

Earlier versions of this module centered on the SQL*Plus PRODUCT_USER_PROFILE table, also called the product user profile or PUP table.That feature attempted to disable selected SQL*Plus commands or roles for particular users. Oracle deprecated the table in Oracle Database 18c and desupported it beginning with Oracle Database 19c. Consequently, it is not an appropriate foundation for a course targeting Oracle AI Database 26ai. The historical feature remains useful as context, but this module now focuses on controls that Oracle supports for current deployments.

The central security principle is simple: enforce access in Oracle Database rather than in one client program. SQL*Plus configuration can make a command-line session safer and more consistent, but it does not decide whether a database user may select from a table, alter a schema, or execute a procedure. Privileges, roles, database profiles, multitenant controls, and other database security policies provide the durable enforcement boundary.

Why the SQL*Plus Product User Profile Is Legacy

The product user profile addressed a limited client-side problem. An administrator could configure SQL*Plus to suppress selected commands for a named database user. For example, a restricted SQL*Plus session might refuse a command that launched an operating-system process or executed a script. The restriction was interpreted by SQL*Plus; it was not a revocation of the user's underlying Oracle Database privileges.

That distinction is decisive. A user who could connect through SQLcl, SQL Developer, JDBC, an application server, or another client was not necessarily governed by a rule implemented only in SQL*Plus. Client command suppression could reduce accidental or casual use of a command, but it could not serve as a consistent authorization boundary for the database. A security design must continue to protect the data when the client application changes.

Oracle's current upgrade guidance recommends database settings that apply across client applications. For that reason, this module does not teach inserts into SYSTEM.PRODUCT_USER_PROFILE, PUP-table wildcards, role entries, PUPBLD.SQL, or deletion of PUP rows. Any residual references to PUP behavior in SQL*Plus compatibility documentation do not change the feature's desupported status. The modern task is to identify the intended policy and implement it with a supported control at the correct layer.

Enforce Security in Oracle Database, Not in One Client

Privileges and roles express authorization

Oracle Database privileges determine which actions an authenticated user can perform. System privileges authorize classes of database operations, while object privileges authorize operations on particular objects. Roles collect related privileges so administrators can grant and revoke a job function as a unit. These checks occur in the database and therefore apply regardless of the client used to submit the SQL statement.

A least-privilege design grants only what a person or application needs. It does not grant a broad privilege and then rely on SQL*Plus to hide the corresponding command. Direct grants may be appropriate when a privilege must remain available in stored program units or when precise attribution is required; roles are useful for managing collections of privileges. Privilege analysis can help identify privilege paths that are actually used and privileges that may be candidates for removal. Detailed capture and remediation procedures belong in later lessons.

Some applications require privileges to become active only through an approved execution path. Secure application roles and application-context-based authorization can support that design. These mechanisms are materially different from a client-side wildcard: the database evaluates whether the role may be enabled. They should be selected only after the application's authentication, session, and connection-pooling behavior is understood.

Database user profiles govern passwords and resources

An Oracle Database user profile is a named object created with CREATE PROFILE and assigned with CREATE USER or ALTER USER. It groups password-management parameters and, when configured, resource limits. Typical subjects include failed-login attempts, password verification and reuse, account inactivity, sessions per user, connection time, and idle time. Administrators can inspect profile definitions and user assignments through supported data dictionary views.

A database user profile does not grant or deny SELECT, INSERT, CREATE TABLE, or other SQL permissions. Those remain privilege and role decisions. Password parameters are enforced independently of the RESOURCE_LIMIT setting; resource limits that depend on that setting require the documented database configuration. For flexible workload governance, Oracle Database Resource Manager may be a better fit than profile resource limits.

In a multitenant database, scope also matters. Common profiles are created in an appropriate root and can apply across containers, while local profiles are created for their local container. Administrators must confirm the current container, required privilege, naming rules, and intended user scope before creating or changing a profile.

PDB lockdown profiles add container-level restrictions

A PDB lockdown profile is a separate multitenant control. It can restrict specified statements, features, options, and selected operating-system or network access paths within pluggable databases. This helps a CDB administrator contain tenant capabilities even when a local administrator holds broad privileges inside a PDB.

Lockdown profiles do not replace ordinary grants, roles, database user profiles, Database Vault, or application authorization. They address isolation and containment at the PDB boundary. They are created and enabled from the appropriate root by an administrator with the required privileges, and their effect must be evaluated together with the privileges already granted in each container.

What “Profile” Means in Oracle AI Database 26ai

This vocabulary prevents two common design errors. First, SQL*Plus startup files are sometimes confused with the obsolete product user profile even though the files customize a client rather than maintain PUP restrictions. Second, an optimizer SQL profile is sometimes confused with a database user profile even though it has no account-security function.

Configure and Harden SQL*Plus

SQL*Plus still provides useful configuration controls. The site profile, normally named glogin.sql, supplies defaults for a SQL*Plus installation. The user profile, normally named login.sql, supplies settings for an individual user's sessions. These scripts can set a prompt, line and page sizes, number formats, and other SQL*Plus behavior. Those interface preferences never belonged to the historical PUP table.

Oracle AI Database 26ai SQL*Plus uses the ORA_PLUS_AUTOEXEC environment variable to control implicit profile processing. Its values have precise meanings:

• ENABLE implicitly runs glogin.sql and login.sql without the command restrictions imposed by this variable. It is the default when the variable is unset.
• RESTRICT implicitly runs the profiles but blocks selected commands and statements while those profiles, including nested scripts started by them, are active.
• DISABLE prevents SQL*Plus from implicitly running the two profile scripts.

The restriction applies only to implicit processing. It does not restrict a script that a user explicitly invokes with START, @, or @@, even if that script is named login.sql or glogin.sql. An administrator can set the variable in the operating-system environment before starting SQL*Plus. For example, a POSIX shell session could use:

export ORA_PLUS_AUTOEXEC=RESTRICT
sqlplus username@service

SQL*Plus also accepts the startup option -RESTRICT {1|2|3}. Its levels disable documented client commands that interact with files or the operating system. Level 1 disables EDIT and HOST; level 2 additionally disables commands including SAVE, SPOOL, and STORE; level 3 additionally disables GET, START, @, and @@. At level 3, SQL*Plus does not read login.sql; it reads glogin.sql, but restricted commands in it fail.

sqlplus -RESTRICT 2 username@service

This switch can reduce the local capabilities of one SQL*Plus process, including before it connects to a server. It does not disable arbitrary SQL statements and does not revoke database privileges. The two controls can be used together when their separate effects are understood: one governs implicit profile-script processing, and the other disables a documented set of SQL*Plus client commands for the lifetime of the process.

Optimizer SQL Profiles Are a Different Feature

An optimizer SQL profile is performance-tuning metadata, not a security profile. SQL Tuning Advisor can recommend a SQL profile when auxiliary information may improve optimizer estimates. Accepting the recommendation associates that information with a SQL statement, but the SQL profile does not contain or force a single execution plan. The optimizer still selects a plan using the available information.

A SQL plan baseline serves a different performance purpose. It contains accepted plans and can constrain which plans the optimizer may use, providing plan stability as the environment changes. SQL Tuning Advisor and SQL Access Advisor are also distinct advisors: the former analyzes SQL statements and tuning opportunities, while the latter analyzes workloads and possible access structures. These performance features have edition, option, service, and licensing considerations and are not substitutes for privileges, roles, or account profiles.

Module Learning Path

By the end of Module 7, you should be able to:

1. Explain the historical purpose and limitations of the SQL*Plus product user profile.
2. Distinguish SQL*Plus profile scripts, database user profiles, PDB lockdown profiles, optimizer SQL profiles, and SQL plan baselines.
3. Use database privileges and roles to enforce least-privilege access independently of the client application.
4. Explain when a secure application role or application context can limit privilege activation to an approved application path.
5. Configure password and resource policies with Oracle Database profiles and report profile assignments and limits.
6. Explain how PDB lockdown profiles restrict statements, features, options, and selected access paths in a multitenant environment.
7. Harden implicit SQL*Plus profile processing with ORA_PLUS_AUTOEXEC and reduce local client capabilities with -RESTRICT where appropriate.
8. Change or remove a restriction through the supported mechanism that created it rather than by modifying a desupported PUP table.
9. Report grants, role assignments, database user-profile settings, and applicable lockdown rules through supported Oracle views.

The remaining lessons will map each former product-profile objective to a supported Oracle AI Database 26ai control. Lesson 2 begins with the core replacement for client-side command suppression: database privileges and roles that enforce authorization no matter which client submits the request.