ORA-00942 — 23 Procedures Fail After Synonym Drop

-- Demonstrate Oracle name resolution order
-- Run as user APP_USER querying the ORDERS table

-- Step 1: Check for a table or view in the current schema
SELECT object_name, object_type
FROM user_objects
WHERE object_name = 'ORDERS'
  AND object_type IN ('TABLE', 'VIEW');
-- If found: resolves to current_schema.ORDERS

-- Step 2: Check for a private synonym in the current schema
SELECT synonym_name, table_owner, table_name
FROM user_synonyms
WHERE synonym_name = 'ORDERS';
-- If found: resolves to table_owner.table_name

-- Step 3: Check for a public synonym
SELECT synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name = 'ORDERS'
  AND owner = 'PUBLIC';
-- If found: resolves to table_owner.table_name

-- Step 4: Check across all accessible schemas
SELECT owner, object_name, object_type
FROM all_objects
WHERE object_name = 'ORDERS'
  AND object_type IN ('TABLE', 'VIEW')
ORDER BY owner;
-- Shows every schema that contains an ORDERS table/view

-- Step 5: Check privileges on the resolved object
SELECT grantor, grantee, privilege, grantable
FROM all_tab_privs
WHERE table_name = 'ORDERS'
ORDER BY grantee, privilege;
-- Shows who holds SELECT, INSERT, UPDATE, DELETE on ORDERS

-- Step 6: Trace the full resolution path in one query
SELECT
  'Local Object' AS resolution_type,
  object_name    AS name,
  SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || '.' || object_name AS resolves_to
FROM user_objects
WHERE object_name = 'ORDERS'
  AND object_type IN ('TABLE', 'VIEW')
UNION ALL
SELECT
  'Private Synonym',
  synonym_name,
  table_owner || '.' || table_name
FROM user_synonyms
WHERE synonym_name = 'ORDERS'
UNION ALL
SELECT
  'Public Synonym',
  synonym_name,
  table_owner || '.' || table_name
FROM all_synonyms
WHERE synonym_name = 'ORDERS'
  AND owner = 'PUBLIC';
-- The first row returned is the resolution Oracle would use

-- Diagnostic 1: Find all public synonyms actively used by application objects
-- Run this BEFORE any synonym cleanup or decommission script
SELECT DISTINCT
  d.referenced_name  AS synonym_or_table_name,
  d.referenced_owner AS target_schema,
  d.owner            AS dependent_schema,
  d.name             AS dependent_object,
  d.type             AS dependent_type
FROM dba_dependencies d
WHERE d.referenced_type IN ('TABLE', 'VIEW')
  AND d.owner IN ('APP_SCHEMA1', 'APP_SCHEMA2', 'APP_SCHEMA3')
  AND d.referenced_name IN (
    SELECT synonym_name
    FROM dba_synonyms
    WHERE owner = 'PUBLIC'
  )
ORDER BY d.referenced_name, d.owner;

-- Diagnostic 2: Check if a specific synonym exists and where it points
SELECT
  owner          AS synonym_owner,
  synonym_name,
  table_owner    AS target_owner,
  table_name     AS target_object,
  db_link
FROM all_synonyms
WHERE synonym_name = 'ORDERS'
ORDER BY DECODE(owner, 'PUBLIC', 2, 1), owner;
-- Private synonyms appear first, public synonyms second

-- Diagnostic 3: Find every PL/SQL object that depends on a synonym
SELECT
  owner,
  name,
  type,
  referenced_name,
  referenced_type
FROM dba_dependencies
WHERE referenced_name = 'ORDERS'
  AND referenced_type IN ('SYNONYM', 'TABLE', 'VIEW')
ORDER BY owner, type, name;

-- Diagnostic 4: Recreate a dropped public synonym
CREATE OR REPLACE PUBLIC SYNONYM orders
FOR owner.orders;
-- All procedures that reference 'ORDERS' without qualification
-- will resolve again immediately — no recompilation required

-- Diagnostic 5: Find all unqualified table references in PL/SQL source
-- These references are vulnerable to synonym drops
SELECT
  owner,
  name,
  type,
  line,
  TRIM(text) AS source_line
FROM all_source
WHERE UPPER(text) LIKE '%FROM ORDERS%'
   OR UPPER(text) LIKE '%INTO ORDERS%'
   OR UPPER(text) LIKE '%UPDATE ORDERS%'
   OR UPPER(text) LIKE '%DELETE FROM ORDERS%'
ORDER BY owner, name, line;
-- Review results: any line that does NOT include 'OWNER.ORDERS'
-- is relying on a synonym and is vulnerable

