1

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.

EDIT1 : I have the possibility to do that select, but i need to do a
Grant SELECT on table_used_by_view to USER1 WITH GRANT OPTION; on each table...
I really need to stick with the ROLE/PROFILE method, so I can't accept this solution (as you can't do a WITH GRANT OPTION on a role).

daniel.rna
  • 73
  • 1
  • 11
  • select * from user1.viewname? – jarlh Mar 03 '15 at 13:07
  • I just tried and it sends me the following error message : "SELECT Failed 3523 : An owner referenced by user does not have SELECT WITH GRANT OPTION access to (name of the table used in the view)" – daniel.rna Mar 03 '15 at 13:08

2 Answers2

0

The owner of the view, USER1 in this case, must have SELECT WITH GRANT OPTION explicitly granted on either the databases or the tables defined in the view.

GRANT SELECT ON {db1}.{table1|view1} TO USER1 WITH GRANT OPTION;

At a high level, this allows USER2 to access data in another database object without having explicit permissions to access that data granted to USER2.

Rob Paller
  • 7,736
  • 29
  • 26
  • So it's basically impossible to give the permission to USER2 thanks to a ROLE ? granting select explicitly with option would be the only way to give the select right to a child user ? – daniel.rna Mar 03 '15 at 16:47
  • You need to both. First `User1` needs `SELECT WITH GRANT`. Then `User2` needs `SELECT` via role or explicit grant on the view owned by `User1`. – Rob Paller Mar 03 '15 at 17:02
0

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

daniel.rna
  • 73
  • 1
  • 11