Skip to content

Teradata – Database to database grants for views

SCENARIO:

We have a database DB1_t which have all the tables and a database DB2_v which have views pointing to database DB1_t.
Now if we have a new database DB3_v which will have views poniting to database DB2_v, do we need to provide database to database access to DB3_v on DB1_t with grant option?

TEST:

Create Database DB1_t From dbc As Perm=1e10, No Fallback, No Journal;
Create Database DB2_v From dbc As Perm=1e9, No Fallback, No Journal;
Create Database DB3_v From dbc As Perm=1e9, No Fallback, No Journal;

create table DB1_t.T1 (Counter number, Descr char(10));
insert into DB1_t.T1 values (1,’a’);
insert into DB1_t.T1 values (2,’b’);

Select * from DB1_t.T1;

REPLACE VIEW DB2_v.V1 AS LOCKING ROW FOR ACCESS SELECT * FROM DB1_t.T1;
grant select on DB1_t to DB2_v with grant option;
select * from DB2_v.V1;

REPLACE VIEW DB3_v.V1 AS LOCKING ROW FOR ACCESS SELECT * FROM DB2_v.v1;
grant select on DB2_v to DB3_v with grant option;
select * from DB3_v.V1;

REPLACE VIEW DB2_v.V1 AS LOCKING ROW FOR ACCESS SELECT * FROM DB1_t.T1;
select * from DB3_v.V1;

drop VIEW DB2_v.V1;
REPLACE VIEW DB2_v.V1 AS LOCKING ROW FOR ACCESS SELECT * FROM DB1_t.T1;
select * from DB3_v.V1;

RESULTS:

We only need to provide grant on DB2_v to DB3_v. No need to provide grant on DB1_t to DB3_v.