-- Diagnostic 6: Verify the target object exists and is VALID
SELECT owner, object_name, object_type, status, last_ddl_time
FROM all_objects
WHERE object_name = 'ORDERS'
  AND object_type IN ('TABLE', 'VIEW', 'SYNONYM')
ORDER BY owner, object_type;

-- Diagnostic 1: Verify the object exists (run as DBA)
SELECT owner, object_name, object_type, status, last_ddl_time
FROM dba_objects
WHERE object_name = 'ORDERS'
  AND object_type IN ('TABLE', 'VIEW');
-- If rows returned: the object exists — problem is privilege or synonym
-- If no rows: the object was dropped or never created

-- Diagnostic 2: Check direct grants on the object
SELECT grantor, grantee, privilege, grantable
FROM dba_tab_privs
WHERE table_name = 'ORDERS'
ORDER BY grantee, privilege;
-- Look for the executing user or the procedure owner in the grantee column

-- Diagnostic 3: Check if the user can see the object via ALL_OBJECTS
-- Run this connected AS the application user, not as DBA
SELECT owner, object_name, object_type
FROM all_objects
WHERE object_name = 'ORDERS'
  AND object_type IN ('TABLE', 'VIEW');
-- No rows: the user cannot see the object (privilege or synonym issue)
-- Rows returned: the user can see it (problem is elsewhere)

-- Diagnostic 4: Check role-based grants
-- These work for direct SQL and invoker's rights PL/SQL
-- but NOT for definer's rights PL/SQL
SELECT
  r.role,
  rp.table_name,
  rp.privilege,
  rp.grantable
FROM dba_role_privs r
JOIN role_tab_privs rp ON rp.role = r.granted_role
WHERE r.grantee = 'APP_USER'
  AND rp.table_name = 'ORDERS'
ORDER BY r.role, rp.privilege;

-- Diagnostic 5: Check the AUTHID of the failing procedure
SELECT
  owner,
  object_name,
  authid
FROM all_procedures
WHERE object_name = 'GET_ORDERS'
  AND owner = 'APP_SCHEMA';
-- DEFINER  = owner's privileges apply (roles ignored)
-- CURRENT_USER = caller's privileges apply (roles active)

