Design Server-Side APIs with Packages, Privileges, Transaction Boundaries, and Deployment Discipline

Learning outcomes

ServiceHub's runtime account currently has direct SELECT/INSERT/UPDATE/DELETE privileges on owner tables because “the application needs the data.” As a result, every code path can bypass validation, audit context, and state transitions. The safer database API pattern keeps base tables private and grants the application EXECUTE on a narrow package whose privilege model, transaction ownership, version contract, and deployment rollback are explicit.

1. A package API is a privilege boundary

With definer's-rights code, a runtime account can execute a package operation without receiving direct privileges on the underlying table. The package owner becomes responsible for restricting which columns/rows/actions are exposed. Oracle's Security Guide specifically presents definer-rights procedures as a way to give users controlled access to private objects through EXECUTE.

CREATE TABLE servicehub_api_case(  case_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  external_ref VARCHAR2(40) NOT NULL UNIQUE,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL,  created_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,  CONSTRAINT sh12_api_status_ck    CHECK(status_code IN ('OPEN','HOLD','CLOSED')));

2. Expose business verbs, not unrestricted CRUD

CREATE OR REPLACE PACKAGE servicehub_work_apiAUTHID DEFINERAS  FUNCTION api_version RETURN VARCHAR2;  PROCEDURE create_case(    p_external_ref IN VARCHAR2,    p_amount       IN NUMBER,    p_case_id      OUT NUMBER  );  PROCEDURE set_status(    p_case_id IN NUMBER,    p_status  IN VARCHAR2  );  FUNCTION get_status(    p_case_id IN NUMBER  ) RETURN VARCHAR2;END servicehub_work_api;/

CREATE OR REPLACE PACKAGE BODY servicehub_work_api AS  FUNCTION api_version RETURN VARCHAR2 IS  BEGIN    RETURN '1.0.0';  END;  PROCEDURE create_case(    p_external_ref IN VARCHAR2,    p_amount       IN NUMBER,    p_case_id      OUT NUMBER  ) IS  BEGIN    IF p_amount IS NULL OR p_amount <= 0 THEN      RAISE_APPLICATION_ERROR(-20410,'amount must be positive');    END IF;    INSERT INTO servicehub_api_case(external_ref,status_code,amount)    VALUES(UPPER(TRIM(p_external_ref)),'OPEN',p_amount)    RETURNING case_id INTO p_case_id;  END;  PROCEDURE set_status(p_case_id IN NUMBER,p_status IN VARCHAR2) IS    l_status VARCHAR2(12) := UPPER(TRIM(p_status));  BEGIN    IF l_status NOT IN ('OPEN','HOLD','CLOSED') THEN      RAISE_APPLICATION_ERROR(-20411,'invalid status transition value');    END IF;    UPDATE servicehub_api_case    SET status_code=l_status    WHERE case_id=p_case_id;    IF SQL%ROWCOUNT=0 THEN      RAISE_APPLICATION_ERROR(-20412,'unknown case_id');    END IF;  END;  FUNCTION get_status(p_case_id IN NUMBER) RETURN VARCHAR2 IS    l_status servicehub_api_case.status_code%TYPE;  BEGIN    SELECT status_code INTO l_status    FROM servicehub_api_case    WHERE case_id=p_case_id;    RETURN l_status;  EXCEPTION    WHEN NO_DATA_FOUND THEN      RAISE_APPLICATION_ERROR(-20412,'unknown case_id');  END;END servicehub_work_api;/

The specification is the client contract; body changes can preserve that contract. The version function is implemented in the body so changing the reported implementation version does not require changing a public constant declaration.

3. Definer rights and invoker rights solve different security problems

AUTHID DEFINER is the default. It is appropriate for a narrow API that intentionally lets callers perform controlled operations using the owner's privileges. If definer-rights code references objects in another schema, required object privileges must be granted directly to the owner rather than only through roles.

AUTHID CURRENT_USER is appropriate when the operation should use the caller's object resolution/privileges. Oracle also protects invoker-rights execution with INHERIT PRIVILEGES/INHERIT ANY PRIVILEGES: a hardened site can revoke default inheritance and explicitly allow only trusted invoker-rights owners.

4. Remove direct table access and grant only EXECUTE

The course already established SERVICEHUB_APP as a runtime identity. For this new table, first inspect whether any legacy direct grants actually exist; revoke only grants that are present as part of a deliberate migration, then grant the package capability.

-- As SERVICEHUB_OWNER (or an administrator where required):SELECT grantee,owner,table_name,privilegeFROM all_tab_privsWHERE grantee='SERVICEHUB_APP'  AND owner=USER  AND table_name='SERVICEHUB_API_CASE'ORDER BY privilege;-- If legacy direct grants are returned above, revoke exactly those grants.-- Example migration only when SELECT/INSERT/UPDATE/DELETE are actually present:-- REVOKE SELECT,INSERT,UPDATE,DELETE-- ON servicehub_api_case FROM servicehub_app;GRANT EXECUTE ON servicehub_work_api TO servicehub_app;

