Oracle Database / Advanced: secure and recoverable evolution / Lesson 13

Privileges, packages and secure interfaces

Expose a narrow booking API and reason about schema ownership, stored-code rights and dynamic SQL.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish object privileges, roles and callable package permissions.
Review definer and invoker rights with safe value binding.

Separate the owner from the runtime principal

A schema owner can maintain tables while an application runtime account receives only the operations it needs. Giving the application broad ANY privileges or ownership makes every application defect more powerful. A package can expose reserve_capacity while keeping underlying table writes outside the caller’s direct interface. This is a design boundary, not a reason to place arbitrary privileged behavior in one enormous package. List the permitted transitions, required inputs and audit fields. Review migrations, reporting and ordinary runtime as separate responsibilities so a deployment permission does not silently become a permanent application permission.

Grant a reviewed interface rather than all tables

The illustrative grant gives a synthetic runtime account permission to execute one owner package. It does not itself grant SELECT or UPDATE on every underlying table. The package implementation, its AUTHID setting and its owner’s privileges determine how statements are checked. Grants are administration changes and remain non-runnable here. Before proposing them, inspect the current policy through approved evidence and name the exact object owner. A synonym can simplify the package name but adds no authority. Keep a record of why each grant exists and how a retired application loses it.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

GRANT EXECUTE ON lab_owner.booking_api TO lab_runtime;

A proposed least-privilege interface grant for fictional accounts, not a command to run against any configured database.

AUTHID is an execution decision

Stored PL/SQL defaults to definer rights. AUTHID CURRENT_USER selects invoker-rights behavior, changing runtime name and privilege checking in documented ways. Definer-rights code normally executes with the owner’s relevant direct grants rather than relying on roles enabled in an interactive session; compilation and runtime requirements must both be reviewed. Invoker-rights code is not automatically safe merely because it uses the caller’s context. Object resolution, inherited privileges and called routines still matter. State the intended authority at the package specification and test allowed and denied caller cases in an independently authorized lab.

Bind data inside dynamic SQL

Static SQL is easier to inspect when the object and structure are known. If dynamic SQL is necessary, bind the note and shipment identifier rather than concatenating their values. The code keeps the statement structure fixed. Identifiers cannot be supplied as ordinary bind values, so a dynamic table choice requires a narrow allowlist and appropriate identifier validation, not merely escaping a string. DBMS_ASSERT helps with specified validation tasks but does not decide whether an object is authorized for the business operation. A syntactically valid name can still point to the wrong sensitive object.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

DECLARE
  v_note VARCHAR2(100) := 'lab inspection';
  v_id NUMBER := 101;
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE shipment_notes SET note_text = :note WHERE shipment_id = :id'
    USING v_note, v_id;
END;

Values remain data even when they contain quotes. The caller still needs an approved transaction and a valid authority model.

Review both authorized success and denied paths

Security verification includes a runtime user that can call the booking API, cannot update protected tables directly and cannot supply an unapproved object name through the interface. Capture only synthetic inputs and approved audit evidence. A successful administrator call proves little about the runtime user’s authority. Connection pool session state can also leak application context between requests unless initialization and reset behavior are explicit. Treat auditing, encryption and policy features according to the exact release, edition and service entitlement; the presence of a documented option does not establish that a particular deployment includes it.

Common errors and fixes

ORA-01031

Why it happens: The executing context lacks a privilege required by the statement.
How to fix it: Identify the object and stored-code rights model before proposing a narrow grant.
Prevent it next time: Verify permissions as the actual runtime principal rather than an administrator.

Stored code fails although interactive SQL works

Why it happens: The session relied on a role or different name context unavailable to the stored unit.
How to fix it: Review compilation grants, AUTHID and owner-qualified dependencies.
Prevent it next time: Record direct dependencies and allowed callers in package review.

A quoted input changes the SQL statement

Why it happens: Dynamic SQL concatenated data into executable structure.
How to fix it: Bind values and allowlist any genuinely dynamic identifiers.
Prevent it next time: Test quotes, Unicode and denied object choices with synthetic inputs.

Check your understanding

1. Why can GRANT EXECUTE on a package be preferable to granting UPDATE on every application table?

Show worked solution

The package can expose reviewed state transitions and validate their invariants while withholding arbitrary direct writes. Its implementation and authority still require review. Verify that the runtime account can perform the intended call and cannot bypass it through table permissions or an overly broad dynamic SQL parameter.

2. Does DBMS_ASSERT turn any valid table name into an authorized target?