Error – Select with Grant Option required

"An owner referenced by user does not have SELECT WITH GRANT OPTION access to DB_Name.Table_Name.Col_Name ". This is very common error related with privileges granted to objects in Teradata. So first thing you must understand is this error related with insufficient privileges. In this post, we will see how this error can come up while working with views on Teradata.

Whenever we create VIEWS in Teradata on top of base Tables, we must grant permission to VIEW database on Table database. This is required because VIEW has to access underlying TABLE and if does not have proper permissions it won't be able to access it. This is one time activity and is required at time of creation of VIEW database however if you are creating new view in the same database which access tables in other database then you may have to Grant permission again to new table database.

Let's see this with an example:

Step 1: Create a Table in database "db_company"

CREATE TABLE db_company.employee ( emp_id int, emp_name varchar(30), emp_dob date, emp_salary int) primary index(emp_id);

Step2: Create a VIEW in database "vw_company". This view will access employee table in "db_company" database.

CREATE VIEW vw_company.employee_v (emp_id,emp_name,emp_dob,emp_salary) AS select emp_id,emp_name,emp_dob,emp_salary from db_company.employee;

Now when you will run "select * from vw_company.employee_v", you will get the error. "An owner referenced by user does not have SELECT WITH GRANT OPTION access to db_company.employee.emp_id"
To overcome this error you need to run below command:

GRANT SELECT ON db_company TO vw_company WITH GRANT OPTION;

Now you will be able to access records via view. This error may come in Stored Procedure as well. And now you know how to fix this.

Leave a Reply

Your email address will not be published. Required fields are marked *