Teradata - Using Roles

Hi Friends,

Today we will learn using roles in teradata with examples:

Create roles.

 

  CREATE ROLE Inquiry_HR;

  CREATE ROLE Update_HR;

  CREATE ROLE Inquiry_Payroll;

  CREATE ROLE Update_Payroll;

  CREATE ROLE Batch_HR_Pay;

 

Assign access rights to the roles (partial listing).

 

  GRANT SELECT, EXECUTE   ON HR_VM TO Inquiry_HR;

  GRANT INSERT, UPDATE, DELETE   ON HR_VM TO Update_HR;

 

Grant users permission to use the roles.

 

  GRANT Inquiry_HR   TO Update_HR;   /*nested role*/

  GRANT Inquiry_HR   TO Emp01, Emp02;

  GRANT Update_HR   TO Emp03, Emp04;

  GRANT Update_HR   TO Sup05 WITH ADMIN OPTION;

  GRANT Batch_HR_Pay   TO Sup05 WITH ADMIN OPTION;

 

Modify the user to set the default role.

  MODIFY USER Emp01 AS DEFAULT ROLE = Inquiry_HR;

  MODIFY USER Emp02 AS DEFAULT ROLE = Inquiry_HR;

  MODIFY USER Emp03 AS DEFAULT ROLE = Update_HR;

  MODIFY USER Emp04 AS DEFAULT ROLE = Update_HR;

  MODIFY USER Sup05  AS DEFAULT ROLE = Update_HR;

Emp01 –   is granted to Inquiry_HR role;

Inquiry_HR is current role.

 

  SELECT   * FROM Employee_v  ORDER BY   1;  (success)
 

  UPDATE   Employee_v

  SET   Dept_Number=1001

  WHERE   Employee_Number=100001;  (fails)

  Why does this statement fail for Emp01?

Emp03 –   is granted to Update_HR role;

Update_HR is current role.

  SELECT   * FROM Employee_v ORDER BY   1;  (success)

 

  UPDATE   Employee_v

  SET   Dept_Number=1001

  WHERE   Employee_Number=100001;  (success)

  Why do both of these statements succeed for Emp03?

 

Sup05  –   is granted to Update_HR role WITH ADMIN OPTION.

  GRANT Update_HR TO Emp02;  (success)
 

Emp02 –   is granted to Update_HR role; Inquiry_HR is current role.

  SELECT   * FROM Employee_v ORDER BY 1;  (success)

  UPDATE   Employee_v

  SET   Dept_Number=1001

  WHERE   Employee_Number=100001;  (fails)

  Why does this statement fail for Emp02?

Emp02 – executes the following SET ROLE command

  SET ROLE   Update_HR;

  UPDATE   Employee_v

   

  SET   Dept_Number=1001

  

  WHERE   Employee_Number=100001;

  Will this UPDATE statement succeed this time?

  Will this UPDATE statement succeed the next time Emp02 logs on? 

                                                        ----> to be Continued