Oracle - Grant Select Permissions on View to another Schema
grant option does not exist for ‘Employee.EmployeeInfo’
Today, while working, I faced some issue with granting access on one schema view to another schema in the Oracle database. My view using different tables from different schemas, because of this, I am not able to give access on view to another schema.
Let’s discuss with a simple example. For example, I have three schemas Employee, Manager, CEO in my oracle database CompanyDB.
Under Employee schema, I have a table called EmployeeInfo. In Manager schema, I want to create a view to fetch all employees info under Manager schema, as shown below.
CREATE OR REPLACE FORCE VIEW Manager.VW_EmployeeInfo
(
EmployeeId,
EmployeeName,
EmployeeLocation,
EmployeeSalary
)
AS
SELECT * FROM Employee.EmployeeInfo;
My view Manager.VW_EmployeeInfo view got created but with some errors because Manager schema does not have any permissions to access EmployeeInfo table from Employee schema. So, let’s provide select access on EmployeeInfo table to Manager schema by running below query under Employee schema.
GRANT SELECT ON EMPLOYEE.EmployeeInfo TO MANAGER;
Now compile the view VW_EmployeeInfo under Manager schema, you can see view compiled without any errors.
Let’s give select access on this view to CEO schema by executing the same Grant query as below under Manager schema.
GRANT SELECT ON Manager.VW_EmployeeInfo TO CEO;
Here you get an error as “grant option does not exist for ‘Employee.EmployeeInfo’”
We get this error because of view Manager.VW_EmployeeInfo using Employee.EmployeeInfo table and Manager schema do not have any Grant permissions on Employee.EmployeeInfo table, it has only select permissions. To allow Manager schema to provide select permissions on VW_EmployeeInfo view to CEO schema, it should have permissions on Employee.EmployeeInfo table. We can grant these permissions to Manager schema on Employee.EmployeeInfo table by using the below query.
GRANT SELECT ON EMPLOYEE.EmployeeInfo TO MANAGER WITH GRANT OPTION;
We have to execute this query under Employee schema. If you compare the above query with previous grant queries, you can observe we are providing the select & grant permissions by using WITH GRANT OPTION command.
Now we can successfully provide the select permissions to CEO schema on Manager.VW_EmployeeInfo view.
