What Is ORA-01720: ‘Grant Option Does Not Exist’ and How Do You Fix It?

The Oracle error ORA-01720: grant option does not exist occurs when you attempt to grant privileges on an object, but the user granting the privileges does not have the necessary GRANT OPTION privilege for that object. Let me explain this error with an example and its solution.

Scenario and Example

1. Setup Users and Objects: Suppose we have three users in an Oracle database:

• USER_A: The owner of a table EMPLOYEES.
• USER_B: A user to whom USER_A grants access to the EMPLOYEES table.
• USER_C: A user to whom USER_B tries to grant access to the EMPLOYEES table.

2. Granting Access Without GRANT OPTION:

• USER_A grants SELECT privilege on the EMPLOYEES table to USER_B without the GRANT OPTION:

CONNECT USER_A/password;
GRANT SELECT ON EMPLOYEES TO USER_B;

• Now, USER_B tries to grant SELECT privilege on the EMPLOYEES table to USER_C:

CONNECT USER_B/password;
GRANT SELECT ON USER_A.EMPLOYEES TO USER_C;

• This will throw the following error:

ORA-01720: grant option does not exist

The error occurs because USER_B does not have the GRANT OPTION privilege for the EMPLOYEES table.

Understanding the GRANT OPTION

The GRANT OPTION allows a user to pass on a privilege they have received to another user. Without the GRANT OPTION, a user cannot propagate privileges.

Solution

To resolve the error, ensure that the user granting the privilege has the GRANT OPTION. In this case, USER_A should grant SELECT privilege with the GRANT OPTION to USER_B. Here’s how:

1. Grant Privilege with GRANT OPTION:

CONNECT USER_A/password;
GRANT SELECT ON EMPLOYEES TO USER_B WITH GRANT OPTION;

2. Granting Privilege Further: Now, USER_B can grant the SELECT privilege to USER_C without encountering the error:

CONNECT USER_B/password;
GRANT SELECT ON USER_A.EMPLOYEES TO USER_C;

3. Verification: USER_C can now successfully query the EMPLOYEES table:

CONNECT USER_C/password;
SELECT * FROM USER_A.EMPLOYEES;

Key Points to Remember

1. Use the WITH GRANT OPTION clause to allow a user to pass on privileges.
2. Granting privileges with GRANT OPTION should be done cautiously to avoid unintended privilege escalation.
3. If GRANT OPTION is not explicitly needed, avoid granting it to adhere to the principle of least privilege.

Conclusion

The ORA-01720: grant option does not exist error highlights the importance of understanding privilege inheritance in Oracle. By ensuring that the appropriate privileges are granted with the GRANT OPTION when needed, you can effectively manage access control in your database.