Users vs Schemas, Object Ownership, Naming, Synonyms, and Namespace Semantics

Learning outcomes

ServiceHub already separates its schema owner from its runtime identity. The next migration task exposes a classic cross-engine trap: a developer treats an Oracle schema like a PostgreSQL or SQL Server namespace that can be created and assigned independently, then assumes that changing CURRENT_SCHEMA grants access. Oracle's model is different. This lesson builds the ownership and name-resolution map before later lessons create richer objects.

1. User, schema, and current schema are related—but not interchangeable

In Oracle, creating a database user creates an account that can own a schema with the same name. The schema is the namespace that contains objects owned by that user. The authenticated session identity and the default schema used for name resolution normally start aligned, but they are separate session properties. ALTER SESSION SET CURRENT_SCHEMA changes where Oracle first looks for unqualified schema objects; it does not log in as that owner and it does not add object privileges.

SELECT SYS_CONTEXT('USERENV','SESSION_USER')  AS session_user,       SYS_CONTEXT('USERENV','CURRENT_USER')  AS current_user,       SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schema,       SYS_CONTEXT('USERENV','CON_NAME')      AS con_nameFROM dual;SELECT USER AS sql_user FROM dual;

For an ordinary session these values are often identical, which is why the distinction is easy to miss. Stored definer-rights code, proxy/application identity mechanisms, and explicit CURRENT_SCHEMA changes can make the differences operationally important.

2. Ownership is visible in the data dictionary

Objects belong to a schema owner. The USER_ views describe objects owned by the current user, ALL_ views describe objects accessible to the current user, and appropriately privileged administrators can use DBA_ views for database-wide inventory. Do not infer ownership from an application's unqualified SQL text.

SELECT owner, object_name, object_typeFROM   all_objectsWHERE  owner = 'SERVICEHUB_OWNER'AND    object_name IN ('SERVICE_REGIONS','TECHNICIANS','WORK_ORDERS')ORDER BY object_name;SELECT owner, table_name, grantee, privilegeFROM   all_tab_privsWHERE  owner = 'SERVICEHUB_OWNER'AND    grantee = 'SERVICEHUB_APP'ORDER BY table_name, privilege;

The first query proves who owns the objects; the second proves which object privileges have been granted. Neither query says which name an application happens to use.

3. Name resolution and Oracle namespaces

Unquoted identifiers are normalized by Oracle, so work_orders, WORK_ORDERS, and Work_Orders normally resolve to the same unquoted name. Quoted identifiers preserve case and punctuation and must then be referenced exactly, including quotes. Quoted mixed-case names are legal but impose permanent quoting obligations on SQL, tools, migrations, and ORMs, so they should be deliberate rather than decorative.

Oracle also has namespaces. Tables, views, sequences, private synonyms, packages, standalone procedures/functions, user-defined types, and operators share a schema namespace; indexes and constraints have separate namespaces. This is why a table and index can share a name, while a table and sequence in the same schema cannot.

4. Synonyms are aliases, not authorization

A private synonym is a schema object owned by one schema. A public synonym is visible through the public synonym namespace. Both can hide an owner or location from application SQL, but neither grants access to its target. Authorization is still evaluated against the underlying object privileges. Public synonyms also enlarge the global naming surface and can mask ownership assumptions, so they are not a default least-privilege pattern.

-- As a PDB administrator, only for this disposable lab:GRANT CREATE SYNONYM TO servicehub_app;-- Connect as SERVICEHUB_APP to FREEPDB1:CREATE SYNONYM work_orders_api FOR servicehub_owner.work_orders;SELECT synonym_name, table_owner, table_nameFROM   user_synonymsWHERE  synonym_name = 'WORK_ORDERS_API';SELECT COUNT(*) FROM work_orders_api;-- Cleanup after the exercise:DROP SYNONYM work_orders_api;

The final query succeeds only because Chapter 01 granted SELECT on the underlying table. The synonym changes naming convenience, not authority. After the exercise, the PDB administrator can revoke CREATE SYNONYM again if it is not part of the intended runtime design.

5. Deliberately wrong approach: CURRENT_SCHEMA as a privilege shortcut

Connect as SERVICEHUB_APP and first revoke a lab-only privilege on a disposable owner table or use an owner object to which the runtime user has not been granted access. Changing the current schema does not make the runtime session the owner.

-- As SERVICEHUB_OWNER:CREATE TABLE ownership_probe (probe_id NUMBER PRIMARY KEY);-- As SERVICEHUB_APP:ALTER SESSION SET CURRENT_SCHEMA = servicehub_owner;SELECT SYS_CONTEXT('USERENV','SESSION_USER')   AS session_user,       SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schemaFROM dual;SELECT * FROM ownership_probe;-- Expected: ORA-00942 table or view does not exist (no SELECT privilege).-- Safe repair, as SERVICEHUB_OWNER:GRANT SELECT ON ownership_probe TO servicehub_app;-- Re-run as SERVICEHUB_APP; then cleanup as owner:-- DROP TABLE ownership_probe PURGE;

The important evidence is that SESSION_USER remains SERVICEHUB_APP. The correct repair is an explicit object privilege (or a governed API/view), not a session-name-resolution trick and certainly not a broad SELECT ANY TABLE grant.

6. Hands-on lab: build an ownership and namespace evidence card

Using the existing Free lab, capture one evidence card that another operator could use without guessing. Record the PDB, session user, current schema, the owner of the three ServiceHub base tables, runtime grants, and any private synonyms. Then temporarily set CURRENT_SCHEMA to SERVICEHUB_OWNER and prove that identity remains unchanged.

SELECT SYS_CONTEXT('USERENV','CON_NAME') AS con_name,       SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,       SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schemaFROM dual;SELECT owner, object_name, object_typeFROM all_objectsWHERE owner = 'SERVICEHUB_OWNER'AND object_name IN ('SERVICE_REGIONS','TECHNICIANS','WORK_ORDERS')ORDER BY object_name;SELECT owner, table_name, grantee, privilegeFROM all_tab_privsWHERE owner = 'SERVICEHUB_OWNER'AND grantee = 'SERVICEHUB_APP'ORDER BY table_name, privilege;SELECT synonym_name, table_owner, table_nameFROM user_synonymsORDER BY synonym_name;

Verification: you should be able to point to separate evidence for identity, name resolution, ownership, and privilege. If one query is unavailable because your runtime identity cannot see a dictionary view, record that limitation instead of escalating privilege solely for convenience.

7. Production judgment

Prefer explicit ownership and narrow grants as architectural facts. Use synonyms only when they provide a deliberate compatibility or location-transparency boundary, not to hide an unclear privilege model. Treat public synonyms as shared global names that require governance. Avoid quoted mixed-case object names unless interoperability requirements justify the ongoing quoting cost. For deployment tooling, always qualify administrative DDL with the intended owner and verify the current PDB before executing it.

No special 26ai option or management pack is required for these semantics. The key prerequisites are ordinary schema/object privileges in the correct PDB. The lesson does not change COMPATIBLE or initialization parameters.

8. Summary and next step

Oracle ties a user closely to its same-named schema, while session identity, current schema, object ownership, name resolution, synonyms, and privileges remain distinct mechanisms. You can now explain why changing CURRENT_SCHEMA does not grant access and why a synonym cannot replace authorization. Lesson 2 moves from object names to the type system that gives each column its storage and comparison semantics.

Authoritative references

• Schema object syntax and name resolution — current 26ai name-resolution behavior
• CREATE SYNONYM — private/public synonym semantics and prerequisites
• Managing schema objects — dictionary views and schema-object administration
• Privilege and role authorization — object privilege behavior including synonyms