Changing Access Restrictions in Oracle AI Database 26ai

Removing a restriction can describe opposite security changes. A user who has acquired an approved responsibility may need additional access through a GRANT. A user who no longer requires an operation may need a role or privilege removed through REVOKE. A role might remain granted but need a different default state at logon. A SQL*Plus client restriction may require terminating and relaunching the process rather than executing SQL.

Define the intended direction before choosing a statement. Identify the database identity, requested operation, schema object, service, pluggable database, client, and business approval. An authorization failure is not automatically a defect; it can be evidence that least privilege is working as designed.

Older versions of this lesson removed rows from the SQL*Plus Product User Profile. Oracle desupported the PRODUCT_USER_PROFILE table beginning with Oracle Database 19c, and that product-level security mechanism is unavailable in Oracle 26ai. Supported changes therefore operate on database privileges and roles or on the policy used to launch SQL*Plus.

Plan the Authorization Change

Treat a grant or revoke as a production security change rather than an interactive troubleshooting shortcut. Record the current authorization, the requested end state, the approver, the affected PDB or root, and the identities and applications that depend on the privilege. A human-readable request such as “let Brian enter orders” must be mapped to the actual database identity and to a specific role, object privilege, or protected API.

Capture the relevant grant metadata before changing it. A simple inverse statement may not reproduce the original state if the grant used ADMIN OPTION, DELEGATE OPTION, GRANT OPTION, or a common container scope. The rollback plan must restore those properties deliberately rather than merely grant the same name again.

Estimate the change's reach. Granting a role to Brian changes one membership. Adding a privilege to ORDER_WRITER changes the effective authorization of every user, role, or program unit that receives that role. Revoking an object privilege can affect onward grants and dependent schema objects. Use a maintenance and notification process appropriate to that impact.

Never generate production authorization changes directly from a username wildcard. A pattern can find candidate accounts for a review queue, but each result must be resolved to an owner and an approved responsibility. This keeps the administrative convenience of search without allowing a naming convention to become an accidental security policy.

Identify the Authorization Path

Begin with evidence. Suppose Brian cannot insert an order. Determine which database identity the connection actually uses and whether the operation targets APP_OWNER.ORDERS in the expected PDB. Connection pools, proxy authentication, and shared application schemas can make the database identity different from the end user's name.

An appropriately authorized administrator can inspect Brian's direct role grants:

SELECT grantee, granted_role, default_role,
       admin_option, common, inherited
FROM   dba_role_privs
WHERE  grantee = 'BRIAN'
ORDER  BY granted_role;

The next query examines direct object privileges granted to Brian and to the two roles introduced in Lesson 5:

SELECT grantee, owner, table_name, privilege,
       grantable, common, inherited
FROM   dba_tab_privs
WHERE  grantee IN ('BRIAN', 'ORDER_READER', 'ORDER_WRITER')
AND    owner = 'APP_OWNER'
AND    table_name = 'ORDERS'
ORDER  BY grantee, privilege;

Access to these DBA_* views requires appropriate dictionary authority, and the results reflect the current container. These queries do not necessarily reveal every route to a privilege. Nested roles, grants to PUBLIC, system or schema privileges, code-based access control, and other grant paths may also contribute to the effective authorization.

Do not confuse an authorization restriction with a relational constraint. A privilege determines who may attempt an operation. A primary key, foreign key, unique constraint, or check constraint determines which data states are valid after an authorized operation. If a valid constraint rejects an insert, correct the data or transaction. Dropping the constraint is not an authorization remedy.

Restore Approved Access with a Role

Lesson 5 assigned Bob, Brian, and Bruce to ORDER_READER, which provides SELECT on the orders table. If Brian's job now includes the complete order-writer responsibility and that change is approved, grant the broader role explicitly:

GRANT order_writer TO brian;

This statement adds an authorization path; it does not delete or override a restriction. Review the contents of ORDER_WRITER before issuing the grant. In the Lesson 5 example, the role supplies SELECT, INSERT, and UPDATE on APP_OWNER.ORDERS. Granting it would be excessive if Brian had been approved only to create orders.

Decide separately whether Brian should retain ORDER_READER. If the writer role replaces the reader role under the organization's model, grant the replacement before revoking the old role so that approved access remains continuous:

GRANT order_writer TO brian;
REVOKE order_reader FROM brian;

The revoke removes only the direct ORDER_READER grant from Brian. It does not create a deny rule, remove privileges supplied by ORDER_WRITER, or cancel an equivalent role inherited through another path. Explicit responsibility-based membership is more reliable than granting access because a username matches a pattern such as B%.

Use Narrow Grants or Protected APIs When Appropriate

If the full writer role is too broad, select a narrower design. A direct object grant is valid when Brian alone requires the approved operation:

GRANT INSERT ON app_owner.orders TO brian;

Direct grants become difficult to review when repeated for many users. A purpose-built role such as ORDER_CREATOR may be more maintainable when several identities share the same responsibility. Define its owner, privilege scope, membership approval, and review lifecycle rather than creating a role merely to shorten the SQL.

