SQL*Plus Security and Oracle Authorization Conclusion

SQL*Plus remains a supported command-line utility for Oracle AI Database 26ai. It can connect to a database service, execute SQL and PL/SQL, run repeatable scripts, inspect database objects, and format or spool query results. Those capabilities make it valuable for administrators, developers, automated jobs, and troubleshooting. They do not make SQL*Plus the security boundary for the database.

The central lesson of this module is that Oracle Database must enforce database authorization. A rule implemented only in one client does not protect the same account when it connects through SQLcl, SQL Developer, JDBC, an application server, or another client. SQL*Plus controls can reduce the local capabilities of a managed command-line process, but privileges, roles, protected program units, and database policies determine what an authenticated identity is authorized to do.

From Product Profiles to Supported Controls

Earlier Oracle releases included the SQL*Plus Product User Profile, commonly called the PUP table. SQL*Plus could consult entries in that table and suppress selected commands or role use for matching users. Oracle deprecated the feature in Oracle Database 18c and desupported it beginning with Oracle Database 19c. It is therefore not a supported security mechanism for Oracle AI Database 26ai.

The feature's history still teaches an important architectural lesson. A PUP entry was interpreted by SQL*Plus; it did not revoke an object or system privilege in Oracle Database. A person using a different client could still exercise the underlying authorization. A residual PUP object or reference in an upgraded environment does not restore the desupported product-security functionality, and creating a similarly named table does not create an enforcement mechanism.

Modernization is not a row-for-row conversion of old restrictions. Each historical rule must first be translated into a security requirement. The correct replacement depends on what must be protected and where the control must operate.

This mapping prevents two recurring errors: granting a broad database privilege and trying to hide its syntax in a client, or choosing a feature merely because Oracle uses the word profile in its name. Database user profiles, PDB lockdown profiles, optimizer SQL profiles, SQL*Plus startup files, and the historical Product User Profile solve different problems.

Build Authorization with Least Privilege

Object privileges authorize operations such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE on particular objects. System and schema privileges authorize broader classes of operations. Roles collect related privileges so an administrator can assign a job function as a unit. Because Oracle Database evaluates these grants, the authorization applies regardless of the client that submits the request.

A sound design separates identities by responsibility. The application owner, deployment account, runtime service, reporting account, and human administrator should not automatically share one credential or one broad privilege domain. Protected objects should reside in an owner schema that ordinary users do not control. Routine identities should receive the smallest useful set of grants, and privileges containing ANY deserve particular scrutiny because they can extend across many schemas.

Roles simplify administration, but the role name alone does not document its effect. A role can contain object privileges, system privileges, schema privileges, and other roles. A nested role can enlarge the effective privilege domain beyond the first role an administrator inspects. Direct grants also remain important because revoking a role does not remove an equivalent privilege granted independently to the same user.

When a data change is valid only after business conditions are checked, do not grant unrestricted DML on the base table and depend on an application screen to enforce the conditions. A stored PL/SQL API can provide a narrow entry point. The caller receives EXECUTE on the approved program unit rather than direct modification rights on the protected table. The package can validate inputs, evaluate current data, apply the business rule, and perform the permitted transaction.

Definer's-rights and invoker's-rights program units have different privilege behavior, and roles are not treated identically in every stored-code context. The program unit's owner, direct grants, dynamic SQL, error handling, and transaction design therefore require review. A stored API is a useful authorization boundary only when its implementation is secure.

Understand Granted, Default, and Enabled Roles

A role can be granted to a user without being active in every session. The grant establishes entitlement. A default-role setting determines whether an eligible role is normally enabled at logon. SET ROLE can alter the enabled role set for the current session when the role's authorization method permits. These are three different states, and an authorization report must identify which question it answers.

Secure application roles add another distinction. Their privileges become active only through an authorized PL/SQL procedure or package after trusted checks succeed. This can support an application-controlled privilege path, but it requires careful handling of connection pools, proxy authentication, session reuse, and application context. A client-supplied program name is not sufficient proof that a request followed the approved path.

Revocation also has a session dimension. If a role is already enabled when it is revoked, Oracle permits the current session to continue using that role's privilege domain while the role remains enabled. The user cannot enable it again after it becomes disabled. Administrators should therefore test important role changes in a newly authenticated session and decide whether existing sessions require operational intervention.

Use SQL*Plus Controls for Client Hardening

The SQL*Plus -RESTRICT startup option remains useful when a managed command-line session should have fewer file-system or operating-system capabilities. Its documented levels disable progressively larger groups of SQL*Plus commands. This can reduce exposure from commands such as HOST, EDIT, SPOOL, or script execution, depending on the chosen level.

The restriction belongs to that SQL*Plus process. It does not revoke INSERT, DELETE, CREATE TABLE, or another database privilege, and it does not govern another client. Changing the restriction requires ending the process and starting a new SQL*Plus session with the intended option. It is best treated as defense in depth around an already least-privileged database identity.

SQL*Plus also supports site and user profile scripts, normally glogin.sql and login.sql. They configure the client environment, including prompts, formatting, and other session behavior. Oracle AI Database 26ai also provides ORA_PLUS_AUTOEXEC settings that govern implicit processing of these scripts. Startup files must be protected from unauthorized modification, but their settings are not a substitute for database authorization.

