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