Skip to content

Posts tagged ‘teradata’

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.

How to change root password in all Teradata nodes?

TO change the default root password of Teradat nodes you can either login to each node and do it manually. But if you have a larger TD system then possibly this may not be feasible. To avoid loggin in to each TD node you cna make use of teradata utility called psh. Using which you can change password on all nodes by just connecting to one node.

Here are the steps to follow:
1) Login to node 1 (or any node) of your Teradat system as root

2) Run below command and follow the prompt. psh utility will first connect to all the nodes and will then prompt for new password twice.

# psh -i ‘passwd root’
all 4 nodes:
Changing password for root.
New Password:
passwd.0> MyNewRootPassword
all 4 nodes:
MyNewRootPassword
Reenter New Password:
passwd.1> MyNewRootPassword
all 4 nodes exited
all 4 nodes:
MyNewRootPassword
Password changed.

3) Type exit to exit the psh utility
psh.0> exit