SQL wildcards likewise retain their ordinary purpose. With the LIKE operator, percent matches zero or more characters and underscore matches one character. That is pattern matching, not a durable authorization model. Usernames that happen to share a prefix do not necessarily share a job function, and a newly created matching account should not silently acquire access. Assign users to explicit roles through reviewed provisioning and deprovisioning processes.

Change Access Through the Mechanism That Created It

Every authorization change should begin with the requirement, affected identities, object or operation, current container, and approved scope. Before adding or removing a grant, identify every path that may supply the capability: direct grants, roles, nested roles, grants to PUBLIC, schema privileges, system privileges, stored APIs, secure application roles, and common grants inherited into a PDB.

Use GRANT to establish approved authorization and REVOKE to withdraw a particular grant path. Changing a default-role setting changes automatic enablement; it does not remove the role entitlement. Disabling a role in one session does not revoke it from the account. Relaunching SQL*Plus with a different restriction level changes client behavior; it does not alter database grants. Clear terminology prevents an administrator from changing the wrong layer.

A revoke must be evaluated for operational impact and for alternative authorization paths. Removing a role may affect several applications or users, while removing one privilege from a shared role changes every grantee of that role. Conversely, a user may retain the same capability through another role or a direct grant. The desired outcome is a verified change to effective authorization, not merely a successful DDL statement.

Report Authorization in Its Session and Container Context

Lesson 7 completed the management workflow by reporting the configured and active authorization. Begin by recording the session user, current schema, service, and container. This is essential in a multitenant database and in environments that use shared schemas, proxy sessions, or connection pools.

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER')   AS session_user,
       SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME')   AS service_name,
       SYS_CONTEXT('USERENV', 'CON_NAME')       AS container_name
FROM   dual;

USER_ROLE_PRIVS reports roles granted to the current user, including default-role and container metadata. SESSION_ROLES reports roles enabled in the current session. An administrator can use DBA_ROLE_PRIVS to report grants to users and roles, subject to appropriate dictionary access. Comparing these views distinguishes entitlement from runtime state.

The contents of a role require additional views. ROLE_TAB_PRIVS reports object privileges granted to accessible roles, ROLE_SYS_PRIVS reports their system privileges, and ROLE_ROLE_PRIVS exposes nested role membership. Broader reports can use DBA_TAB_PRIVS for object grants and DBA_SYS_PRIVS for system privileges granted to users and roles.

Oracle AI Database 26ai adds schema privileges as a distinct authorization category. ROLE_SCHEMA_PRIVS reports schema privileges granted to accessible roles. The 26ai views ROLE_SYS_PRIVS_ALL and DBA_SYS_PRIVS_ALL combine system and schema privilege reporting. In the combined views, a populated SCHEMA column identifies a schema privilege; a null value identifies a system privilege.

The COMMON and INHERITED columns must be interpreted in container context. COMMON='YES' indicates a common grant made with CONTAINER=ALL, while COMMON='NO' identifies a local grant. An INHERITED value of YES means it came from another container; it does not describe a nested role. A report run in the wrong container can produce an incomplete or misleading result.

Why No Single Query Proves Effective Access

Effective authorization can combine direct object, schema, and system privileges; enabled roles; nested roles; grants to PUBLIC; common and local grants; stored-code execution rules; secure application role activation; and policy features. The identity used by an application may also differ from the human identity interacting with it. One dictionary query rarely captures all of these dimensions.

Configured authorization should therefore be compared with behavior. A positive test proves that an approved identity can perform the required operation. A negative test proves that a reader, unrelated identity, or unapproved connection path cannot perform it. Test the actual service, PDB, application, connection pool, proxy path, and direct-client paths that exist in the deployment.

Use a new or deliberately refreshed session after material role changes. Record the report time, database identity, container, tested statement, result, and change reference. Repeatable evidence makes it possible for another administrator or reviewer to reproduce the conclusion rather than relying on an unexplained screenshot or a single successful query.

Module 7 Completion Checklist

Now that you have completed this module, you should be able to:

1. Explain why the historical SQL*Plus Product User Profile is not a supported authorization mechanism for Oracle AI Database 26ai.
2. Distinguish database authorization from SQL*Plus configuration and client hardening.
3. Select object, schema, and system privileges according to the required operation and scope.
4. Use roles to represent job functions and account for direct grants and nested roles.
5. Distinguish a granted role, a default role, and a role enabled in the current session.
6. Use a protected stored API or secure application role when authorization depends on approved database-side checks.
7. Use SQL*Plus -RESTRICT only for its documented client-command restrictions.
8. Recognize that SQL wildcards perform pattern matching and should not replace explicit role assignment.
9. Change access through the supported mechanism that created it and evaluate every remaining authorization path.
10. Report role grants, enabled roles, role contents, direct privileges, schema privileges, and container scope.
11. Verify each material authorization change with positive and negative tests through the real connection path.

SQL*Plus is powerful because it provides direct, scriptable interaction with Oracle Database. Safe use does not require pretending that the utility is the database's authorization engine. It requires narrowly scoped database privileges, intentional role design, protected program interfaces, secure client configuration, accurate reporting, and reproducible verification.

In the next module, you will learn how database triggers respond to specified events and extend Oracle Database behavior. The same principles continue to apply: understand the execution context, grant only the required authority, protect business rules at the correct layer, and verify the resulting behavior.

Wildcards Profile - Quiz