Search code examples
teradatateradata-sql-assistant

Teradata - Create views of tables in another database

I need to create in Teradata VIEWS of tables in another database. I have created both but now when making a select of the VIEW, It shows me the following Error: "Table/View 'MyDatabase.MyView' not found, or you have no access rights".

What type of rights need the User, View, Table, Database... to make this works? And at what time and how should I give them?

Thanks!

Solution

  • Suppose we have SomeDB.TableOrViewA and a view MyViews.ViewX that references SomeDB.TableOrViewA. The "view owner" MyViews is allowed to provide access (via ViewX) only if MyViews holds corresponding rights WITH GRANT OPTION.

    In order for a UserN (who is not the creator of MyViews.ViewX) to SELECT from the view, not only must UserN hold SELECT permission on MyViews.ViewX (or the entire MyViews database), but the MyViews database must also hold SELECT WITH GRANT OPTION permission on SomeDB.TableOrViewA or on the entire SomeDB database. This is true whether or not UserN has SELECT permission on the underlying SomeDB.TableOrViewA itself.

    GRANT SELECT ON SomeDB.TableOrViewA TO MyViews WITH GRANT OPTION;

    or GRANT SELECT ON SomeDB TO MyViews WITH GRANT OPTION;