WITH GRANT OPTION

GRANT lets you grant SYSTEM and OBJECT privileges to users and roles. This is a very powerful feature which should be used with very cautiously. This is a great feature to secure your data and database among db users. One small mistake can put your database into a security hole. With this article, we will talk about using GRANT features to secure and prevent unauthorized users accessing or manipulating data that they should not be. Often time, Database Administrators are required to audit roles, grants and privileges for security.

We all have used GRANT as a DBA or Developer to provide CRUD access on database objects or SYSTEM privilege like CREATE ANY SYNONYM to roles or users. Grant lets you use WITH ADMIN OR GRANT OPTION. These option should  never be used without the knowledge.

Using "with admin" and "with grant" options are considered Oracle dangerous because if they are not managed carefully you can have unintended side effects, resulting to a security hole. -- Burleson

You are hired as a Database consultant to design a database in a company. One of the components of the project requires you to create a complex view.  The view requires data from two schema (Sales and Finance). Sales own the View and occasionally the BI folks need to access the View to move data to their data warehouse for reporting and analytical purpose.

View SALES.MONTHLY_SALES_V
Query Uses SALES.MONTHLY and FINANCE.DEPT tables

GRANT:
To create a View on SALES:

GRANT SELECT ON finance.dept TO SALES; -- Run as FINANCE

Without the above GRANT, the view can’t be created on SALES schema. Also, BI Team occasionally needs to access view to move data to data warehouse. How would you give access to view to BI team for select?

GRANT SELECT on SALES.MONTHLY_SALES_V TO BI;

Error:

Error:
GRANT SELECT ON SALES.MONTHLY_SALES_V  TO BI
Error report -
SQL Error: ORA-01720: grant option does not exist for FINANCE.DEPT
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view or a view was being replaced
           and the grant option was not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view

The above statement is going to fail because BI doesn’t have access to the underlying view tables.
What is the error telling you? Does that make sense to you?

• BI doesn’t have access to view’s underlying tables
• SALES is trying to give access to BI which SALES don’t have permission WITH GRANT OPTION.

How are we going to solve this error? We are trying to give access to view to BI where SALES don’t have permission to give SELECT access on FINANCE.DEPT table. SALES will need WITH GRANT OPTION from FINANCE.

Let’s fix this using proper GRANT statement.

GRANT SELECT ON finance.dept TO SALES WITH GRANTS OPTION; -- Run as Finance
GRANT SELECT ON sales.monthly TO BI; Run as SALES, do you need this?
GRANT SELECT ON sales.monthly_sales_v TO BI; --Run as sales

WITH GRANT OPTION allows the recently granted user to grant these privileges to other users. I hope the above example makes it clear on how to use it. Also this is available only with object privileges and doesn’t work with SYSTEM privileges. Only the user who granted can revoke the access and the REVOKE will have cascading effect.

WITH ADMIN OPTION: It is similar to that of WITH GRANT OPTION but this only applies to SYSTEM privileges. As a remainder, I would not recommend using this feature unless you know what you are doing. There are other work around that are much secure and I encourage you to research them.