This keeps the mandatory lab rerunnable: a REVOKE for a privilege that was never granted can fail. Production deployment scripts should query current grants and make privilege migrations explicit rather than suppressing every error.

SELECT grantee,owner,table_name,privilegeFROM all_tab_privsWHERE grantee='SERVICEHUB_APP'  AND owner=USER  AND table_name IN ('SERVICEHUB_API_CASE','SERVICEHUB_WORK_API')ORDER BY table_name,privilege;

5. Caller-owned transactions make composition possible

The API body deliberately does not commit. A web request may need to call two packages and either commit both or roll back both. Hidden commits make that composition impossible.

-- Connect as SERVICEHUB_APP.VARIABLE new_case NUMBEREXEC servicehub_owner.servicehub_work_api.create_case('EXT-9001',250,:new_case);PRINT new_caseSELECT servicehub_owner.servicehub_work_api.get_status(:new_case)AS status_before_rollbackFROM dual;-- OPENROLLBACK;BEGIN  DBMS_OUTPUT.PUT_LINE(    servicehub_owner.servicehub_work_api.get_status(:new_case)  );END;/-- Expected application error -20412 because the insert was rolled back.

Background Scheduler jobs can own their transaction; request APIs usually should not. State the transaction owner in the API documentation.

6. Deliberately wrong: grant table CRUD and hope clients voluntarily use the package

If SERVICEHUB_APP keeps direct table DML, it can bypass the package validation and future policy. The package becomes a convenience library rather than a security boundary.

SELECT COUNT(*)FROM servicehub_owner.servicehub_api_case;-- With no SELECT grant, direct access should fail (commonly ORA-00942).UPDATE servicehub_owner.servicehub_api_caseSET status_code='CLOSED';-- Direct DML should fail without the object privilege.

The package call can still succeed because definer-rights code accesses the owner's table through its controlled program unit.

7. Deployment is DDL: validate before routing traffic

CREATE OR REPLACE PACKAGE is DDL and is not transactional deployment in the application sense. Capture the previous tested artifact before deployment. After compiling, verify status/errors, signatures, dependent objects, and grants before directing traffic to the new code.

SELECT object_name,object_type,status,editionable,last_ddl_timeFROM user_objectsWHERE object_name='SERVICEHUB_WORK_API'ORDER BY object_type;SELECT name,type,line,position,textFROM user_errorsWHERE name='SERVICEHUB_WORK_API'ORDER BY type,sequence;SELECT package_name,object_name,argument_name,position,       in_out,data_type,defaultedFROM user_argumentsWHERE package_name='SERVICEHUB_WORK_API'ORDER BY object_name,overload,sequence;SELECT referenced_name,referenced_type,dependency_typeFROM user_dependenciesWHERE name='SERVICEHUB_WORK_API'ORDER BY referenced_type,referenced_name;

A rollback is normally “redeploy the previously tested package spec/body and grants,” not ROLLBACK. Edition-Based Redefinition can provide richer coexistence/rollback architecture, but it is not assumed or configured in this mandatory lab.

8. Versioning and compatibility discipline

Treat the package specification like an API schema. Additive overloads can still create ambiguity; removing/renaming parameters breaks callers; changing default behavior can be a semantic breaking change even if compilation succeeds. Keep API tests in source control and validate both old and new application callers during staged deployment.

SELECT servicehub_owner.servicehub_work_api.api_version AS api_versionFROM dual;SELECT owner,object_name,procedure_name,authidFROM all_proceduresWHERE owner='SERVICEHUB_OWNER'  AND object_name='SERVICEHUB_WORK_API'ORDER BY procedure_name;

9. Reproducible owner cleanup

-- As SERVICEHUB_OWNER:REVOKE EXECUTE ON servicehub_work_api FROM servicehub_app;DROP PACKAGE servicehub_work_api;DROP TABLE servicehub_api_case PURGE;

Do not drop the shared course runtime account. It belongs to the cross-chapter environment established earlier.

10. Production judgment and chapter bridge

A package API is appropriate when the database should own validated business operations, privilege mediation, or reusable transactional behavior. Keep the spec stable, grants narrow, body side effects explicit, and transaction control caller-owned unless the unit is independently scheduled/autonomous by design. Audit privilege changes and test negative access paths.

No paid option, management pack, restart, or COMPATIBLE change is required. This closes the advanced PL/SQL/API chapter. Chapter 13 moves to multitenant architecture—CDB root, PDBs, common/local identities, services, cloning, portability, resource/isolation boundaries, and lifecycle operations—where package/API privileges and Scheduler state must be understood in container scope.

Authoritative references

• Database Security Guide — definer/invoker rights, direct grants and controlled API access
• Invoker Rights and Definer Rights — AUTHID semantics and inheritance
• CREATE PACKAGE — package privileges/editionability and API specification
• CREATE PACKAGE BODY — implementation replacement semantics
• Dependencies Among Schema Objects — dependency/invalidation deployment behavior