-- Fix 1: Direct grant to the schema owner (works for definer's rights)
GRANT SELECT ON owner.orders TO app_schema;

-- Fix 2: Direct grant to the calling user (works for invoker's rights)
GRANT SELECT ON owner.orders TO app_user;

-- Fix 3: Switch procedure to invoker's rights if role-based grants must be used
CREATE OR REPLACE PROCEDURE app_schema.get_orders(
  p_order_id IN NUMBER
)
  AUTHID CURRENT_USER  -- Caller's privileges, roles active
IS
  v_rec owner.orders%ROWTYPE;
BEGIN
  SELECT * INTO v_rec
  FROM owner.orders  -- Fully qualified for safety
  WHERE order_id = p_order_id;
END get_orders;
/

-- Pattern 1: Fully qualified names in PL/SQL
-- Eliminates synonym dependency entirely
CREATE OR REPLACE PROCEDURE app_schema.get_order(
  p_order_id IN NUMBER,
  p_order    OUT data_owner.orders%ROWTYPE
) IS
BEGIN
  SELECT *
  INTO p_order
  FROM data_owner.orders  -- Fully qualified: schema.table
  WHERE order_id = p_order_id;
  -- Works regardless of synonym state
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20010, 'Order not found: ' || p_order_id);
END get_order;
/

-- Pattern 2: Centralized table name constants for dynamic SQL
CREATE OR REPLACE PACKAGE app_schema.table_ref AS
  -- Single source of truth for all schema-qualified table names
  c_orders     CONSTANT VARCHAR2(128) := 'DATA_OWNER.ORDERS';
  c_customers  CONSTANT VARCHAR2(128) := 'DATA_OWNER.CUSTOMERS';
  c_payments   CONSTANT VARCHAR2(128) := 'DATA_OWNER.PAYMENTS';
  c_audit_log  CONSTANT VARCHAR2(128) := 'DATA_OWNER.AUDIT_LOG';
  -- To migrate schemas, change the constants here and recompile
END table_ref;
/

-- Pattern 3: Using the constant in dynamic SQL
CREATE OR REPLACE PROCEDURE app_schema.count_by_status(
  p_status IN VARCHAR2,
  p_count  OUT NUMBER
) IS
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM '
    || app_schema.table_ref.c_orders
    || ' WHERE status = :b_status';

  EXECUTE IMMEDIATE v_sql INTO p_count USING p_status;
END count_by_status;
/

-- Pattern 4: Views with fully qualified names
CREATE OR REPLACE VIEW app_schema.active_orders_v AS
SELECT
  o.order_id,
  o.customer_id,
  o.amount,
  o.status,
  c.customer_name
FROM data_owner.orders o       -- Fully qualified
JOIN data_owner.customers c    -- Fully qualified
  ON c.customer_id = o.customer_id
WHERE o.status = 'ACTIVE';
-- This view remains VALID regardless of synonym state
/

-- Pattern 5: Find all unqualified table references in a schema
-- Use this to plan an incremental migration to qualified names
SELECT
  owner,
  name,
  type,
  line,
  TRIM(text) AS source_line
FROM all_source
WHERE owner = 'APP_SCHEMA'
  AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
  AND (
       REGEXP_LIKE(UPPER(text), '\bFROM\s+[A-Z_][A-Z0-9_]*\b')
    OR REGEXP_LIKE(UPPER(text), '\bJOIN\s+[A-Z_][A-Z0-9_]*\b')
    OR REGEXP_LIKE(UPPER(text), '\bINTO\s+[A-Z_][A-Z0-9_]*\b')
    OR REGEXP_LIKE(UPPER(text), '\bUPDATE\s+[A-Z_][A-Z0-9_]*\b')
    OR REGEXP_LIKE(UPPER(text), '\bDELETE\s+FROM\s+[A-Z_][A-Z0-9_]*\b')
  )
  AND NOT REGEXP_LIKE(UPPER(text), '\b[A-Z_][A-Z0-9_]*\.[A-Z_][A-Z0-9_]*\b')
ORDER BY owner, name, line;
-- Each row is an unqualified reference that depends on a synonym

-- WRONG: Dynamic SQL with unqualified name and no logging
CREATE OR REPLACE PROCEDURE app_schema.broken_dynamic(
  p_status IN VARCHAR2
) IS
  v_sql   VARCHAR2(4000);
  v_count NUMBER;
BEGIN
  -- Problem 1: unqualified name — depends on synonym
  -- Problem 2: no logging — impossible to debug
  -- Problem 3: string concatenation for value — SQL injection risk
  v_sql := 'SELECT COUNT(*) FROM ORDERS WHERE status = ''' || p_status || '''';
  EXECUTE IMMEDIATE v_sql INTO v_count;
END broken_dynamic;
/

-- CORRECT: Dynamic SQL with qualified name, logging, and bind variables
CREATE OR REPLACE PROCEDURE app_schema.safe_dynamic(
  p_status IN VARCHAR2
) IS
  v_sql   VARCHAR2(4000);
  v_count NUMBER;
BEGIN
  -- Fully qualified name — no synonym dependency
  -- Bind variable — no SQL injection, no quoting errors
  v_sql := 'SELECT COUNT(*) FROM data_owner.orders WHERE status = :b_status';

  -- Log the SQL before execution — critical for ORA-00942 diagnosis
  DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);

  EXECUTE IMMEDIATE v_sql INTO v_count USING p_status;

  DBMS_OUTPUT.PUT_LINE('Result: ' || v_count);
EXCEPTION
  WHEN OTHERS THEN
    -- Log the failed SQL with the error — the only way to diagnose runtime failures
    DBMS_OUTPUT.PUT_LINE('FAILED SQL: ' || v_sql);
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
    RAISE;  -- Re-raise after logging
END safe_dynamic;
/

-- CORRECT: Dynamic SQL with privilege context awareness
CREATE OR REPLACE PROCEDURE app_schema.privilege_aware_dynamic
  AUTHID CURRENT_USER  -- Uses caller's privileges
IS
  v_sql   VARCHAR2(4000);
  v_count NUMBER;
BEGIN
  -- AUTHID CURRENT_USER: EXECUTE IMMEDIATE uses the CALLER's privileges
  -- If the caller lacks SELECT on data_owner.orders: ORA-00942
  v_sql := 'SELECT COUNT(*) FROM data_owner.orders WHERE status = :b_status';

  BEGIN
    EXECUTE IMMEDIATE v_sql INTO v_count USING 'ACTIVE';
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(
        -20001,
        'Dynamic SQL failed. '
        || 'SQL: ' || v_sql
        || ' | Error: ' || SQLERRM
        || ' | Session User: ' || SYS_CONTEXT('USERENV', 'SESSION_USER')
        || ' | Note: AUTHID CURRENT_USER means caller privileges apply',
        TRUE  -- Preserve the original error stack
      );
  END;
END privilege_aware_dynamic;
/

-- Check 1: Pre-deployment dependency validation
-- Run before every deployment to verify all references resolve
CREATE OR REPLACE PROCEDURE app_schema.check_dependencies(
  p_schema IN VARCHAR2
) IS
  v_missing_count NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT
      d.owner,
      d.name,
      d.type,
      d.referenced_name,
      d.referenced_type,
      d.referenced_owner
    FROM dba_dependencies d
    WHERE d.owner = UPPER(p_schema)
      AND d.referenced_owner NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
      AND NOT EXISTS (
        SELECT 1
        FROM dba_objects o
        WHERE o.owner       = d.referenced_owner
          AND o.object_name = d.referenced_name
          AND o.object_type = d.referenced_type
          AND o.status      = 'VALID'
      )
    ORDER BY d.name, d.referenced_name
  ) LOOP
    v_missing_count := v_missing_count + 1;
    DBMS_OUTPUT.PUT_LINE(
      'BROKEN REF: ' || rec.type || ' ' || rec.owner || '.' || rec.name
      || ' -> ' || rec.referenced_type
      || ' ' || rec.referenced_owner || '.' || rec.referenced_name
    );
  END LOOP;

  IF v_missing_count > 0 THEN
    RAISE_APPLICATION_ERROR(
      -20070,
      'Deployment blocked: ' || v_missing_count
      || ' broken object references in schema ' || p_schema
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE('All dependencies valid in schema ' || p_schema);
  END IF;
END check_dependencies;
/

-- Check 2: Daily synonym integrity check
-- Detects synonyms that point to non-existent or invalid objects
CREATE OR REPLACE PROCEDURE app_schema.check_synonyms IS
  v_broken_count NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT
      s.owner        AS synonym_owner,
      s.synonym_name,
      s.table_owner  AS target_owner,
      s.table_name   AS target_object
    FROM dba_synonyms s
    WHERE s.owner IN ('PUBLIC', 'APP_SCHEMA', 'APP_SCHEMA2', 'APP_SCHEMA3')
      AND s.db_link IS NULL  -- Skip DB link synonyms for this check
      AND NOT EXISTS (
        SELECT 1
        FROM dba_objects o
        WHERE o.owner       = s.table_owner
          AND o.object_name = s.table_name
          AND o.status      = 'VALID'
      )
    ORDER BY s.owner, s.synonym_name
  ) LOOP
    v_broken_count := v_broken_count + 1;
    DBMS_OUTPUT.PUT_LINE(
      'BROKEN SYNONYM: ' || rec.synonym_owner || '.' || rec.synonym_name
      || ' -> ' || rec.target_owner || '.' || rec.target_object || ' (missing or invalid)'
    );
  END LOOP;

  IF v_broken_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE(v_broken_count || ' broken synonym(s) detected — review and fix before business hours');
  ELSE
    DBMS_OUTPUT.PUT_LINE('All synonyms valid');
  END IF;
END check_synonyms;
/

-- Check 3: Synonym drop impact analysis
-- Run BEFORE any DROP SYNONYM command to measure blast radius
CREATE OR REPLACE PROCEDURE app_schema.analyze_synonym_drop(
  p_synonym_name IN VARCHAR2
) IS
  v_reference_count NUMBER := 0;
BEGIN
  -- Count source code references across all application schemas
  SELECT COUNT(*) INTO v_reference_count
  FROM all_source
  WHERE REGEXP_LIKE(UPPER(text), '\b' || UPPER(p_synonym_name) || '\b')
    AND owner NOT IN ('SYS', 'SYSTEM')
    AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'VIEW');

  IF v_reference_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE(
      'WARNING: ' || v_reference_count
      || ' source references found for synonym ' || UPPER(p_synonym_name)
    );
    DBMS_OUTPUT.PUT_LINE(
      'Dropping this synonym WILL cause ORA-00942 in the following objects:'
    );

    FOR rec IN (
      SELECT DISTINCT owner, name, type
      FROM all_source
      WHERE REGEXP_LIKE(UPPER(text), '\b' || UPPER(p_synonym_name) || '\b')
        AND owner NOT IN ('SYS', 'SYSTEM')
        AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'VIEW')
      ORDER BY owner, type, name
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('  ' || rec.type || ' ' || rec.owner || '.' || rec.name);
    END LOOP;

    RAISE_APPLICATION_ERROR(
      -20072,
      'Drop blocked: ' || v_reference_count
      || ' objects reference synonym ' || UPPER(p_synonym_name)
      || '. Update references to qualified names before dropping.'
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE(
      'No references found for synonym ' || UPPER(p_synonym_name) || '. Safe to drop.'
    );
  END IF;
END analyze_synonym_drop;
/

-- Schedule the daily checks using DBMS_SCHEDULER
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'APP_SCHEMA.DAILY_SYNONYM_CHECK',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN app_schema.check_synonyms; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=6; BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Daily check for broken synonyms — prevents ORA-00942 at runtime'
  );
END;
/