DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01720 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01720: grant option does not exist for

ORA-01720 occurs when a user attempts to grant privileges on an object (typically a view) to another user, but the grantor does not hold the required WITH GRANT OPTION on the underlying base objects. This error is especially common in multi-schema environments where views reference tables owned by a different schema. Without WITH GRANT OPTION on all base tables, the privilege chain cannot be extended to third parties.

Top 3 Causes

1. Missing GRANT OPTION on View's Base Table

When a view is created using a table from another schema, the view owner must have received WITH GRANT OPTION on that base table. If not, granting SELECT on the view to a third user will trigger ORA-01720.

-- SCHEMA_B owns the base table
-- SCHEMA_A creates a view on it

-- Problematic: B granted SELECT to A without GRANT OPTION
GRANT SELECT ON schema_b.employees TO schema_a;  -- No WITH GRANT OPTION

-- SCHEMA_A tries to grant SELECT on the view to SCHEMA_C
-- This will raise ORA-01720
GRANT SELECT ON schema_a.emp_view TO schema_c;

-- Check GRANTABLE column to diagnose
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE = 'SCHEMA_A'
  AND  TABLE_NAME = 'EMPLOYEES';
-- GRANTABLE = 'NO' confirms the problem
Enter fullscreen mode Exit fullscreen mode

2. Re-granting Privileges Received Without GRANT OPTION

Any privilege granted without WITH GRANT OPTION is non-transferable. If a user received SELECT on a table directly and tries to pass it along, Oracle blocks it with ORA-01720.

-- DBA grants without WITH GRANT OPTION
GRANT SELECT ON hr.departments TO app_user;

-- app_user attempts to re-grant — this fails with ORA-01720
GRANT SELECT ON hr.departments TO report_user;

-- Fix: DBA must revoke and re-grant with GRANT OPTION
REVOKE SELECT ON hr.departments FROM app_user;
GRANT SELECT ON hr.departments TO app_user WITH GRANT OPTION;

-- Now app_user can successfully grant to others
GRANT SELECT ON hr.departments TO report_user;
Enter fullscreen mode Exit fullscreen mode

3. Synonym-Based View With Indirect Object References

When a view references objects through a synonym, the privilege check still applies to the actual base object — not the synonym. If WITH GRANT OPTION is absent on the real table, ORA-01720 will occur.

-- Find base objects referenced by the view
SELECT NAME,
       REFERENCED_OWNER,
       REFERENCED_NAME,
       REFERENCED_TYPE
FROM   DBA_DEPENDENCIES
WHERE  NAME  = 'EMP_VIEW'
  AND  OWNER = 'SCHEMA_A'
  AND  REFERENCED_TYPE IN ('TABLE', 'SYNONYM');

-- Verify GRANT OPTION on all referenced objects
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE = 'SCHEMA_A'
  AND  TABLE_NAME IN (
         SELECT REFERENCED_NAME
         FROM DBA_DEPENDENCIES
         WHERE NAME = 'EMP_VIEW' AND OWNER = 'SCHEMA_A'
       );
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Identify the broken privilege chain:

-- Find views missing GRANT OPTION on their base tables
SELECT D.OWNER, D.NAME AS VIEW_NAME,
       D.REFERENCED_OWNER, D.REFERENCED_NAME,
       NVL(P.GRANTABLE, 'MISSING') AS STATUS
FROM   DBA_DEPENDENCIES D
LEFT JOIN DBA_TAB_PRIVS P
       ON  P.GRANTEE    = D.OWNER
       AND P.OWNER      = D.REFERENCED_OWNER
       AND P.TABLE_NAME = D.REFERENCED_NAME
       AND P.PRIVILEGE  = 'SELECT'
WHERE  D.TYPE = 'VIEW'
  AND  D.REFERENCED_TYPE = 'TABLE'
  AND  NVL(P.GRANTABLE, 'NO') = 'NO';
Enter fullscreen mode Exit fullscreen mode

Step 2 — Fix by re-granting with GRANT OPTION:

-- Base table owner executes this
REVOKE SELECT ON schema_b.employees FROM schema_a;
GRANT SELECT ON schema_b.employees TO schema_a WITH GRANT OPTION;

-- View owner can now propagate the privilege
GRANT SELECT ON schema_a.emp_view TO schema_c;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always document and enforce GRANT OPTION requirements at design time.
Before deploying any cross-schema view, confirm that all base table owners have granted privileges WITH GRANT OPTION. Maintain a privilege matrix and run a monthly audit script to detect missing GRANT OPTIONs before they cause runtime errors.

2. Use Role-based access control to reduce direct GRANT complexity.
Rather than building long privilege chains between individual users, centralize permissions through roles. This minimizes the need for WITH GRANT OPTION and makes privilege management far easier to audit and maintain. Note that role-based privileges are not active inside stored procedures or views — for those contexts, direct grants with WITH GRANT OPTION remain necessary.

Related errors: **ORA-01031* (insufficient privileges during object access), ORA-01749 (cannot grant privileges to yourself during privilege restructuring).*

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)