I have create a role test_role. Also I create a view for a table which allow only see 3 columns out of 5. Then I assign this view to this role.
CREATE ROLE test_role;
Create a View
CREATE VIEW LDAP_TEST.employee_view AS SELECT EMPLOYEE_ID, EMPLOYEE_NAME, DEPT_ID FROM LDAP_TEST.employee;
Grant the role to a table
GRANT SELECT ON LDAP_TEST.employee_view TO ldapUser; TO test_role;
Grate the role to a user
GRANT test_role TO myUser;
When I login to a user and run the command
select * from LDAP_TEST.employee_view;
It return error that
Executed as Single statement. Failed [3523 : 42000] The user does not have SELECT access to LDAP_TEST.employee_view.
Elapsed time = 00:00:00.362
STATEMENT 1: Select Statement failed.
How ever when I run SET ROLE test_role; It return the 3 columns out of 5 from the table as expected.
Challenges:
I am building a dotnet app which create user and assign the role according to the Users role from Active Directory. My app successfully fetch the user create them in Teradata and also assign the role. How ever User are not able to use the role privilege's until manually login and use SET ROLE test_role command. I want to run this command as a admin so user don't have to login to activate the role.
What I have done? I have try to create a user with default role. Still user need to login and use the set command to make it active. I also modify the user and set the role as default role. Still the same problem.
What DO I want? A way to set the role of a user from dbc user or any administrator user of Teradata so User don't have to login and set the role before use it.