Direct table DML may still be inappropriate. Creating an order can require inventory checks, valid status transitions, approval limits, and related updates. In that design, place the transaction in a reviewed stored API and grant a runtime role EXECUTE on the package instead of granting direct INSERT on the table. The role identifies who may invoke the operation; the stored code controls how the business transaction occurs.

If a secure application role supplies the capability only after trusted policy code validates a session, do not bypass that package to make a failed test succeed. Verify Brian's entitlement, application identity propagation, and the policy decision. Correct defective policy code through the deployment process without weakening a condition that correctly rejected an unauthorized session.

Undo a Grant with REVOKE

If the requested change is to remove previously granted authorization, use REVOKE. For example, the following statement makes the writer role unavailable for Brian to enable later:

REVOKE order_writer FROM brian;

Role revocation has an important session boundary. If ORDER_WRITER is already enabled in Brian's current session, that session can continue to exercise the role's privileges while the role remains enabled. Brian cannot enable it again after it is disabled, and a new session will not have the revoked role. Long-running and pooled sessions therefore require deliberate refresh, retirement, or reconnection before testing the final state.

If Brian received INSERT directly and that specific grant must be withdrawn, revoke it from the actual grantee:

REVOKE INSERT ON app_owner.orders FROM brian;

A direct object-privilege revoke takes effect immediately, but it removes only that grant path. Brian may still receive INSERT through ORDER_WRITER, another enabled role, or another grantor. Confirm the complete privilege domain before declaring the access removed.

Do not revoke INSERT from ORDER_WRITER to solve a one-user exception unless the shared role is itself incorrect. Changing the role removes that privilege immediately for every session using the role. Object-privilege revocation can also cascade grants made onward with grant authority and can invalidate dependent views or prevent stored code from executing. Inventory affected users and dependencies, obtain approval, and prepare reviewed rollback SQL before changing a shared production role.

Container scope also matters. A locally granted role or privilege in one PDB is distinct from a common grant made from a root with CONTAINER=ALL. Execute the change in the correct container and match the scope of the original grant. A local revoke cannot silently erase a separate common grant.

Change Default Roles Without Changing Entitlement

Sometimes the role is granted correctly but is not enabled automatically when a new session begins. An authorized administrator can configure Brian's eligible default roles:

ALTER USER brian
  DEFAULT ROLE order_reader, order_writer;

This statement does not grant either role. Each named role must already be eligible under Oracle's default-role rules, including being granted directly to Brian. The statement defines the default-role list, so omitting another required default role can change the logon state unintentionally. Inspect the complete configuration before executing it.

Making a role nondefault also does not revoke it. An ordinary granted role can still be enabled later when its authorization method permits. Conversely, password-authenticated and secure application roles cannot simply be placed in the default-role list. Use GRANT or REVOKE when the requirement concerns entitlement; use DEFAULT ROLE only when it concerns initial enablement.

Change a SQL*Plus Restriction by Relaunching the Client

Database SQL cannot relax the active SQL*Plus -RESTRICT level. A process started at level 1 has HOST and EDIT disabled even before it connects to a server, and those commands remain disabled until SQL*Plus terminates:

sqlplus -RESTRICT 1 reporting_user@service_name

To use a different approved level, exit that process and start a new SQL*Plus process through the organization's controlled launcher. Omitting the option permits otherwise available SQL*Plus commands, but that choice should follow workstation and client-use policy. Do not place a password on the command line.

Relaunching SQL*Plus does not grant a database privilege. Granting a role does not enable HOST or EDIT inside an already restricted process. The client option is also different from Oracle Database restricted session mode, a PDB lockdown profile, a resource profile, a read-only user, or the ORA_PLUS_AUTOEXEC setting. Each control has a separate scope and change procedure.

Verify the New Authorization

After changing a role or its default status, use a new test session and distinguish roles granted to the user from roles currently enabled:

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 grants visible to the current user. SESSION_ROLES describes the current enabled-role set. Neither view alone proves every effective object or system privilege. Administrative views and dependency analysis may be required to follow nested roles, direct grants, shared grants, and container scope.

Run positive and negative tests. Confirm that Brian can perform the newly approved operation and that an order reader still cannot perform it. Confirm that unrelated writer operations remain unavailable when the narrower design was selected. Test through the actual application, pool, proxy, service, PDB, and direct-client paths that exist in the deployment.

For SQL*Plus policy, test the launcher separately. Confirm that the intended commands fail in a restricted process and that a newly launched, approved process has the expected level. Database privilege queries cannot prove which startup option created a client process.

Record the request, approval, statements executed, affected container, test evidence, and rollback statements. Unified auditing and change-management records can provide evidence of administrative activity, but they do not grant or block an operation by themselves.

Summary

1. Identify whether the change restores access, withdraws access, changes default enablement, or changes a client process.
2. Use an approved role or narrow object privilege to restore database capability.
3. Use REVOKE to remove a specific grant path, then inspect other paths that may supply equivalent authority.
4. Do not modify a shared role to solve a single-user exception without analyzing every member and dependency.
5. Exit and relaunch SQL*Plus to change -RESTRICT; SQL cannot relax it inside the current process.
6. Verify configuration and behavior in a new session through every relevant connection path.

The next lesson reports on supported authorization state, including role membership, role contents, direct privileges, default roles, and enabled session roles.