All Forums General
Rna 2 posts Joined 03/15
03 Mar 2015
Child user can't select view of parent in teradata

In Teradata SQLAssistant, I have created a db user USER1 which has 17 views. This user can do a SELECT on any view, thanks to a profile/role I granted and then associated to him. His child USER2 is a db user with the same profile/role as USER1, but my problem is he can't select any of the views of USER1 :

Table/View **** not found, or you have no access rights

When I check in Teradata Administrator, USER2 has the same right (READ) on the datawarehouse tables used by the 17 views as USER1....
Can anyone help me ?
I can provide more details.
Thanks in advance.

Fred 1096 posts Joined 08/04
03 Mar 2015

USER2 does not need rights on the referenced tables to use views in USER1.
USER2 must have SELECT access on USER1 (or on the views individually); this could be granted via a role
USER1 as a database / view owner must have SELECT WITH GRANT access to the referenced tables; this cannot be granted via a role.
If you don't want to give SELECT WITH GRANT to USER1 directly, then you can create a separate database VIEW1 to hold the views. Give VIEW1 SELECT WITH GRANT, give USER1 CREATE VIEW on VIEW1, and give USER2 SELECT on VIEW1.

Rna 2 posts Joined 03/15
04 Mar 2015

Granting select with option on datawarehouse tables (used by USER1's views) to USER1 and granting SELECT on USER1 to USER2 through a ROLE worked.
Now i only need to give that role to USER1's children when i create them and they can perform a SELECT on USER1's views
Thanks !

You must sign in to leave a comment.